Everybody who’s online these days uses tabs (even Internet Explorer users!). Yet SQL Server Reporting Services doesn’t natively support tabs for its reports. It does, however, support all the building blocks necessary to create tabs yourself. In this post, we’ll post show you one way to do it.
So when would you want to use tabs? Let’s say you have multiple companies within your organization. Perhaps you’d like the first tab to show consolidated results for the company and then subsequent tabs to show results for each subsequent company. Or perhaps you’d like different tabs for each line of business or sales person?
In our example, we use data from Lawson GL where company is a legal entity and Acct Unit is a profit center.
Let’s use a simple report created by the SSRS report wizard to illustrate:
The idea is simple: Create an additional tablix (a.k.a. table) without data to hold our “tabs” in the header row. Add a hidden parameter for the report, which our tabs drive. The hidden parameter, in turn, drives our dataset. Finally, add some conditional formatting to each tab, so we know which tab is selected.
Here’s how to do it: Insert a table and delete the data row. This table will be the only place where we add/modify/change our tabs.
Add some descriptive names to each column. In this case, let’s called them Company 1, 3, 5, etc., based on the company we wish to see. We also add “All Companies” which is our default tab:
Next, add a hidden parameter to drive our selections:
The default value is zero. This will make sense in a moment if it doesn’t already:
Our dataset query (which started off as “Select * from GLNAMES” in the wizard) will now select companies based on this parameter, or all companies if the parameter is 0.
You can apply this “or @Parameter = 0” technique to any parameters. Use blanks or nulls if the values aren’t numeric.
To tie this all together, we need to add actual tab functionality so that when we click on a tab, our data is updated automatically.
To do this, go back to the tabs and give each one an action:
Do this for all four tabs (or however many you’ve created) changing the parameter under “Use these parameters to run the report” accordingly. Company 1 gets parameter value 1, etc.
Finally, give the tabs custom styling so users can easily see which tab is currently selected, even when it’s not evident from the data (e.g. “All Companies” and “Company 1” look very similar.)
Here, we’ve created custom “BackgroundColor” and “Color” properties for these tabs. You can also use images (selected and not selected) if you prefer.
BackgroundColor: =IIF(Parameters!Company.Value = 0,”SteelBlue”,Nothing)
Color: =IIF(Parameters!Company.Value = 0,”White”,”Black”)
Do the same for each tab, again changing the parameter value you’re expecting inside the “IIF” clause. You can also use the value “Nothing” if “Transparent” or “White” gives you warnings.
Here’s what it looks like when deployed:
Here we used a combination of “go to” report action and a hidden parameter, but there are other ways to achieve this functionality. For example, multiple tablixes can be hidden or shown instead.