November 24, 2013

Section based headers – SSRS

I got an interesting requirement this week. My client has requested for Claim Movement report in different currency and currency profile. He also asked whether it is possible to show exchange rate only for certain profile. I don’t want to add another column; instead I went to show an additional header only for certain currency profile

Example output

clip_image001

Let us take a simple example from Adventure works to illustrate the problem

Database: AdventureWorksDW2008R2

Tables: FactInternetSales, DimCurrency and DimSalesTerritory

Scenario: Report the Sale amount grouped by Territory and currency. Also show an additional header with “Test” only when the customer name is “US Dollar”

Create a report with below Report Query: (Let us use the SSRS grouping)

SELECT ster.SalesTerritoryRegion RegionName

,cur.CurrencyName

, fis.SalesAmount

FROM [dbo].[FactInternetSales] fis

JOIN dbo.DimSalesTerritory ster ON fis.SalesTerritoryKey = ster.SalesTerritoryKey

JOIN dbo.DimCurrency cur ON cur.CurrencyKey = fis.CurrencyKey


Group the results based on Territory and Currency in the SSRS.

image

Section the reports based on Territory

clip_image002

Sample view of the report


Page 1:

clip_image003

Last page:

clip_image004

Now we want to show an additional header just for the USDollar.



Step1 : Insert an additional row above the currency group.


clip_image005

Step 2: Open the grouping pane in advanced mode

clip_image006

Step 3: select the newly added row group column and find the static element

clip_image007

Step 4: Open the properties pane of the Static element and select the visibility Criteria as shown below

clip_image009

Against the hidden parameter enter the below expression. This will show the row only when Currency name is US Dollar

=iif(Fields!CurrencyName.Value = "US Dollar",False,True)

 

Final Output


Page 1: No additional Header for the Canadian Dollar Section

clip_image010

Last Page: Us dollar with additional Test Header

As you can see Headers can be shown based on sections.

clip_image011

Happy Learning Smile

No comments:

Post a Comment