Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a Tabbed Report in SSRS


By:   |   Read Comments (13)   |   Related Tips: > Reporting Services Formatting


SQL Server Conference Giveaway - click to learn more


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 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.

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.

Adding New Report

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 and Quarter. After creating the Main dataset, the Report Data pane looks like the below image.

Report Data Pane Window

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.

Report Parameter General Changes

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.

Report Parameter Default Tab Changes

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.
Adding Tablix

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.

Adding Textboxs For Tabs

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.

Year 2005 Tab Textbox 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.

Year 2005 Tab Textbox Properties Action Tab Properties

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 F4 key, 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.

Year 2005 Tab Background Color On Click

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.

Quarter Q1 Tab Textbox Properties

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.

Quarter Q1 Tab Textbox Properties Action Tab

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.

Quarter Q1 Tab On Click Background Color
Step 5.5.1: Repeat step 5.5 for the other three remaining tabs. Make sure you change the Quarter parameter value in the expression for each quarter (1-4).

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 choose Advance Mode. Once you click on Advance Mode it will show all the static rows under Row Groups and Column Groups.

Tablix Column Groups Advance Mode

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.

Tablix Header Stati RowGroup Properties

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.

Report Preview
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, November 08, 2015 - 12:09:17 AM - Kris Back To Top

Thanks for educating the community.

Nice article.

can you write an article usage of Variables in SSRS?

 


Friday, June 26, 2015 - 7:22:02 AM - Ghanesh Back To Top

Hi Paul,

Thanks, be in tocuh for more tips and tricks.

Ghanesh

 


Friday, June 26, 2015 - 7:20:43 AM - Ghanesh Back To Top

Hi Bhushan,

you are right in this case it will only export the filtered data.

I also understood your question, please find below my suggestion.

Create two tablix.

First tablix is what I have created

Second tablix is without any year and quarter parameter.

You have to do additional work on visibility part, When you are viewing your report in report manager in that case mark second tablix to hidden and first tablix to visible and when user export the report into excel or pdf then mark first tablix to hidden and second tablix to visible.

I hope it will help you, good luck.

Regards

Ghanesh


Friday, June 26, 2015 - 2:23:33 AM - Paul Caballero Back To Top

Nice Article! More tips and tricks...


Thursday, June 25, 2015 - 4:06:03 PM - Bhushan Karle Back To Top

This is a great example. I have the next question. Doing it this way, the result set will only contain the particular selected quarter's data, right? so If the user exports to Excel or PDF, he/she is going to get only that quarter's data on the exported output, correct?

What if the user wanted ALL years' and all quarter's data in a reports that can then be exported, what woudl you do? 


Wednesday, June 24, 2015 - 2:35:27 PM - Ghanesh Prasad Back To Top

AmitK, jeff_yao ,sankar

Thanks for your comment

Regards

Ghanesh


Wednesday, June 24, 2015 - 2:32:50 PM - Ghanesh Prasad Back To Top

Hi Gowri,

As per my understanding we can not create tabs automcaitally. Do let me know if you find any solution.

Regards,

Ghanesh


Wednesday, June 24, 2015 - 2:30:44 PM - Ghanesh Prasad Back To Top

Hello Jagadish,

You are right SSRS is not fully compatible lastest IE11 , I have already covered the solution for that problem, Soon my tip will be published on how to make SSRS compatible with IE11.

Regards Ghanesh

 


Wednesday, June 24, 2015 - 12:24:03 PM - Jagadish Back To Top

We have used same like TABBED report in my project in SCORE CARD report. But some IE version problem. Latest version of IE is not displaying full data, if it is old version of IE working fine. MICRO SOFT will release next patch then this problem will fix.

Thanks,

Jag


Tuesday, June 23, 2015 - 4:37:56 PM - jeff_yao Back To Top

Indeed a very high quality article, enjoy it a lot.

 

Thanks Ghanesh, and looking for such type of tips..


Tuesday, June 23, 2015 - 9:03:34 AM - AmitK Back To Top

Hi Ghanesh,

Thanks a lot for posting nice article on tabbed report. I was able to create report though i am new to SSRS.

Keep posting !!


Tuesday, June 23, 2015 - 8:54:42 AM - Gowri Back To Top

Very great article to overcome shortcomings in SSRS. How do you make this dynamic so any new years are automatically shown as tabs?


Tuesday, June 23, 2015 - 6:52:07 AM - sankar Back To Top

Very Nice artical.


Learn more about SQL Server tools