Tweaks in SQL Server Reporting Services


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

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |