Minus Sign ()

Hello:

I need to change minus brackets () with - sign in SSRS. How i can do this?

Thanks

Amit

Hi Amit,

See this post for information and links on how to do this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=105700&SiteID=1

You'll want to do something similar to this: ##.##;-##.##

-Jessica

|||

Dear Jessica,

Thanks for your reply and i tried couple of formats and still not able to get correct results.

i need to change (123.90) to -123.90

Any help!!

Thanks

Amit

|||iif(number is negative,
Cint( replace(
replace((your number),")" ,"")
,"(","")

) * -1
,your number)

sorry if syntax on replace is wrong

it's pseudo code ish:

replace the brackets with nowt and then * by -1|||

Hello Amit,

Since your value is stored as a string, this should work for you:

=Iif(InStr(Fields!Number.Value, "(") > 0, "-" & replace(replace(Fields!Number.Value, "(", ""), ")", ""), Fields!Number.Value)

Jarret

|||Haven't I seen that somewhere before....?|||

If you are referring to the pseudo code you posted, almost.

Since his field is stored as a string, there's no need to convert to an integer and then negate it when all you need to do is prefix it with '-'. Should be a little more efficient, and, a conversion using CInt will fail if a non-numeric value is stored there.

Jarret

|||

Hi,

I don't know if I'm missing a point here, but if the field is stored as a string, just converting it to Double should be sufficient.

For example:

CDbl("(23.45)") = -23.45

Please correct me if I'm wrong.

-Aayush.

|||The CINT is because I was multiplying by -1

String * Integer?

Nice.

So when you export to excel and it is not a number, how exactly does that help the user?|||

Adolf - I understand what you were doing in regards to the CInt and -1. I am unsure of what you mean by this though:

String * Integer?

Nice.

I don't know if this was meant to be sarcastic or not, but if you look at my reply, "there's no need to convert to an integer and then negate it". The "negate it" means multiply by -1, I wasn't saying to leave it as string and still multiply by -1.

As for the export to Excel question... Amit didn't mention anything about exporting to Excel to do further processing, and since the values are already strings and didn't work this way before, I wouldn't think this would be a concern. However... If the number is not negative, you're not converting to Integer in your pseudo code. I just did a test export with some integers and some strings in the same field (as with your code), all values are considered strings in Excel. The only way to get all numbers in Excel with your version is if they are all negative numbers.

Another thing, converting to Integer using CInt will lose the decimals from the negative number (if it is a number), I doubt this was Amit's intention.

Aayush - Yes, that will work also, assuming that all values in that field are numbers stored as strings. I'm not sure if it matters to the Amit, but CDbl("(123.90)") becomes -123.9 without the ending 0.

Jarret

|||- you never know how users are going to use reports (just yesterday a report which 'had to fit on a single A4 for PDF' is now fine because they are sending it out in excel)

- export to excel functionality in RS is utterly ***. A cell formatted with N2, shows up in excel as [$-1010809]#,##0.00;-#,##0.00 in one cell and [$-1010809]General in another! I have to use CDbl in addition to the N2 formatting otherwise it comes out as a string

Ok so it should have been CDbl not CInt.

Right I'm done with this now.

Next!