SQL Reporting Services: Report Data in Report Header and Report Footer

Any Report in Reporting Services can have a Report Header and / or Report Footer that are placed at top and bottom of Report Page. By default both of them are turned off but can be turned on simply by clicking Report Menu Item (from Layout Tab) and enabling Report Header and Report Footer as is depicted below.

Below are restrictions on Report Header / Report Footer:

* Header and Footers can contain only static data (text, Images,lines, rectangles, Borders etc)

* It can not contain Report Items like Tables, Matrix, List etc.

* TextBoxes in Report Footer or Report header can not reference Data from Dataset. But using Expressions ReportItems or Parameters Collections can be used.

* Expressions in TextBoxes can not refer more than 2 ReportItems from Reports

* Expressions can not contain functions like RowNumber() with scope as Dataset.

In Some cases, it would be helpful to have Data from Dataset in Report Footer or Report header and typically that could be consistent across all pages in Report. For example Report Metadata (that could not be generated from Built in reporting services functions) or Template Reports that could be reused across organizations may have these enabled.

All such reports can be created using ReportItems or Parameters Collections.

ReportItems Collection:

Creating New Dataset:

 

 

DataSource:

Connecting to AdventureWorksDW database (downloaded from www.codeplex.com)

Query for Dataset:

SELECT       
    DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName,
    FactResellerSales.OrderQuantity, DimProduct.EnglishProductName
FROM           
DimProduct INNER JOIN DimProductSubcategory
    ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN DimProductCategory
    ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
INNER JOIN FactResellerSales
    ON DimProduct.ProductKey = FactResellerSales.ProductKey

 

Enabling Page Footer:

 

Report Body

Generate a report in normal way one would develop report with Table Report Item.

Steps:

1. Drop a table Report Item from toolbox:

Configure table report Item with dataset created in Data tab and set toggling. Post configuration Table Report Item should be as below:

2. Create table Groups – Right Click on left most columns ( ) and click on Insert Group.

Enter Group Name and Select Grouping Columns as below:

 

Defining Toggling by going to Visibility tab.

For drill down enable toggling for each grouping in table:

 

Post all configurations table Report Item should look as below.

In Report Footer, we would show Product Category and corresponding Order Quantity, so drag and drop 4 text boxes as shown below:

 

Top 2 text boxes are for headings and bottom one is where table cells from Report Body are referred. To refer cells in Table write expressions for values as below:

=ReportItems!EnglishProductCategoryName.Value

Where ReportItems is a collection are report level.

EnglishProductCategory is textbox in table (Remember in table each cell of table (intersection of row and column is a textbox).

Then set visibility to false to Row and columns that show ProductCategory (DO NOT DELETE IT) and it is shown only in footer.

Using this indirect method Textboxes in Report Footer and Header can refer Data from Dataset.

In next we would indulge in how to use Parameters in Report Header and Report Footers.