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.



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,

Select
D.CompanyName,
C.Name as ProductName,
F.CountryRegion as SalesCountry,
Sum(B.UnitPrice) as SalAmount
From
SalesLT.SalesOrderHeader As A
inner join SalesLT.SalesOrderDetail As B on (A.SalesOrderID = B.SalesOrderID)
inner join SalesLT.Product As C on (B.ProductID = C.ProductID)
inner join SalesLT.Customer As D on (D.CustomerID = A.CustomerID)
inner join SalesLT.CustomerAddress As E on (D.CustomerID = E.CustomerID)
inner join SalesLT.Address As F on (F.AddressID = E.AddressID)
group by
F.CountryRegion,
D.CompanyName,
C.Name

Step 2

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



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



Set default value as country,



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.



Select By Country text box right Click -> Text box properties -> Font -> give below expression in the color,

=iif(Parameters!Menu_Value.Value="Country","White","Black")

Goto Fill Properties -> Fill Color -> give the below expression,

=iif(Parameters!Menu_Value.Value="Country","SteelBlue","WhiteSmoke")

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.



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

By Company text box Expression

Font  

=iif(Parameters!Menu_Value.Value="Company","White","Black")

Fill 

=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

=iif(Parameters!Menu_Value.Value="Product","White","Black")

Fill 

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



Go to row group select (details) right click -> Add Group -> Parent Group..-> Give below expression in the group value,

=iif(Parameters!Menu_Value.Value="Product",Fields!SalesCountry.Value,Fields!ProductName.Value)

Select add header group check box



Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,

=iif(Parameters!Menu_Value.Value="Product","Sales Country","Product Name")

 
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.



Go to row group select (Group1) right click -> Add Group -> Parent Group..-> Give below expression in the group value,

=iif(Parameters!Menu_Value.Value="Company",Fields!SalesCountry.Value,Fields!CompanyName.Value)

Select add header group check box



Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,

=iif(Parameters!Menu_Value.Value="Company","Sales Country","Company Name")

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.



Go to row group select (Group2) right click -> Add Group -> Parent Group..-> Give below expression in the group value,

=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



Now select the newly added field group header Right Click -> Text box properties -> Give the below expression in the value,

=iif(Parameters!Menu_Value.Value="Country","Sales Country",iif(Parameters!Menu_Value.Value="Company","Company Name","Product Name"))

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.



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

Now add Sal Amount total like below,



Add the same in the above cell also.

Result