Repeat Tablix Row Header on each page (SSRS 2008)

Getting SSRS 2008 to repeat the Tablix header is not a straight forward setting and requires some additional steps to do so.
This used to be very simple thing till SSRS 2005, there basically we just used to right click on the Table, select the check box “Repeat header rows on each page” and we are done.







We need to take some additional steps to make this working in SSRS 2008 and the primary reason is the Table structure has been changed in SSRS 2008. Now we have “Tablix” in place of “Table” which is the combination of Table and Matrix.
Here are the steps needed to make sure Tablix Row header is shown is each page.
1. Even though the Tablix has Row/Column header properties in the property pane but this does not work especially when the row grouping is used in Tablix.
















2. In order to make sure that the Tablix row header is repeated in each page, we need to go the Advance Mode settings of Tablix.
In the bottom of Report Designer-->Under Groupng Pane-->Column Group-->Click on Advance Mode




3. In the Row Groups pane, click the static member of the row you need to repeat.
4. When we select the Static Member, the corresponding cell in the Tablix will be selected. If this confirms the cell which needs to be repeated, go to the properties of this Static Member.
5. Set the KeepWithGroup property
a) For a Static row that is above a group, select “After”
b) For a Static row that is below the group, select “Before” if the Static Row is below the Group.


6. The last thing would be to set the RepeatOnNewPage property “True”





















For more details please see the below msdn article.
http://msdn.microsoft.com/en-us/library/cc627566.aspx

I hope this helps.

Thanks,
Gaurav

Parameter could not be resolved because it was referenced in an inner subexpression

One day while working on a SSRS report I got an error message “Parameter could not be resolved because it was referenced in an inner subexpression” when I was using parameterized MDX.
Here is the sample MDX where I got this error message

with member [Measures].[Jan SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))'
member [Measures].[Feb SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))'
select {

[Measures].[Jan SalesAmt],

[Measures].[Feb SalesAmt]

} on Columns,

non empty

[Customer].[Customer].[All Customers] on Rows

From [Adventure Works]

This MDX was used in one of the data set where I already declared the parameter “Year2Digit” in my dataset. After spending some time I was able to fix this problem and here is the solution – Remove single quotes from the member expression and this would fix the problem.

Here is the modified MDX...

with member [Measures].[Jan SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))
member [Measures].[Feb SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))
select {

[Measures].[Jan SalesAmt],

[Measures].[Feb SalesAmt]

} on Columns,

non empty

[Customer].[Customer].[All Customers] on Rows

From [Adventure Works]

I hope this helps.

Thanks,
Gaurav

How to fix number of rows per page in SSRS

Recently while working on a SSRS project where we had requirement to limit the number of records in report per page. This was something basically needed to improve the report visibility in the SharePoint page. If we fix the number of records per page in the report then we can control the height of the report and can avoid the vertical scroll bar in the report as well.
In this example I am going to show the 25 rows per page in the report…The steps are mentioned below to achieve this functionality.

1. Add a Row Group (Parent)

2. In the group enter the expression, =CEILING(RowNumber(Nothing)/25)

3. In the above expression 25 is the number of records displayed per page.
4. The CEILING function returns the Returns the smallest integer greater than, or equal to, the given numeric expression.
http://msdn.microsoft.com/en-us/library/aa258240(SQL.80).aspx
5. The RowNumber Returns a running count of the number of rows for the specified scope.
http://msdn.microsoft.com/en-us/library/ms159225.aspx
6. Since we are using “Nothing”, the RowNumber will consider the outermost data region, this can be replaced with the name of the specific data region.
7. Once the group is created, go to the Group properties-->Page Breaks and select the check box “Between each instance of a group”

8. Make sure you have removed the sorting from the Group Properties since in this case this will cause an error.
9. Now at last we can delete the first column which was created when the Group was added since we do not need to display anything here. Please make sure to select the “Delete Columns Only” option while deleting the column.

10. Run the report and it should return 25 records per page.

Hope this article helps…please leave the comment if this is not clear or if you have any questions on this.

Thanks,

Gaurav

Custom SSRS Templates

This is always recommended to use the custom SSRS templates specially when we are working at enterprise level and there are many SQL Reporting projects going on within an organization. Using custom Templates helps to make sure the basic look and feel of the all the reports are same, export to pdf, print report etc… is working fine across without spending additional time in each and every report to fix all these common issues.

Also we can deploy our custom Templates in Visual Studio so that any time when we add a new report file (RDL), we can choose our custom templates which we have already created. In order to deploy these custom templates we need to make sure our custom files ate available in the below location
“C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject”
Once this is done we will able to choose our custom template any time when add the new rdl file from Report project using Business Intelligence Development Studio.

I created two custom report templates
1. Landscape
2. Portrait
Here are the details on what all exist in above two report files.
1. The header and footer are already being added in both the reports.
2. The header section has a text box which would show all the selected parameters in the report. To do this we do not need to modify anything in the Header section and we just need to give parameter names in a hidden parameter.
3. The header section contains company logo in it.
4. The footer part will print the userid of the person who is running the report and page number.
5. The landscape report is used when we need to print the report in landscape format.
6. The portrait report is used when we need to print the report in the portrait format.
7. Both the reports would ensure that export to pdf and print works fine without making any changes.
We should not change the report layout or the size of the report but there always can be exception when we need to increase the width of the report if all the contents are not fitting in the template report.
At last once again I would recommend using the SSRS custom templates while working on SQL Report projects, this helps in saving some time and make sure all the reports are consistent across within an organization.
I hope this helps, please leave comments if you have any questions or thoughts on this.