Tab Menu In SSRS Report

Introduction

The objective of this article is to share the trick of implementing tab menu in SSRS report. Let us consider we have product sales data along with company name and selling country. We can provide sales analysis by country, by company, and by product.

report

Below are the steps to develop a report like above.

Step 1

Create a script like below which will pull the Company Name, Product Name, Sales Country and Sales Amount

Note 

I use AdventureWorksLT2008R2 Data Base,

  1. Select  
  2. D.CompanyName,  
  3. C.Name as ProductName,  
  4. F.CountryRegion as SalesCountry,  
  5. Sum(B.UnitPrice) as SalAmount  
  6. From  
  7. SalesLT.SalesOrderHeader As A  
  8. inner join SalesLT.SalesOrderDetail As B on (A.SalesOrderID = B.SalesOrderID)  
  9. inner join SalesLT.Product As C on (B.ProductID = C.ProductID)  
  10. inner join SalesLT.Customer As D on (D.CustomerID = A.CustomerID)  
  11. inner join SalesLT.CustomerAddress As E on (D.CustomerID = E.CustomerID)  
  12. inner join SalesLT.Address As F on (F.AddressID = E.AddressID)  
  13. group by  
  14. F.CountryRegion,  
  15. D.CompanyName,  
  16. C.Name  
code

Step 2

Create one data source pointing to AdventureWorksLT2008R2 and one dataset named as SALES_DATA with query written in step 1.

source

Create one parameter with Text as Data type, visibility property as hidden and Default value as Country and name it as Menu_Value.

Menu_Value

Set default value as country,

default value

Step 3

Add header and footer in the report and give report title in header part. Drag and drop three text boxes inside the report then name it by Country, by Company, and by Product

Note

Place the text boxes one by one without gap which will give menu effect when report renders.

report

Select By Country text box right Click -> Text box properties -> Font -> give below expression in the color,
  1. =iif(Parameters!Menu_Value.Value="Country","White","Black")  
properties

Goto Fill Properties -> Fill Color -> give the below expression,
  1. =iif(Parameters!Menu_Value.Value="Country","SteelBlue","WhiteSmoke")  
Properties

Go to Action property -> select “Go to report” action then pick the same report name from the drop down so when the user clicks a report comes to the same report,

Select parameter Menu_Name in Name and type country in value.

Menu_Name

Repeat the above three steps for both By Company text box and By product Text box.

By Company text box Expression

Font
 
  1. =iif(Parameters!Menu_Value.Value="Company","White","Black")  
Fill 
  1. =iif(Parameters!Menu_Value.Value="Company","SteelBlue","WhiteSmoke")  
Action - Action property -> select “Go to report” -> Value = Menu_Name (select from droup down) Value = Comapany.

By Product text box Expression

Font
  1. =iif(Parameters!Menu_Value.Value="Product","White","Black")  
Fill 
  1. =iif(Parameters!Menu_Value.Value="Product","SteelBlue","WhiteSmoke")  
Action - Action property -> select “Go to report” -> Value = Menu_Name (select from droup down) Value = Product.

Step 4

Drag and drop tablix into the report then first add Amount field into the tablix.

tablix

Go to row group select (details) right click -> Add Group -> Parent Group..-> Give below expression in the group value,
  1. =iif(Parameters!Menu_Value.Value="Product",Fields!SalesCountry.Value,Fields!ProductName.Value)  
Select add header group check box

 check box

Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,
  1. =iif(Parameters!Menu_Value.Value="Product","Sales Country","Product Name")  
properties
 
Now select the (Detail) in row group right click -> Group Properties -> Visibility -> Select hide radio button -> select display can be toggled by this item check box then select Group1 from drop down.

Properties

Go to row group select (Group1) right click -> Add Group -> Parent Group..-> Give below expression in the group value,
  1. =iif(Parameters!Menu_Value.Value="Company",Fields!SalesCountry.Value,Fields!CompanyName.Value)  
Select add header group check box

box

Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,
  1. =iif(Parameters!Menu_Value.Value="Company","Sales Country","Company Name")  
expression

Now select the (Group1) in row group right click -> Group Properties -> Visibility -> Select hide radio button -> select display can be toggled by this item check box then select Group2 from drop down.

Properties

Go to row group select (Group2) right click -> Add Group -> Parent Group..-> Give below expression in the group value,
  1. =iif(Parameters!Menu_Value.Value="Country",Fields!SalesCountry.Value,iif(Parameters!Menu_Value.Value="Company",Fields!CompanyName.Value,Fields!ProductName.Value))  
Select add header group check box

group check box

Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,
  1. =iif(Parameters!Menu_Value.Value="Country","Sales Country",iif(Parameters!Menu_Value.Value="Company","Company Name","Product Name"))  
properties

Now select the (Group2) in row group right click -> Group Properties -> Visibility -> Select hide radio button -> select display can be toggled by this item check box then select Group3 from drop down.

properties

Format the tablix header with fill color = SteelBlue and font color = White

Now add Sal Amount total like below,

Sal Amount

Add the same in the above cell also.

Result

Result

Result

Result