Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5775
  • Last Modified:

Accounting Format in SSRS

My reporting guy tells me this is impossible, and I just can't quite accept that. I am interested in generating columns of numbers in SSRS reports that emulate the Excel "accounting" format. Specifically:

parentheses are used to indicate negative numbers
the numbers are all alligned on the decimal point, regardless of whether they are positive or negative (essentially, the positive numbers have to be slightly inset from the right margin by the width of a parenthesis character)
the $ character is left justified in the cell, regardless of the length of the number
zeroes are indicated by a "-" character
In Excel, all this is encompassed in the "Accounting" format. I do believe that this format is not directly supported in SSRS, but can all (or some) of this behavior be simulated in SSRS? Does SSRS in SQL Server 2008 R2 offer any additional support for this, if SQL Server 2005 does not? I am embedding an image here, showing what I am after, both the "ideal" as well as an alternate that gives up on the $ character:
 Accounting Format Example
I am also attaching an Excel file that was used to generate the above.

Again, if there's something supported in SSRS 2008 edition that might help, that would be a great selling point on investing in a new version of SQL. Many thanks! Accounting-Format-Example.xls
0
propertytax
Asked:
propertytax
  • 5
  • 5
  • 2
  • +1
4 Solutions
 
AliSyedCommented:
Yes this can be done
•parentheses are used to indicate negative numbers
In format property of fields that you want to show -ve number as () place this code #,##0;
•the numbers are all alligned on the decimal point, regardless of whether they are positive or negative (essentially, the positive numbers have to be slightly inset from the right margin by the width of a parenthesis character)
this should align properly other wise can be done using "iif" to do this


•the $ character is left justified in the cell, regardless of the length of the number
use textAlign property to left for required fields



•zeroes are indicated by a "-" character

again use IIF
IIf((Fields!field1.Value) =0,"-"),Fields!field1.Value))

0
 
itcoupleCommented:
Hi

Just wanted to make an additional comment about 2008 R2. There are no differences in what you are after. Unless you have data which you want to show on a map :)

Regards
Emil
0
 
propertytaxAuthor Commented:
@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!
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
AliSyedCommented:
. 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
0
 
propertytaxAuthor Commented:
> I thought your question was how to align so $value is always on the left. SO
> i suggested aliging it to left

Open 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.
0
 
itcoupleCommented:
Hi

You can do that with SSRS and if you don't find what you are after using standard 'click' options you can build expression.

1) $ on the left (this is just an extra column with dollar sign doesn't need any alignment if the width is set correctly
2) See screenshot (this should be the same in 2008 / 2008 R2 and 2005 I think should be the same if not it will still be avialable from properties pane)
-- -10.54 is (10.54), zero is '-'

   AccountingFormat
These are rather basic formatting options. I would strongly recommend to ask your report developer to have a look into them in more details. Unless you still need some reports from him than don't tell him :)

Hope that helps
Emil
0
 
propertytaxAuthor Commented:
@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?
0
 
itcoupleCommented:
Hi

SSRS isn't perfect with spacing and alignments. It has to work with different browsers and screen resolutions. So often is just a matter of testing different scenarios/figures.

Regards
Emil
0
 
propertytaxAuthor Commented:
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.
0
 
itcoupleCommented:
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
0
 
propertytaxAuthor Commented:
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.
0
 
itcoupleCommented:
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
0
 
rupex2101Commented:
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.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now