Have you ever struggled with creating tabs for a SQL Server Reporting Services report? If yes, then you will most likely know that SQL Server Reporting Services does not provide any built in feature to accomplish this task. Finding a suitable solution can be very tricky. So how can we create a tabbed report in SSRS?
In this tip I will give a demo on how to create a tabbed report in SSRS? To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.
This tip assumes that you have previous real world work experience building a simple SSRS Report.
Problem
Have you ever struggled with creating tabs for a SQL Server Reporting Services report? If yes, then you will most likely know that SQL Server Reporting Services does not provide any built in feature to accomplish this task. Finding a suitable solution can be very tricky. So how can we create a tabbed report in SSRS?
Solution
In this tip I will give a demo on how to create a tabbed report in SSRS? To demonstrate the solution, I will use theAdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.
This tip assumes that you have previous real world work experience building a simple SSRS Report.
Step 1: Create New SSRS Report
Let's create a sample report for demo purposes. Please change the report name to SSRS_TABBED_REPORT. You can refer to the below image.
Step 2: Create SSRS Data Source and Dataset
I have already created a data source connection to the AdventureworksDW2008R2 sample database. Let's create the below dataset for this sample report.
Main Dataset: This dataset will be used for the report body and it has two query parameters @Year and @Quarter. This dataset returns three data fields CalendarYear, CalendarQuarter and SalesAmount.
SELECT DimDate.CalendarYear, DimDate.CalendarQuarter, FactInternetSales.SalesAmount FROM DimDate INNER JOIN FactInternetSales ON DimDate.DateKey = FactInternetSales.OrderDateKey WHERE (DimDate.CalendarYear = @Year) AND (DimDate.CalendarQuarter = @Quarter) ORDER BY DimDate.CalendarYear, DimDate.CalendarQuarter
On successful creation, SQL Server Reporting Services will automatically create two report parameters Year andQuarter. After creating the Main dataset, the Report Data pane looks like the below image.
Step 3: SSRS Report Parameters Configuration
Please make the below changes for both the Year and Quarter report parameters.
Hide the parameters
Our sample report has two report parameters Year and Report. As you know we want to implement a tab navigational structure in SSRS, so we have to hide these two parameters. You must be thinking how will the parameter values be passed into the query? Actually we have to create tabs for each parameter and its value; each tab will have one parameter value assigned.
When a user clicks on a particular tab the corresponding parameter value will be passed into the query i.e. suppose you have a year parameter which has two values 2005 and 2006 then we will create two tabs one for 2005 and another for 2006. If the user clicks on the 2005 tab, then 2005 will be passed into the query or if the user clicks on the 2006 tab then 2006 will be passed into the query. I will show how to create tabs in the upcoming steps. You can refer to the below image to hide both report parameters.
Set default values
We have to set a default value for both of the report parameters. For the first time when the report will be previewed these default values will be passed into the query and results will be displayed for the default parameter values. We are assigning 2005 as the default value for the Year parameter and 1 as the default value for the Quarter parameter. You can refer to the below image.
Step 4: Add Tablix
Let's add a Tablix for data viewing purposes. I will pull all three data fields from the Main Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.
Step 5: Implementation for a Tabbed Report in SSRS
Step 5.1: We have to add a few text boxes which will be treated as tabs in the report. The text box can either display the parameter value or any meaningful value. As you can see from the below image, first I added a report header and then I added four text boxes in the first row. These four text boxes will be treated as four tabs for four different years and each text box displays the Year parameter value. I also added four more text boxes in the second row, these four text boxes will be treated as four tabs for four different Quarters and each text box displays the Quarter parameter value as shown below.
Step 5.2: Right click on the first text box which displays 2005 and click on Text Box Properties, you can refer to the below image. It will open a Text Box Properties Window.
You have to navigate to the Action tab, here we will implement On Click logic. Our On Click logic is when a user clicks on a tab then an On Click event will call the same report and if the report has parameters it will add the parameters and assign the parameter values in the Action tab. As you can see from the below image, I am calling the same report and this report has two parameters Year and Quarter, so I am adding the parameters too.
Step 5.2.1: Repeat step 5.2 for the other three remaining tabs. Make sure you change the Year parameter values, i.e. the Text Box which displays 2006 will have a Year parameter value of 2006, the Text Box which displays 2007 will have Year parameter value to 2007, etc.
Step 5.3: For the On Click event we want to highlight the tab selection i.e. if the user clicks on 2006 then it should be highlighted. When the tab is clicked, we will change the background color. Select the first text box and press the F4key, it will open a properties window. In the BackgroundColor value section please use the below expression.
=IIF(Parameters!Year.Value=2005,"LightSteelBlue","No Color")
You can refer to below image.
Step 5.3.1: Repeat step 5.3 for the other three remaining tabs. Make sure you change the Year parameter value in the expression.
Step 5.4 We have already configured the Year tabs in previous steps, now we will configure the Quarter tabs. Right click on the first Text Box from the second row (Text Box which displays Q1) and click on Text Box Properties; you can refer to the below image. It will open a Text Box Properties window.
You have to navigate to the Action tab and make the changes as shown in the below image. Here I am calling the same report and this report has two parameters Year and Quarter. For the Quarter parameter I assign a value of 1 (for quarter 1) and the Year parameter value can be picked from the default/current @Year parameter value as shown below.
Step 5.4.1: Repeat step 5.4 for other three remaining tabs. Make sure you change the Quarter parameter value for each quarter (1-4).
Step 5.5: For the On Click event we have to highlight the current tab selection, like we did for year. We will change the background color when the tab is clicked. Select the first text box and press the F4 key, it will open a properties window. In the BackgroundColor value section please use the below expression.
=IIF(Parameters!Quarter.Value=1,"LightSteelBlue","No Color")
You can refer to below image.
Step 6: Repeat Tablix Header for Tabbed Report in SSRS
If the report will have more than one page, then the Tablix header will be missing from the second page. We have to show the Tablix header on each page of the report. To do this, please click on the arrow as shown below and chooseAdvance Mode. Once you click on Advance Mode it will show all the static rows under Row Groups and Column Groups.
In the Row groups, select the first static row and then press the F4 key, it will open the properties window. Please make the below highlighted changes in the properties KeepWithGroup = After and RepeatOnNewPage = True.
Step 7: Preview Tabbed Report in SSRS
We have done all necessary changes, now let's preview the report. As you can see in the left image, 2005 and Q1 Tabs are highlighted. This is because we assigned 2005 and 1 as the default values for Year and Quarter parameters respectively. The report is not showing any data for 2005 and Q1, because there is no data for this combination.
In the report on the right, we can see data when 2005 and Q3 are selected.
