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.


April 11, 2012

Monster Reports – Part III – Disparate Controls

Filed under: Presentations,SSRS — sqlpam @ 8:29 pm

This – as the name states – is Part III in a series on My New Presentation – Taking the Scary out of Monster Reports. Previously, we discussed that there are 2 types of monster reports: Disparate Data and Disparate Controls. This article will discuss Disparate Control reports, some of their challenges and some ways to get around them.

With this type of report, I usually start by defining the components that will go on the report. If the user has supplied a detailed version of what they want I know how to proceed. If not, I need to ask myself and sometimes the user, some of the following questions:

Is the report portrait or landscape?

  • Will I need more than one page for the report?
  • How many columns are needed across a page?
  • Can the different components be sized close to the same width? There might be different column widths on a page allowing you to group the components into the columns by width.
  • Do the different components have a relatively fixed height or will they grow with data? Top X reports are relatively fixed – as are set aggregates. A list based on parameters may grow beyond your page.

You may need to work with the user to come up with a clean design. Knowing the column widths up front is critical for a clean report before you start the next step of building the individual components.

I do recommend building the different components individually. This does three things:

  1. It allows me to work in a more controlled environment without worrying about messing up something else.
  2. It adds to the catalog of reports I can offer my users.
  3. It builds a “library” of reports I can pull onto my Monster reports.

Item one is really big on my list. It really pains me when I have a report close and then mess it up by adding something that doesn’t work. By breaking them out individually, I get the luxury of a clean sand box. I know that what I am attempting is possible. Remember that some things are easier to accomplish than others. This is where we find that out.

Item two tends to be high on my clients list. In addition to the Monster report, my user often wants to be able to print the individual components. I will often place these in a subfolder and adjust the parameter settings to enhance the user experience. If I am implementing the reports on the Monster report as sub reports, these are not the deployments I access. Those are usually deployed so they are not visible to the average user. This allows the user to share a small portion of the Monster report without exposing everything. It is a nice bonus for them.

Item three is my primary reason for breaking these out into individual reports. Basically, I am creating the Lego blocks I will be using to build my report. If I need a red block – I can easily grab it and implement. It has my data sets and layouts to make my life so much easier. Most importantly – I now know they actually work.

Now that I know what components I need, I need to determine how I am going to fit them together. I have used two methods primarily. Sub reports called from a table on the Monster report or including everything on the base layout.

A table holding sub reports is usually the easiest method. There are two things to consider. If the heights of the controls are not consistent there will be gaps when one control takes up more vertical space than the other controls on a row. Remember that SSRS does not allow you the luxury of spanning rows. But let’s assume that all your components are the same size. We need to handle the parameters. When calling sub reports, the parameters are expected to come from the table’s defined dataset. In our case, we need to create a data set that defines our parameters as the columns of the main grids data set. Now we set for a fast build. I usually define one sub report and copy it to the other cells. I then make changes based on the component’s parameter needs. This makes pulling it together very fast and very easy. It just needed a little prep.

The other method is a little more solid – less exposed blank space. I start by adding each individual dataset on the report. If I am reusing a component with different parameters – I need a multiple datasets. At this point I move to the layout where I can start dropping copies of the original components onto the report.

If I drop the components directly onto the base layout, I will encounter the same blank space issue as I had with the table. However, if I place rectangles on the base report and drop a copy of the component into the rectangle I have fewer issues with blank space. The rectangles allow each “column” to grow vertically independent of the contents of the other rectangles. The rectangles can also help set the page breaks if needed.

Don’t get me wrong, Monster reports are seldom simple. By with previously outlined steps, I hope you find them less “Scary”.

You find the files associated with this preasentation at: http://sdrv.ms/II1Lcn.

April 1, 2012

Monster Reports – Part II – Disparate Data

Filed under: Presentations,SSRS — sqlpam @ 2:23 pm

This – as the name states – is Part II in a series on My New Presentation – Taking the Scary out of Monster Reports.  We discussed that there are 2 types of monster report Disparate Data and Disparate Controls.  This article will discuss Disparate Data reports, some of their challenges and some ways to get around them.

When I get a request for one of these reports, I envision someone sitting there with stacks of reports, a pair of scissors and lots of tape.  More likely – it was tons of Excel spreadsheets and utilization of the cut and paste options – but the effect is the same – they took the totals from a lot of reports and smashed them together into a single report.  I can understand why they want them – but the expectations that that these smash up reports will run as fast as one of the original reports and not tax the system in the least that usually gets to me.

So let’s state the obvious – in a perfect word – the data for these reports would all come from cubes and we would be able to easily pull this data using MDX.  That world does exist for the rare few – but not for most of us – so we get over it and roll up our sleeves.

The trouble here is that now we have to run the queries that created each of those spreadsheets we saw earlier.  If we are lucky – we have the resulting spreadsheet to work from.  It holds the clue to how our report will work.  If not, it might save some time to build that spreadsheet – not really the data – but the layout.  It will tell us what groupings we need, the row layout for each grouping, as well as the formatting of each line.

So for disparate data reports – it is all about the data – more than anything else.  So we need a- for the most part – a single stored procedure to return all the data for the main grid on the report.  I usually have to play with how this will happen at each client’s site – because everyone’s data is different.  If the data is not massive – CTE’s may be the best solution available, other might need table variables or even temp tables.  The old standby – “it depends” is very much in play here.  But need is the same – accumulate the data into a single query-able source.

It never fails – I get everything into the ONE SOURCE when the client comes back to me and says – I have a few minor changes; “I need to change the order on some of this and add the following…”.  Let’s just plan on this happening up front – because it will.  Let’s add a “control table” to the mix.  This is a table that contains data about the different report data that will be displayed on the report and the attributes of each of these data areas.  This is my blue print to the report and it is usually a work in process thru out the life of the report.

For my demo, to promote self-containment – my control table is actually a CTE.  I find a table infinitely easier to work with – but the demo has other needs.  The control table will serve a multitude of purposes.  It will control the order of the data being displayed, the format it is displayed into and as documentation for anyone coming behind me on the report.

In the monster stored procedure, for each queried bit of data, I include a short description of the data being collected.  This is in verbiage – because it is easier to relate than numbers – but again – I keep it short.  This is going to be my link between the stored procedure data and the control table.  I will have a more verbose description that gets displayed to the end user – that can be easily changed on a whim.  I will have a number that represents the order that the data is displayed.  From here the sky is the limit – anything I need to track can be included here.  My format is usually a must, but I often include source data information and tool tips.  Note that multiple fields may be required for tool tips if they are verbose because tool tips do not wrap.

The final SELECT statement in the monster stored procedure joins the collected data to the control table and imposes order while adding the elements need for the report.

In the report itself, the source data set will be based on our monster stored procedure.  We will add the groupings with headers and footers as needed.  We may need multiple detail/header/footer rows to accommodate the multiple layouts involved.  The control table will provide the key as to which will be visible for each row displayed.  A critical note – if this is being exported to Excel prior to 2008 R2 – the multiple layout rows need to be kept to a minimum as each row is visible in the Excel export.  The control table can also provide the extras such as tool tip verbiage.

So for me – the control table is a must have when dealing with disparate data reports.

The next in the series will be dealing with Disparate Controls.

March 22, 2012

A New Presentation – Monster Reports – Part I

Filed under: Presentations,SSRS — sqlpam @ 10:35 pm

Taking the Scary out of Monster Reports – this is the title for my latest presentation. For the past 4-5 years I have had one presentation on reporting services – Tips and Tricks of Reporting Services that has been presented at least 15 times. It discusses the joys of the dynamic features in SSRS that allow you to modify the report’s appearance and behavior by using data. But it was getting old – way old and it was time for another main line presentation. I must note – this was not the only presentation over that time frame – just the most well received.

I write reports for my clients. Recently, I saw a trend to more complicated reports or what I refer to as “Monster Reports”. I tend to cringe when I see these. They are very important reports to management. They usually mean that instead of digging to the bottom line of a number of reports – they have one report that encapsulates what they need to see.

I have noticed two directions for these reports. One has a lot of disparate data on the report while the other has a lot of disparate controls on the report.

The disparate data report is usually a report that combines the subtotal/total lines from a number of reports. A lot of time they look at fixed period(s) in time to display associated totals. But sometimes they get even more complicated by spinning in different columns within the multiple subtotal or they request charts to visualize one or two of the result sets. These can be a beast to create and maintain.

The disparate controls report has a similar challenge in that you are pulling back very disparate data – but the presentation is what differs so much with this one. You are attempting to wedge multiple charts, grids and potentially gauges on a single page – possibly more. They want is packed tight with few gaps. But they want it nice looking.

So the point of the presentation is to supply the attendees with a number of strategies to help make these “Monster” reports less “Scary” to tackle. So I will be posting a series of blogs over the next or so to go into a little more depth than the typical hour presentation will allow. My carrot to the attendees is that at the end of the presentation – they will have access to a series of RDLs that will allow them to start to monitor their Reporting Server Database. The idea for the topic was inspired by Jessica Moss (Blog | Twitter) who dids a presentation at PASS Summit 2011 entitled: Preventing the Oh, Poop! Reporting Situation. I liked that at the end of the presentation the attendee actually had something useful. So I built on what she supplied.

The next blog will be on the Disparate Data reports and the techniques I employ the tame them.

February 10, 2012

In SSRS, Long Tool Tips Don’t Auto-Magically Wrap

Filed under: SSRS — sqlpam @ 5:58 pm

So here is the issue: The user wants detailed information as to how data is derived.  There is no way to shortcut it – you have to get wordy.  So you store the verbiage in a VARCHAR(MAX) column and populate the tool tips using this. 

Works great!  There is the verbiage and off the screen it goes.  What?  It isn’t smart enough to wrap to stay on the screen – not really.  

So now what? 

Here’s what I did. 

I found the longest tool tip in the table and then broke it out into 100 character segments.  I created new fields for each needed segment and updated them in the table. 

But it was more than that – I broke it out based on breaks so that words were not chopped.  My solution was not elegant but here it is:

My original tool tip field was GrpToolTip.  I added GrpToolTip1, GrpToolTip2, GrpToolTip3, GrpToolTip4, and GrpToolTip5 – all VARCHAR(100).

I look for the original Tool Tip to be greater than 100.  I determine the location of the first space after 80 to allow for longer non-breaking text and pull the left most characters based on the results.

If the original Tool Tip was less than 100 characters – I replace the new appropriate new Tool Tip with the original.

Next I update the original Tool Tip to remove the data moved to the new tool tip.

Then repeat the process thru all the new fields.

UPDATE rptReportTemplate
SET GrpToolTip1 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100

UPDATE rptReportTemplate
SET GrpToolTip1 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip1, ”) 

UPDATE rptReportTemplate
SET GrpToolTip2 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100

UPDATE rptReportTemplate
SET GrpToolTip2 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip2, ”) 

UPDATE rptReportTemplate
SET GrpToolTip3 = LEFT(GrpToolTip, charindex(‘ ‘, GrpToolTip, 80))
WHERE LEN(GrpToolTip) > 100 

UPDATE rptReportTemplate
SET GrpToolTip3 = GrpToolTip
WHERE LEN(GrpToolTip) <= 100

UPDATE rptReportTemplate
SET GrpToolTip = REPLACE(GrpToolTip, GrpToolTip3, ”)

You get the picture…

Now that I have all the data segmented into the proper lengths, I update the stored proc that feed the report to include the new segmented fields instead of the original field.  I need to refresh the report’s dataset so it sees the new fields. 

The next step is what all this was about.  Where the tool tips were displayed, instead of just setting the tool tip to the original tool tip field – I utilize an expression to force the next line down.  Here is the expression: 

=IIF(LEN(Fields!GrpToolTip1.Value)>0,Fields!GrpToolTip1.Value,””) +
          IIF(LEN(Fields!GrpToolTip2.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip2.Value),””) +
          IIF(LEN(Fields!GrpToolTip3.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip3.Value),””) +
          IIF(LEN(Fields!GrpToolTip4.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip4.Value),””) +
          IIF(LEN(Fields!GrpToolTip5.Value)>0,CHR(10) + LTRIM(Fields!GrpToolTip5.Value),””)

Note that I only include the CHR(10) when the segment is populated and I also strip off leading spaces for an even left edge.  That could have been accomplished with the above code with a little more effort – but it works here. 

Not rocket science – but hopefully it will save a few steps for someone in the future.

April 27, 2011

Using Multi-Value Parameters in SSRS Datasets

Filed under: SSRS — sqlpam @ 1:14 pm

I was given a simple assignment to generate a Usage Summary report for the reports in SSRS.  To achieve the required results took very little time.  However, I decided to allow the user the ability to select which report user(s) could be displayed on the report.  Thanks to a recent presentation by Mike Davis, I was able to implement this with minimal effort.  My one gotcha was that I neglected to place parenthesis around the parameter – so it was a minor blonde moment.

As I played with the report a couple of days later, it struck me that instead of having to wade thru all the users of a report, it would be helpful to see all users grouped.  Yes – a grouping on the report would have achieved this – but the individual users would still be there.  I had the idea to present the option of All Users in the user selection list.  If this option was selected – by itself – it meant the user wants to see the summary of all users per report. 

My dataset is embedded in the report – not best practice but is the easiest under current circumstances.  This meant that to determine is a user was in the selected list all I had to code was:

UserName IN (@UserList)

The issue came in when I test in my query if the only selected user was All Users.  If there was only one selection from the list a simple test of @UserList = ‘  All Users  ‘ would work.  If more than one value was selected, I started getting errors.

I got around this issue by creating a new internal parameter.  I set this parameter to ‘MultiValue’ if more than one selection was made.  If only one selection was made, I set the new parameter to the selected item.  The default function for the new parameter looks like:


I now had a parameter I could test against to render the results I wanted.  If the new parameter was the All Users option, I set the User Name to All Users instead of the actual user name.  For readability, I did the initial query as a CTE and then grouped on the information in the CTE.  I had not thought to use characteristics of parameters as internal parameters until this need arose.  I am now considering where this might be helpful in other situations.

March 17, 2011

Cascading Parameters

Filed under: SSRS — sqlpam @ 10:22 pm

Cascading Parameters

I have attended Mike Davis’ session on Advanced Parameters for SSRS several times now.  One of the coolest features he brings up during the session is cascading parameters.  In my recent online presentation for 24 Hours of PASS, I was asked how to make this happen.  Since I have not been able to find a write up by Mike to point to, I will take a swing at it here.

First, cascading parameters are when one parameter’s selection options are determined by another parameter’s selected criteria.  The example Mike uses is allowing the user to narrow down city selections by allowing them to define the state first.  Once the state is selected, only cities with that state will be presented as options. 

In my example, I will use some data I have on hand for a current project for the LPGA.  They often need to select the tournament to review.  Since they have data for 50 years, it would be nice to narrow it down to a single year.  So we will present the user with 2 parameters, the Year and the Tournament.

The first thing I do is set up my dataset to present the Tournament options to the user: 



Please note that I am filtering my list based on the Parameter @Year.  Normally I would add additional filters and order by options – but we are keeping this simple.

Next, I set up the parameter for the Tournament.  In this case, I will present the Tournament Name to the user, but actually grab the TournamentID as the parameter to utilize.  The setup looks like this: 


Please note that @Year must be presented before @TournamentID or there will be issues because @Year must be available for the data to pull the Tournament list correctly.

What the user sees is: 

Note they have not options for the Tournament selection when the parameters are first presented.  Once the year is entered they have options: 

Thanks Mike for making it easy.  This works starting SSRS 2005.

Blog at WordPress.com.