Display Given Number of Records per Page
If you want to display only a set number of rows per report page, it is not an easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports. Let us say, you want to have only 20 rows per page, add a group to the report with following expression.
=int((RowNumber(Nothing)-1)/20)
Then select page break at the end option for the group.
Suppressing Drill Down for Total Columns in Matrixes
When you have a matrix in your report, you know that you have the option of displaying row and column totals like this:
If you want to have a navigation to another report if you click any details cell, you can configure the navigation tab. However, the problem with this configuration is that this configuration will allow user sto click the total column and drill through to the navigation report. You cannot suppress this by including a expression because total column will act as one of the other columns.
The following is the query we are using for the above example.
SELECT YEAR(Sales.SalesOrderHeader.OrderDate) AS Year,
Production.Product.Name AS Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Amt
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE ( Production.Product.Name LIKE ‘HL R%’ )
AND ( YEAR(Sales.SalesOrderHeader.OrderDate) IN ( 2001, 2002 ) )
GROUP BY Production.Product.Name,
YEAR(Sales.SalesOrderHeader.OrderDate)
ORDER BY YEAR,
Name
Please note that I have included the where so that it is easy to see the total columns.
To tackle the above issue what we can do is include total values into the query rather than getting it from the reporting services.
SELECT ISNULL(YEAR(Sales.SalesOrderHeader.OrderDate), ‘9999’) AS YEAR,
ISNULL(Production.Product.Name, ‘Total’) AS Name,
SUM(Sales.SalesOrderDetail.LineTotal) AS Amt
FROM Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
WHERE ( Production.Product.Name LIKE ‘HL R%’ )
AND ( YEAR(Sales.SalesOrderHeader.OrderDate) IN ( 2001, 2002 ) )
GROUP BY Production.Product.Name,
YEAR(Sales.SalesOrderHeader.OrderDate)
WITH CUBE
ORDER BY YEAR,
Name
The CUBE operator will give you total values for the year, and product name. The problem with this query is we cannot have a Total label for the year and for the total, as year is an integer. Therefore, in the above query an arbitrary 9999 is included.
For the total heading, you might have to write a simple expression like, IIF(Fields!Year.Value=”9999″,”Total”,Fields!Year.Value).
This is the output you will get which is the same as above.
Next is to write an expression for the navigation.
=SWITCH(Fields!Year.Value <>9999 AND Fields!Name.Value<>”Total”,”SubReport”)
In the above expression, it is checked for the year and name total values so that it won’t navigate to the subreport.
Final Word
Above are few that I have come across during my SSRS development. Let me know any other issues you have when it comes to developing SSRS. In addition, if you need samples for above instances, I am just happy to send them to you. Drop me a mail to dineshasanka@dbfriend.net
]]>