Accounting Format in SSRS

@AliSyed - I think I follow what you're suggesting for the zero indicated with the ["-"] character, but I don't see right off how the IIF statement will help to force negative and positive numbers to align on their decimal points exactly. If you're recommending using IIF logic to append a space [" "]character to the right of a positive number, is there a way to specify the specific width of the space? The parenthesis character [")"] doesn't line up exactly with a space character [" "] in many fonts.

Finally, I am not following on how setting the textAlign property to Left would help, unless you're proposing that I two fields to represent the number (one for the $ symbol, and one for the number), which is the main thing that a true "accounting" format, a la Excel, offers.

Thanks for the initial thoughts! If you could clarify/expand a little, I'd appreciate it!

. If you're recommending using IIF logic to append a space [" "]character to the right of a positive number, is there a way to specify the specific width of the space? The parenthesis character [")"] doesn't line up exactly with a space character [" "] in many fonts.

It would need some tweaking to align it.There is nothing built into ssrs that I know of. So I guess it will have to be dirtyI do not have ssrs on this systems so it will be difficult for me to give you a working example

Finally, I am not following on how setting the textAlign property to Left would help, unless you're proposing that I two fields to represent the number (one for the $ symbol, and one for the number), which is the main thing that a true "accounting" format, a la Excel, offers.
NO  I thought your question was how to align so $value is always on the left. SO i suggested aliging it to left

> I thought your question was how to align so $value is always on the left. SO
> i suggested aliging it to left

Select allOpen in new window

I had hoped that,by including the pictorial example, it was clearer that I need to numbers to be aligned on the decimal point (on the right side of the cell) while the "$" symbol was left-aligned within the cell, which is pretty much the definition of "accounting format."  A simple left alignment for all contents in the cell wouldn't result in the numbers being vertically aligned on the decimal point.

The real meat of this question - and the reason for the high point value - is if anyone has tackled this in SSRS in order to yield something that is truly "accounting format" as shown in the example in the original question, where the $ symbols are left aligned within the cell, while the numbers are aligned on the decimal point. Definitely tricky, if there's no native support, but I"m still hoping one of the EE folks here has tackled this very thing.

@itcouple - do you happen to know whether, if using the parentheses to indicate negative numbers, SSRS will "reserve" enough space for the (unused) parenthesis on the right-hand edge when numbers are right-aligned?

Thanks to the experts for weighing in on this. Although ultimately the answer was "no" to get into a true accounting format, the answers, though echoing the solutions I'd already seen attempted by the reports person, were given in good faith so I've left the question points as originally stated and allocated among the various answers on this multi-part question. Thanks, all.

Hmmmm You mentioned that it doesn't give you true accounting format. I wonder why you you think so? (Which bits) SSRS Is Not Excel (works very differently for good reasons) but still I thought it gave you what you wanted to achieve?

Just wondering
Regards
Emil

Hi @itcouple - well, we're preparing reports that we want to send to clients in place of Excel reports, and we often have columns of 4 to 10 numbers (with totals and subtotals) in close proximity, so getting the look right is important. There's two features of "true" accounting format that are important to look right:

1) the number, with parentheses used to indicate negative numbers, is aligned on the decimal point (not the right edge of the cell)
2) the currency symbol needs to be aligned on the left edge of the cell, regardless of the length of the number

The above suggestions, summarized, came down to:

- for #1, use the built-in format to use parentheses to indicate negative numbers, and choose right alignment within the cell

Ignoring the currency symbol issue, this doesn't give us what we need as we get a ragged right margin, with negative numbers "pushed" over to the left compared to positive numbers. The add'l workaround is to use IIF statements to concatenate on a standard space to positive numbers. This is "almost" right, except that a standard space in the proportional fonts we use is not the same width as a parenthesis, so we get a less ragged appearance, but still not exactly aligned on the decimal point as you intermix negative and positive numbers.

- for #2, use a separate column to hold the $ symbol

This does give the same effect as accounting format, with the overhead cost being that we have to double the number of columns that we're managing as we lay out the report. Still, it's a valid workaround to give the appearance of accounting format.

For both of the above, it creates a real mess if we export the SSRS report to Excel, but that wasn't part of the original question and challenge. But the real issue is that, even remaining in SSRS, we get "close" to the look of the accounting format, but fall just a bit short due to #1.

This isn't meant to be picky about the definition of "true," just giving a sense of where it falls short for us. The actual result of all this is that we're probably going to get what we need by exporting the data via powershell into actual Excel templates. It's more work on the setup side, but it does give us perfect-looking Excel files that exactly mimic what we've delivered to our clients in the past, but generated via SSRS rather than being assembled by hand.

Hi

I now understand your issue and i have been involved myself into very detailed requirements which SSRS doesn't handle using build in features, but in your case it wouldn't take much time to do it using common approaches and expressions in SSRS which avoid lack of certain features which are available in Excel. Export to excel might be slighty an issue (would have be to tested) with that approach but there are different ways to do that.

Another approach is to create just a function which takes a number and the output it as string. This gives you control over the layout because you potentially can use spaces to align it but again this approach would have to be tested fully as I haven't used for this specific job myself, but extra benefit is that if you export it to excel it is just string so formatting should remane the same. Functions are non-sharable in SSRS but you can just write the function in Visual Studio and save it as dll (assembly) and then add it to your report. This way it is in central place and any bugs fixing is very easy. I have actually used that this week to develop Flexible Rounding (max/min/Interval) for charts as SSRS auto setting is not brilliant.

If you would prefer to use SSRS and need help in doing that (assembly or sample report) with you exact specification which requires a bit more skills to develop than let me know. I know someone who specializes in SSRS and is doing that for living, and I cannot imagine it would take her long time to do it.

Regards
Emil

As Everyone said, use FORMAT(data.Value, "$#,###,##0.00;($#,###,##0.00)". it leaves -ve number's decimal point sligltly on the left than +ve numbers to accomodate ')'. you can choose to use conditional padding to make them aligned.