This article shows you a couple of tips and tricks to get the most out of your SQL Server Reporting Services reports.
Think before you act
When you are tasked with designing a report, you better start prepared. First of all, it is important that you understand the business. Get to know the company, its departments and the people who work there. It’s easier to develop a report if you understood the message it was supposed to bring and the kind of impact it can make. If you need to create a report for the sales department, it helps if you know what margins are. If you need to create a financial report, it helps if you know what accounts are and why profits are stored as negative values.
Also, make sure that you understand the requirements for the report. “Show me a report of the budgets and actuals please for the last quarter” is a bit thin for a requirement. There are probably a dozen of possible reports that you can create to satisfy this requirements. Sit down with the business people and try to outline as much as you can. Maybe make a rough draft with a pencil and paper. Remember though that designing a report is an iterative process: chances are, you won’t get it right the first time.
Finally, pay attention to the report audience. Is the report intended for a quick glance by the CEO, or are operational people going to look at it multiple times a day? The audience has a big influence on how the data should look like, but also on the granularity of the data. Upper management is typically interested in aggregated facts over larger periods of time, while operational reporting concerns itself about detailed facts, possible over the course of just a single day.
Marlon Ribunal has a great write-up on being prepared: Laying The Ground For A Successful Reporting Project.
Choose the right type of visualization
A visualization, be it a bar chart, a pie diagram or a bubble chart, is a form of communication. The goal of visualization is to get a message across. Charts can be divided into different categories, depending on the kind of message that you want to send. When you design the report, you need to think about which message the report is supposed to deliver and how you are going to visualize it. For example, to compare categories with each other, bar charts – or column charts if you flip them over – are best suited.
When you want to analyse an evolution over time, a line chart is a better choice than a column chart. The column chart focuses on individual quantities, while the line chart is less focused on those values, but more on the trends displayed over time.
The pie chart is a bit of a special example, because it is often misused. The purpose of this chart is to show a “part of a whole” relationship. For instance, to show the relevance of sales of a particular category compared to all the other categories.
This pie chart illustrates clearly the dominance of the Bikes category over the other categories. There are no data labels necessary to convey this point. Because the first slice starts at 12 o’clock, you can easily estimate that bikes sales go over 75% of sales. You can set the starting point of a pie chart in Reporting Services by setting the PieStartAngle property to 270. You can find this property under CustomAttributes of the chart series.
The problem with pie charts is that the human brain has a hard time judging the actual sizes of the slices. Therefore, it is difficult to see what actual value a slice represents. Often, this is even made more difficult because the pie chart has too many slices. In most cases, a pie chart can be converted quite easily to a bar chart which allows easier comparison of the category values. The pie chart in the example above shows the exact same values as the bar chart at the beginning of this section. The value of the Accessories category can easily be distinguished in the bar chart, but it is almost impossible with the pie chart, unless you add labels that would only clutter the chart.
Ideally, a pie chart has only a few slices (certainly not more than 5). With Reporting Services, you can group multiple slices into one slice to solve this issue. Valentino Vranken explains this technique in his article Pie Chart Techniques. The following screenshot gives you a clear “before and after” example.
So if you really want to use a pie chart, keep these rules in mind:
Keep the number of slices low.
Order the slices according to their size.
Make sure the most important slice starts at the top (12 o’clock).
Use labels if necessary, because the area of a slice is hard to judge.
Do not use 3D. Ever. (This goes up for any kind of chart).
In conclusion, for every kind of message, there is a type of chart. The following book gives an excellent overview: Say It With Charts: The Executive’s Guide to Visual Communication.
Pay attention to the layout
This may not come as a surprise to you, but usually reports should look good. The problem is that this is, of course, very subjective. Luckily, a few guidelines can be established. First of all: don’t overdo it. Don’t add fancy logos and pretty pictures everywhere. Your report should look professional and must only draw attention to the data, it’s not supposed to be a children’s colouring book. At the risk of sounding overly cliché: less is more. Sure, that snappy little animation when you click on a chart seems cute the first few times, but it probably will get really frustrating if you have to use the report every day.
A good measure for the layout of the report is the data/ink ratio. With ink we mean every coloured pixel on the screen or literally the ink on a piece of paper when the report is printed. The goal is to maximize this ratio. Ideally, all of the ink is used to display data. However, some non-data ink is necessary to support visualization, such as the axis, the labels, the titles etcetera. Everything else should be removed from the report. The following example shows first a visualization where the ratio is out of balance: the gridlines and the labels are superfluous and of course the black background doesn’t help. The next visualization shows the same data, but only the necessary ink is kept.
A report should try to focus the attention of the reader on the most important data. There are a lot of different methods, such as putting something in bold or in a colour. See how your eyes are drawn to these two words in this paragraph?
However, my previous advice still stands: don’t overdo it. If you use too much colour for example, it will lose its effect and nothing will stand out. It is much clearer from the first example that Nauru and Vanuatu are outliers and are in need of our attention.
A lot can be written about the design of reports and I barely scratched the surface. A very good book about designing dashboards is Information Dashboard Design by Stephen Few. A must read if you want to know more about designing successful dashboards. Its principles can be applied to other types of reports as well.
And don’t forget about performance
You may have the most beautiful and efficient report in the history of mankind, but if it takes too long to load no one will ever use it. People have grown more and more impatient over the years and even waiting a few seconds can cause irritation. For very important reports, users can do the “I launch it and then I go grab a cup of coffee or two”, but all in all, this result only in lack of faith and trust in the business intelligence solution and skills of the BI team. So it is safe to say performance is key in the success of a report.
The first places to start are the datasets that provide the report with data. Try to lift as much burden from the report as possible by incorporating logic into the source queries. Tablixes in SSRS have the possibility to sort and filter the data, but a relational database system such as SQL Server can handle this much more efficiently. Move grouping, pivoting, sorting, filtering, calculations and other data manipulations to the query and let SSRS only worry about creating the layout. Ideally, the result set of the query is exactly the same as what will be shown in the report.
Of course, your source should also be tuned for efficient read access. Usually, this is done by constructing a data warehouse that will publish the data to the reports. Dimensional data warehouses are denormalised in the form of star schemas in order to speed up queries. In SQL Server 2012, the columnstore index was introduced. These indexes can give massive performance gains for traditional data warehouse queries.
Another option is to use an OLAP cube such as Analysis Services Multidimensional – which optimizes queries by pre-calculating aggregates – or to use an in-memory solution such as Analysis Services Tabular. For a discussion about SSAS Multidimensional versus Tabular, please refer to Choosing between Tabular and Multidimensional Analysis Services.
You can verify the time the report took to retrieve the source data versus the time it took to generate the report itself using the execution log found in the ReportServer database. There are a few views, but the ExecutionLog3 view is the most interesting one. Here, you can find three columns of particular interest: TimeDataRetrieval, TimeProcessing and TimeRendering. By optimizing the source and the query, you can drastically reduce TimeDataRetrieval. TimeRendering indicates the time spent to render the report in a particular format, for example HTML or PDF. It’s difficult to get this measure down, since it is mostly influenced by the size and layout of the report. You can have an influence though on TimeProcessing by putting as much calculation as possible in the source query – as discussed in previous paragraphs, with the risk of rising TimeDataRetrieval. Another step you can take is to avoid resource intensive visualizations. For example, consider you have a very wide matrix with an indicator in every cell.This can slow down the report for large number of columns and rows, since too many indicators have to be calculated. In this case, a fast alternative is to use a font instead of indicators. I explained this trick here in more detail. The execution log has other useful information stored, for more information read the article Report Server Execution Log and the ExecutionLog3 View.
However, sometimes it just doesn’t work out the way you want. You could have no control over the source and just be given a stored procedure to use, or the report is just too big. Luckily, there are other options to control performance. When the report is deployed to a server (be it Reporting Services in Native Mode or in SharePoint mode), there are various performance enhancing options. The first one is caching. When a user opens a cached report, the time to retrieve it is drastically reduced. It is possible to preload the cache by specifying a schedule to refresh the cache. For more detailed information: Caching Reports (SSRS).
The second option is to create snapshots. A snapshot stores the layout information and the query results that were retrieved on a specific point in time. When the snapshot is viewed, it is rendered in the final format. Snapshots can be manually created or they can be generated by a schedule. It is important that users realize they are viewing historical data. If real-time reporting is required, snapshots and caching might not be good choices. For more information: Add a Snapshot to Report History (Report Manager).
For information on performance optimizations please refer to the SQLCAT (Customer Advisory Team) article Reporting Services Performance and Optimization.
Note: the examples used in this article can be downloaded here.