SQL Mighty

Maybe I’m just a programmer at heart…

Maybe I’m taking SSRS places it just doesn’t want to go…

Whatever it is, it’s difficult for me just to create a basic report. When I take on a task to develop new reporting, I look at the big picture and ask myself a few questions:

• How can I make this report more than the users expect?
• What new bells and whistles can I incorporate into the report to blow the users’ socks off?
• How can I make this report run as fast as possible?
• How can I provide the most reporting from the fewest reports?

While trying to achieve that last goal, I’ve found the “Hidden” property to be one of my best friends.  You can hide anything:  textboxes, rows, columns, and even a whole tablix.  Meaning, you can include or hide an entire dataset by hiding the tablix which contains it.  Powerful!

Up until recently, I thought I was using this technique with little overhead.  I’ve used this approach in the past, and my reports run fast.  Well, all of my reports complete quickly except for the last one.

This last solution is actually two reports.  The first report is a summary report which comes from a very small table.  The data is summarized daily, so pulling the data for the report is lightning fast.

However, the second report is a “detail” report.  The user can click one of four links from the summary report to view detail from one of four datasets.  Rather than creating four different detail reports, because there are four distinct datasets for the detail, I decided to incorporate a tablix for each dataset into one report.  Using the Hidden property, I display the tablix with the detail requested from the summary report.  It works.

During testing, I found that no matter which detail report I selected, the report took almost three minutes to return data.  I was willing to accept that time from one of the dataset and do some query optimization later, but it did not make sense to me that all four datasets would take that long to return results.

What did I do?  I ran the queries separately in SQL Server Management Studio, and, sure enough, only one of the queries took a long time.  The other three returned very quickly.  I started to form the conclusion the one long query ran no matter which dataset was selected from the summary report.

Proof?  I modified all of the datasets in the detail report to include the parameter I was passing from the summary report.  Example:

Before

select TransDate, Sales from CompASales

After

select TransDate, Sales from CompASales where @Company = ‘A’

This minor adjustment to all of the datasets stopped the unnecessary queries dead in their tracks.  The datasets would only return data if the data was actually needed in the report.  Now, the detail report runs as quickly as the one query which produces the data needed, not all four.

I went back to some of my older reports to determine why I had not encountered this issue before.  For the most part, the datasets and parameters for the other reports were so different; the parameters for one dataset would not work for another dataset.  Although not deliberately, I was using similar logic as above.  Going forward, I will be more deliberate about the parameters I include in the queries for my datasets.

Do you use hidden objects to use fewer reports?  When has it worked well for you?  When has it caused a headache?

I was recently creating a new report using the interactive sort option in SSRS. While testing the sorting, I noticed that the default sort on a number value/column is ascending. In some cases, this probably makes sense, but when looking for the customers with the highest spend, it does not.

My first approach to solving this was looking for an option that said “ascending” or “descending”, but I found no such option within Interactive Sort in SSRS. I did some searching and found a response that Microsoft now has this as a suggestion for a future release. However, there is a very simple solution!

In order to use Interactive Sort on a column, you must select an expression on which to sort. (Only once have I had the need to choose something other than the value in the column I wanted to sort.) My big idea was just to multiply the expression by -1, and, of course, it works like a charm.

Addendum…

Although this method does work to give you a different default sort, the little arrow at the top of the column contradicts the sort order.  In other words, your default sort order might be descending, but the up arrow will be displayed indicating ascending.  I think it’s minor, but I did notice it and thought I should share.

So, a fix is needed from Microsoft, please.