Format similar to Excel accounting format (paren around negatives, - for 0)

I know that it's possible to get () around negatives in Reporting
Services, but I have yet to find a way to have it display 0 as a -
(hyphen). I've found that I can use an IIF but that means that the -
will be a string and not actually representative of 0 when exported to
Excel. Anyone have a solution, or is this just a shortcoming of
Reporting Services?On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> I know that it's possible to get () around negatives in Reporting
> Services, but I have yet to find a way to have it display 0 as a -
> (hyphen). I've found that I can use an IIF but that means that the -
> will be a string and not actually representative of 0 when exported to
> Excel. Anyone have a solution, or is this just a shortcoming of
> Reporting Services?
Have you set the format property using the iif statement and then set
the value property as Nothing or 0 if below zero? If so, you should be
able to avoid the string representation in Excel. You should use
something like this in the Format property.
=iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
and then use the regular expression in the Value property:
=Fields!SomeItem.Value
If this does not work out, you should be able to use the cast as int
functionality in the value property.
=iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 18, 9:21 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> > I know that it's possible to get () around negatives in Reporting
> > Services, but I have yet to find a way to have it display 0 as a -
> > (hyphen). I've found that I can use an IIF but that means that the -
> > will be a string and not actually representative of 0 when exported to
> > Excel. Anyone have a solution, or is this just a shortcoming of
> > Reporting Services?
> Have you set the format property using the iif statement and then set
> the value property as Nothing or 0 if below zero? If so, you should be
> able to avoid the string representation in Excel. You should use
> something like this in the Format property.
> =iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
> and then use the regular expression in the Value property:
> =Fields!SomeItem.Value
> If this does not work out, you should be able to use the cast as int
> functionality in the value property.
> =iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
What I'm hoping to do is have 0 show up as a dash like this: -
Will this do that?