Solved

Accounting Format in SSRS

Posted on 2010-11-12
13
5,045 Views
Last Modified: 2012-05-10
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
Comment
Question by:propertytax
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 6

Accepted Solution

by:
AliSyed earned 200 total points
ID: 34125769
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
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 300 total points
ID: 34139883
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
 

Author Comment

by:propertytax
ID: 34140126
@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
 
LVL 6

Expert Comment

by:AliSyed
ID: 34141894
. 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
 

Author Comment

by:propertytax
ID: 34168430
> 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
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 300 total points
ID: 34171068
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:propertytax
ID: 34190715
@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
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 300 total points
ID: 34195272
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
 

Author Closing Comment

by:propertytax
ID: 34208926
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
 
LVL 10

Expert Comment

by:itcouple
ID: 34211224
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
 

Author Comment

by:propertytax
ID: 34214880
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
 
LVL 10

Expert Comment

by:itcouple
ID: 34246101
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
 

Expert Comment

by:rupex2101
ID: 37739203
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now