SQLPam's Blog

December 14, 2012

Suppress zeros & retain fixed decimals when exporting to Excel

Filed under: SSRS — sqlpam @ 8:42 am

I have recently been working on an interesting project where we dynamically build data for export to excel using SSRS.  The spec was a bit crazy sounding.  We have a bunch of data that we need to export on a nightly basis – but the definition changes on regular basis and by the way – we have an unknown number of sheets with unknown formats that need to be consolidated into a single report for export to excel.  The only definite I was able to establish was that no individual sheet has more than 100 columns.  That did help.

We were able to actually pull this all together with relative ease until it came to formatting.  I will expand on the character versus numeric data and the dreaded green triangles at another point.  For today I would like to touch on decimal formatting for numeric data.

Here is the dilemma we encountered.  We have both a numeric and character column for each of the 100 columns we are working with.  However, the numeric data could be int, bigint or decimal.  Luckily, the only decimal format was limited to 4 decimal places – or the solution would have been more complicated.

In the value expression for the numeric column, we were already evaluating the passed data type and converting the value accordingly using CINT() or CDEC().  I was able to open the code for the RDL and globally change the value expression to multiply the CDEC() value times 1.000.  It was a suggestion I found in a related post.  I am not sure if it was really needed – it did not hurt – so I kept it.

The next issue was the format string.  I was able to easily apply a format string to each numeric column using  “#.####” with the hope that this would both zero suppress and force 4 decimal places.  My hopes were dashed.  I did achieve the zero suppression – but decimal places were not to be found.  However, this did give me a convenient means of globally changing all when I found the solution.

The solution was a conditional format.  I was lucky, by converting the INT data – it retained 0 decimal places so I was able to use the format string:

=IIF(Me.Value=0, “#”, “F4”)

I was not required to touch each column manually by using the Me.Value – so a global replace in the RDL handled this for all 100 columns.  The F4 translated well to Excel.

Advertisements

Create a free website or blog at WordPress.com.