SQLPam's Blog

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.

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.

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.