Creating a Tabbed Report in SSRS

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

Lookup - How to use multiple datasets in a tablix

1.     Background

The purpose of this article is to provide a way of using multiple datasets in a tablix within SQL Server Reporting services 2008 R2. In all the previous versions of SQL Servers (SQL Server 2005/2008), we can use only single dataset for a table/matrix/chart.

2.     How we can use multiple datasets in a tablix?         

SQL Server Reporting Services 2008 R2 has introduced a new function called “LOOKUP”. LOOKUP function is used to retrieve the value from multiple datasets based on 1 to 1 mapping. For example if we have two datasets and both the datasets have EmpID so based on the EmpID mapping, we can retrieve the data from both the datasets.

Syntax: LOOKUP (Key value from already mapped dataset, Key value from new dataset, new value from new dataset)

3.     Create data source and datasets for the report

                    I.            Create a reporting solution in SQL Server Business Intelligence Development Studio.

                  II.            DataSource Creation:

·         For creating a data source for Report, go to Shared data sourceèright clickè Add New data source

·         Click on Editè Provide the server name and database and click ok.

               

·         Now we can see the connection string for our source server. Click ok.

                         

               III.            Dataset Creation:

·         For creating a dataset, go to Shared datasetsèAdd new dataset

·         Choose the DataSource for the dataset and provide the query:

                       

·         Click ok. Below we can see MyDataset1 has been created:

Follow the same steps for creating MyDataset2.

Query for MyDataset1:

SELECT ProgrammerName,SALARY FROM dbo.Programmer

It returns the programmer name and their salary

Query for MyDataset2:

SELECT ProgrammerName, Skill FROM dbo.Programmer

It returns the programmer name and their skills.

Here we can see the two datasets in the solution explorer:

4.     Create a tablix report with two datasets

                    I.            Create a tablix report : For creating a tablix report, Right click on Reports folderèAddèNew ItemsèReportèGive the name of the report e.g. ProgrammerDetailReport.

                  II.            Insert a tablix in the report: Right click on design surfaceèInsertèTable

 

                III.            Set the dataset for the tablix:

Let’s select MyDataset1 and click ok.

                IV.            Now we can map the columns from MyDataset1 to the tablix:

                  V.            Below is the report after formatting:

                VI.            Now if you see the property of the report for dataset. It will show two options either MyDataset1 or none. Even there is no option for writing the expression. Moreover, in all the previous version of the SQL Server Reporting services, there is no option for mapping multiple datasets to a tablix.

5.     Step by step procedure to implement multiple datasets in a single tablix

                    I.            Add one more column to the report by right click on the last columnèInsert columnèRight

                  II.            Now we can see another column in the report:

                III.            Now right click on the text box of new columnèchoose Expression:

                IV.            Use the LOOKUP function in the expression editor to map both the datasets and click Ok.

Expression:=Lookup(Fields!ProgrammerName.Value,Fields!ProgrammerName.Value,Fields!Skill.Value,"MyDataSet2")

             

                  V.            We can give the column header text as Skill. Now report will looks like below one:

6.     Conclusion

By using the LOOKUP function introduced in SSRS 2008 R2, we can map multiple datasets to a tablix.

----------------------------------------------------End of article---------------------------------------------------

Sparklines in SSRS

In this article, I will show you how to use Sparkline in SSRS.

1. First of all open Visual Studio 2010 and open your SSRS project.Then add report named as DemoSparkline in your project.

Now create a Dataset. In the dataset, we will use following query

?

1 2 3 4 5 6 7 8 9

SELECT      PC.Name AS ProdCat, ROUND(SUM(SOD.LineTotal),2) AS Sales,DATENAME(MONTH, SOH.OrderDate)as OrderMonth FROM        Sales.SalesOrderHeader AS SOH INNER JOIN  Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN  Production.Product AS P ON SOD.ProductID = P.ProductID INNER JOIN  Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN  Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID WHERE       DATEPART(YEAR, SOH.OrderDate) = 2007 GROUP BY    PC.Name,DATENAME(MONTH, SOH.OrderDate) ORDER BY    PC.Name

2. Then right click on report area and go to Insert–>Table.

3. Now select ProdCat in first column.

4. Then right click on ProdCat Textbox and select Row Group –> Group Properties.

5. A Group Properties page opens. In that add Group Expression as shown in below screenshot. Then click on OK button.

6. Now drag & drop Sparkline control into 2nd column.

7. A Select Sparkline Type window opens. In that select Line Type as shown in below screenshot. Then click on OK button.

8. Now your report design looks like below.

9. Then double-click on Sparkline chart and select value in Chart Data window as shown in below screenshot.

So we have done with Sparkline in SSRS.

10. Now click on preview tab to see the results. You will see results like below screenshot:

Congratulations! We successfully created Sparklines in SSRS.

1.     Background

This article describes how to add Spark line and Data bar in the report. Addition of sparkline and small charts in the table and matrix makes report more interactive and simplified the report for the business users.

2.     What is Spark Line?

• Spark lines and Data bars are small, simple charts that convey a lot of information in a little space.
• Spark lines and Data bars are often used in table and matrices.
• Spark lines often represents multiple data points, often over time.
• Data bars can represent multiple data points, but typically illustrate only one.
• Spark lines and Data bars represent a single series.
• We cannot add a spark line to a detail group in a table.
• Spark line always shows aggregated data within a group. (Detail group not applicable)
• Spark lines have the same basic chart elements of categories, series, and values.

Types of Spark lines:

               

3.     Steps to add the Sparkline in the report:

Below is the sample Sales report which shows year wise sales amount for various regions:

We will add Sparkline and Chart data region into the above report to show year wise sales amount for regions.

                       I.            Go to report layout and remove FiscalYear column from the tablix:

After deleting column, report is having only two columns:

                     II.            Preview the report:

                          

Here we can see that region is repeating as we have deleted the fiscal year column so we can group the data by region to avoid duplicate records.

                   III.                     To create a group for SalesRegion, click on Details group in Row Group pane and  select  Group Properties:         

                   IV.                     It will show the Group Properties:

                     V.                     Click on “Add” button and select “Sales Region” and click Ok:

                   VI.                     Now go to tablix and right click on SalesAmount text box and select expression:

            

                 VII.                     It will open the expression builder, add the SUM function to sales Amount:

=SUM(Fields!SalesAmount.Value)

 Click Ok.

                VIII.            Preview the report. It will show the aggregated Sales Amount for SalesRegion:

                           

Now we can see the report showing region wise aggregated Sales Amount.

Note: Now we will add the Sparkline to show progress of Sales Amount for a region over years.

                   IX.            For adding Sparkline, right click on Report layout then Insert and select Sparkline:

                     X.            Select the sparkline type. For this report, we are using line sparkline:

It will show the Sparkline in the report layout:

                   XI.            Set the property for sparkline. As we are planning to show the year wise sales amount in the report then set the below mentioned property:

·         Category Group   : Fiscal Year

·         Value                   : Sales Amount

·         Series                   : Sales Region.

                 XII.            Now add a new column into a tablix named SalesProgress.  Drag and drop the Sparkline into this newly created column:

               XIII.            Now report layout looks like below one:

               XIV.            Now run the report and see whether we are able to see the sales amount progress or not:

                          

Report is showing sales amount progress from start year to till end.

                 XV.            We can add data label to Sparkline. Right click on the lines on the Sparkline and select “Show Data Labels”:

               XVI.            Preview the report

Now report is showing with data labels.

4.     Steps to change the Sparkline type in report:

                       I.            Right click on the spark line box in the report layout and select “Change Sparkline Type”:

It will open the list of spark line. 

We can select any spark line type and click ok.

                     II.            Preview the report

                           

Now we can see the report with different sparkline type.

5.     How to change Sparkline to Chart

                       I.            For Changing Sparkline to Chart, right click on the Sparkline and click on “Convert to Full Chart”

If we want we can change the chart type also by right click on sparkline/chart text box and click on select Change Chart Type :

For Demo purpose, I have selected 3D- Clustered cylinderical chart:

                     II.            Preview the report:

Now report is showing with Chart.

Note: We can directly add Chart also to tablix.

6.     Conclusion

By using the above steps, we can show Sparkline and Chart in the tabular report.

----------------------------------------------------End of Document---------------------------------------------------