SQL Server Reporting Services (SSRS) has come a long way since the initial release of SSRS in SQL Server 2000. Early on, many developers and technology managers viewed SSRS as an average report writer that lacked functionality and scalability compared to other established reporting solutions. Fortunately, Microsoft greatly enhanced the SSRS feature set over the last eight years (from SSRS 2008 to the present)-thereby empowering SSRS advocates to make a stronger and more compelling case that SSRS is indeed a prime-time player in the world of business reporting. In this article, I'll demonstrate some tips and reusable approaches in different areas of report authoring/generation that help make that case.
SQL Server Reporting Services: The Ultimate Bread and Butter of Reporting Tools
Recently, I heard someone say, “Third-party report and charting tools might have many bells and whistles, but SQL Server Reporting Services is still the bread and butter for many developers.”
From the late 1990s to 2007, I was a die-hard Crystal Reports fanatic and published a book on Crystal Reports development with .NET. Toward the end of that period, I took a serious look at SQL Server Reporting Services 2005 (I'll use the acronym SSRS for the rest of this article). I concluded that SSRS 2005 was a good but not outstanding product, and lacked functionality compared to Crystal Reports. That changed in SSRS 2008. Microsoft didn't try to match every single bell and whistle of Crystal Reports in SSRS 2008; instead, they expanded their existing reporting (and charting) functionality on their own terms, and greatly improved the scalability and performance of the reporting engine.
So instead of adding the bells and whistles, Microsoft fortified their bread and butter. Although Crystal Reports still had an edge on the total number of features, SSRS 2008 became a truly viable option for internal business reporting. Microsoft continued to expand SSRS in SSRS 2008R2, and has given certain UI areas of the product a much-needed make-over in the upcoming SQL Server 2016 release.
Still, SSRS suffers from a bit of a perception issue. To many, SSRS remains merely adequate, but lacks some of the power found in third-party tools, competing products, and even the quasi-reporting capabilities in Excel. One of my roles as a consultant is to show developers and managers the full power of SSRS and how experienced developers can extend it. Several of the tips in this article reflect those exercises to demonstrate the power of the product.
What's on the Menu?
For over a decade, I've been using the Baker's Dozen theme of giving you 13 tips. In the last year, I've even been writing articles as test or interview questions, and this article will also diverge in that I'm only covering the following eight items:
- Implementing column level security in reports
- Implementing drilldown functionality with options to set initial drilldown
- Creating cross-chart filter refresh effects
- Repeating column headings across multiple pages
- Dealing with page margins and blank extra pages
- Relating multiple tables in a report
- Implementing a nested “Page X of Y” on a report
- Implementing Running Aggregations in a report
I'll present many of these tips by saying, “You want to implement functionality/behavior ABC, but you're running into a limitation. Is there a solution or workaround?” This reflects many scenarios that I encounter in my consulting practice.
Tip #1: Implementing Column-Level Security in Reports
Suppose you build a report that both regular users and executives will view. In addition to other data, the report contains cost and profitability measures that only executives should see. The regular users should only see specified data and the space for the cost columns shouldn't appear. Users should see the report as if the cost and financial measures never existed. Does SSRS provide security features to hide columns based on domain accounts/groups?
The answer is that SSRS doesn't provide any built-in functionality to address column-level security. However, just like many developer products, SSRS doesn't stand in your way from manually implementing such functionality.
Just like many developer products, SSRS doesn't stand in your way from manually implementing column-level security functionality.
I'll keep the example very simple and will focus on the mechanics. Figure 1 shows a very basic report, with a column (“Modified Date”) that you want to configure for visibility based on the current user.
You can implement this in five steps. Essentially, you'll create a SQL Server table to define user/column visibility, and a stored procedure to return that information for the current authenticated user. In the SSRS report, you'll read this information into a hidden/internal SSRS parameter, and tap into the SSRS column visibility feature to show/hide the column based on the SSRS parameter.
Step 1: Create a database table in SQL Server called dbo.RightsToColumns. For simplicity, add a single row for the user domain account name and a flag for whether to see the Modified Date column (Figure 2).
Step 2: Create a basic stored procedure to return the table information for a specific User ID based on a parameter that the report will ultimately supply.
CREATE PROCEDURE [dbo].[GetRightstocolumns]
@UserID varchar(100)
as
select * from RightsToColumns
where UserID = @UserID
Step 3: In the report, create a dataset that reads the stored procedure based on the current authenticated user that SSRS recognizes. Figure 3 shows mapping the SSRS Globals variable called User!UserID to the SSRS stored procedure parameter of the same core name (@UserID). When the report executes, SSRS calls the procedure and pass in the current authenticated user as the parameter.
Step 4: Here is where you bring it all together. You need to collect the results of the procedure (in this case, whether SSRS allows the user to see the column). You can implement a common SSRS pattern: Create a hidden/internal SSRS parameter and map the results of this dataset to the parameter. In Figure 4, I've added an SSRS parameter called ShowModifiedDate (as an internal parameter, so that SSRS never prompts for it), and I've mapped the results of the ColumnVisible column from the dataset to the default value. In the next step, I'll use the value of this parameter to determine column visibility.
Step 5: Finally, in the SSRS designer, right-click on the column separator for the column you want to configure (the “ModifiedDate” column in this example), and select Column Visibility. In the Column Visibility dialog, enter an expression to define whether you should hide the column based on the value of the ShowModifiedDate report parameter. Note that the expression must return a Boolean value based on whether you want to hide the column, so in this case, you're returning the reverse (“not”) of the ShowModifiedDate parameter.
One final note that I didn't demonstrate here: Suppose the ModifiedDate column had another column to the right. If the user running the report doesn't have the rights to view the column, the SSRS engine won't even bother showing an empty space for the column. Instead, the engine shows the report as if the ModifiedDate column never existed. This makes the output appear clean for either scenario.
I've presented an overall approach, a pattern, for solving this problem. You might find that you'll need to greatly enhance this to work for your scenario. But hopefully I've provided a major start on the mechanics.
Tip #2: Implementing Drill-Down Functionality with Options to Set Initial Drill-Down
Sometimes users ask for the same type of functionality that's found in end-user tools like Microsoft Excel. Here's one example: Users want to show an aggregation at a high level (such as Sales by State) and then expand a state to show more detailed information (such as Sales by City for the selected state). Most people refer to this functionality as drill-down, although in practice, you're initially suppressing the display of lower levels of detail (like the city), and toggling the display of that lower level based on how a user clicks on a higher level (like the state).
Figure 6 shows an example of SSRS drilldown. Using the Microsoft AdventureWorks demo database, I've built a report that initially collapses sales by Shipper and Vendor. I need to create a report with two row groups that summarize by Ship Method and Vendor, and I need to create a grand total row (by summarizing all shippers). The user can expand the initial grand total to see the summarized ship methods, and then expand any ship method to see the summarized vendors for that ship method. And, although I haven't displayed it, if the user expands any vendor, the report will show individual purchase orders.
You can implement this in two major steps.
Step 1: In the SSRS Report, I've created two Boolean parameters (Figure 7) that allow the user to determine if SSRS should initially expand or collapse the Ship Methods group and the Vendors group. Although this first step isn't necessary for implementing basic drill-down, it does give the user the ability to define whether SSRS will initially expand or collapse a group (i.e., set each group's ToggleState).
Step 2: To implement drill-down at the Ship Method level, you need to define the initial visibility (based on the value from the ExpandShipMethods parameters in Step 1). You also need to allow the user to toggle the display of the ship methods by the textbox for the Grand Total (so that the user can expand or collapse the grand total to show or suppress the ship methods). In Figure 8, I right-clicked on the row group for ShipMethodName and loaded the Group Properties dialog.
To complete the example, you'd need to follow the same pattern for the Vendor row group, using the ExpandVendors parameter and setting the display toggle to the parent vendor textbox.
Tip #3: Creating Cross-Chart Filtering Refresh Effects
In the last few years, software vendors have released self-service Business Intelligence tools that allow users to create attractive and interactive dashboards and charts. These tools also boast the ability to create powerful visualizations without always needing a developer. Examples of these products include Microsoft Power BI, Tableau, QlikView, Domo, SpotFire, and others. Vendors of these tools often demonstrate (and even promote as a selling point) the ability to create a dashboard page with multiple charts, where the user can click on one data point of one chart within the page, and the tool instantly refreshes other charts to reflect the initial selection.
Here's the question: Does SSRS contain built-in functionality for this type of cross-chart filtering? Similar to Tip #1, the answer is no. SSRS doesn't provide this ability straight out of the box. However (and this becomes a familiar theme), SSRS provides enough open capabilities and hooks to allow you to create a functional workaround.
For example, look at the two charts in Figure 9. The first chart on the left plots sales as a vertical stacked bar chart by country and year. The second chart on the right plots sales by country and month. Suppose you wanted to click on a specific series of data in the first chart (sales in the United Kingdom in 2013) and force all charts on the page to highlight everything associated with U.K./2013 (Figure 10).
I'll throw in a pop-culture reference here. Do you remember the old TV show, “Name that Tune?” The signature response in that show was: “I can name that tune in (N) notes.” Well, I can sing the “cross-chart filter visualization” in three steps. Essentially, you'll tap into the SSRS Report Action feature for a plotted data series point, and re-launch the report. As you do that, you'll pass forward to the report a hidden parameter for the year/country that the user selected. In the reload, you'll set a Fill Color expression to dynamically color the current series in yellow if the current year/country matches the value of the hidden parameter.
I must warn readers that because you're essentially relaunching the same report, this won't perform as instantly as tools that provide this feature out of the box. So use and implement with caution.
Having said all of that, here are the three steps:
Step 1: Create a hidden/internal SSRS parameter called HotSelect. You don't need to provide any special prompts for the parameter, as the user will never see it.
Step 2: For each chart on the report page, go to the Series properties and then the Actions tab (Figure 10). Set the action to Go to report and specify the same report name (to relaunch the report). In the parameters area at the bottom of the dialog box shown in Figure 11, map the HotSelect parameter based on the current tear and country. If you haven't realized it already, this works because SSRS allows you to hook into the Actions area when the user clicks on a chart data point. It also works because SSRS keeps track of the current country/year series when you click on a specific series.
Step 3: For each chart, go to the Series Properties dialog box and the Fill tab (Figure 12). Set the Color Expression to yellow if the current year and country that SSRS is plotting happens to be the same value in the HotSelect parameter.
Before you go any further, let's stop and reflect that you've used hidden/internal parameters as a means to drive different SSRS behavior. It's not a stretch to say that you're implementing some SSRS design patterns here, ones that could be used for other report functions.
Let's stop and reflect that you've used hidden/internal parameters as a means to drive different SSRS behaviors.
Tip #4: Repeating Column Headings Across Multiple Pages
Even the best tools in the world have an Achilles heel, and this topic represents a very strange issue and even stranger workaround in SSRS.
Suppose you implement a standard tabular report (i.e., a fixed number of columns) that spans many pages. When the user views the report in a browser and navigates beyond page one, the SSRS viewer doesn't show the column headings. Although SSRS provides an option in the SSRS table properties to repeat column headings across each page, that option has no affect. So what's going on?
The truth is that the “Repeat header columns on each page” option in the Table/Tablix properties only works for matrix reports. The option has no effect on tabular reports. In order to repeat column headings, you need to tap into the Advanced Mode section at the bottom of the report designer. In Figure 13, I've clicked the down arrow on the far right of the report column groups. Once you go into advanced mode, you'll notice that the Row Groups area now shows several static sections (Figure 14). You'll want to click the F4 key to bring up the full property sheet for each static group above the first non-static group and set the RepeatOnNewPage property to TRUE (Figure 15).
This might go down in history as one of the more obscure workarounds. Even more interesting, this problem didn't exist prior to SSRS 2008. However, an obscure workaround trumps no workaround in any poker game I've ever played!
Tip #5: Dealing with Extra Blank Pages When Exporting Reports
When printing reports or exporting reports to PDF, sometimes the SSRS report engine produces an extra page at the end of the report, or (worse), inserts a blank page between each regular page. The former scenario might not anger users, but the latter scenario definitely requires immediate attention. The question is, why is SSRS doing this and how can you prevent it?
Let's take the latter scenario. This usually occurs because the width of the content body exceeds the allowable width. You can calculate the allowable content body width as the page width (based on the report property orientation: portrait or landscape), less the left/right margins. In Figure 16, the full content body is roughly 10.5 inches, whereas the total page width is 8.5 inches and the left/right margins are one inch each. So the maximum allowable content body is 6.5 inches. (In practice I'd never go beyond 6.3 inches to allow a margin of error).
Some people might address the issue in Figure 16 by simply stretching the report table inward to stay with the allowable width, but they might leave the outer design area at the original width (10.5 inches). As I've shown in Figure 17, you need to also stretch that outer edge of the design area inward to stay within the range, or SSRS generates a blank page at the end.
Tip #6: Relating Multiple Tables in a Report
Suppose you create an SSRS report that contains two datasets. The first dataset contains the primary report data and the second dataset contains lookup information. You'd like the SSRS designer to relate the two datasets based on some common field/key. Unfortunately, the SSRS designer doesn't contain any such feature. Furthermore, the SSRS table (Tablix) report control only allows you to specify one primary dataset to drive the content. Is there any way for the report to show related information from the second dataset?
Before I continue, someone might respond with a suggestion to modify the database code that returns the first result set, and add additional columns so that you don't need the second result set. For this example, assume you can't modify the database code and that you're stuck with two procedures/two datasets. Although this constraint isn't very common, it's certainly not unheard of either.
Prior to SQL Server 2008 R2, this was a very difficult challenge. Fortunately, Microsoft implemented three new functions in SSRS 2008R2 (released in spring 2010). They are Lookup, LookupSet, and MultiLookUp. These functions allow SSRS developers to return information from a secondary SSRS DataSet, so long as the secondary DataSet contains a column that you can relate from the primary dataset.
You can use Lookup to search a second dataset based on a specific common value and return a single column from the second dataset. You can use LookupSet when you want to return multiple columns from the second dataset. Finally, you can use MultiLookUp to lookup in a second dataset based on multiple values.
Here is an excellent blog entry that covers these three functions: http://www.sql-datatools.com/2015/07/lookup-functions-in-ssrs.html
Tip #7: Implementing a Nested “Page X of Y” on a Report
Nearly every report writer provides sufficient pagination features to show “Page X of Y” on reports. However, users might want a nested “Page X of Y”. For example, suppose you're generating a long report (160 pages) of detailed transactions by customer. You want to define overall pagination (Page 1 of 160, Page 2 of 160, etc.), but you also want to define pagination scoped to the customer (Page 1 of 2, if the current customer has two pages of transactions).
Similar to Tip #6, prior to SQL Server 2008R2, developers struggled to implement this. You needed to manually calculate page definitions as part of the result set. This meant determining exactly where page breaks would occur, which might become error-prone if someone changes any aspect of the report layout (report margins, orientation, etc.).
Fortunately, Microsoft implemented a new feature in SSRS 2008R2. Specifically, they redesigned the pagination to implement two sets of page numbering global variables: Globals!OverallPageNumber and Globals!OverallTotalPages, as well as Globals!PageNumber and Globals!TotalPages.
In Figure 18, I've created two textboxes in the page header to use both sets of pagination values. However, that in itself does nothing. SSRS still generates “Page 1 of 166,” “Page 2 of 166,” etc., for both sets of page counts. So what else do you have to do?
SSRS did exactly what you told it to do instead of what you wanted it do to. Even though the report contains a vendor grouping, the report could theoretically contain multiple groups, and SSRS doesn't know which group to use for the nested pagination. You need to provide that information!
In Figure 19, I've pulled up the full-blown property sheet for the Tablix control, and I've set the PageBreak properties (the dataset column that represents the grouping as the PageName property, and a value of TRUE for the ResetPageNumber property).
Tip #8: Implementing Running Aggregations
Even SSRS novices are aware that SSRS allows you to create report subtotals and grand totals very easily. However, does SSRS allow you to show running totals and running aggregations?
Fortunately, the answer is “yes.” Not only that, but SSRS provides some very nice functionality to make this task easy. Figure 20 shows a sample report with three sets of running averages for chronological months in a year.
At first glance, you might think that SSRS provides three separate functions for the three columns. As it turns out, SSRS provides just one function, called RunningValue. You provide the aggregation method as a parameter to the RunningValue function. Figure 21 shows an example.
You pass three parameters to RunningValue:
- The column value you wish to aggregate (the Monthly sales amount)
- The aggregation method (Sum, Avg, Max, etc.). Note that SSRS color-codes the aggregation method in blue, indicating that SSRS treats it as a keyword.
- The scope of the aggregation (the name of the group). In this instance, you're aggregating months within a year. Every running aggregation contains a scope, so you need to provide the actual SSRS group name.
Final thoughts:
I hope I've provided some information to help you in building SSRS reports. In future articles, I'll continue to show different SSRS power tips. As a SQL Server/Business Intelligence contractor/consultant, I've always found that working near the report layer helps me to understand the breadth and depth of the client's business application. Users will always want functionality that the product doesn't provide out of the box, so the value of SSRS depends on whether the tool provides enough hooks or open architecture for developers to extend it. Overall, SSRS does a very good job here.
