Thursday, October 9, 2008

MS Reporting Services 2005 non-standard layout tip

One of my collegues asked me to find a way to display data in a SSRS 05 report in three columns, much like an address cards page. The obvious way would be to just have three datasets, but this is not an ideal approach.

I've been checking out some different possibilities and came up with more then one solution (which is always a good thing, right?)
A common factor in all this is that you need row numbers. Alltough SSRS supplies rownumbers trough the RowNumber() function, you can not use this function in either grouping or filtering, so I needed an alternative.
Looking into getting the rownumber from the database, I decided to have a go with the Row_Number() statement in T-SQL. This proved to be a winner.

I've created two solutions by using the row number. One approach is to assign each row a number according to the column that the row should be displayed in, simply by deviding the row_number by three and using the remainder as my group number.
Then I used three lists that filter the data on the group number. Inside the list I use a rectangle as a container for the data, so each row takes the same physical space in the report.

Another way of doing things is using a matrix and calculation two numbers, one group number, like with the lists approach, and one number that is calculated by adding 0.5 (this is needed for rounding purposed) dividing by three and rounding the result. This results in each set of three records having a number (so record 1 trough 3 have number 1 and records 4 trough 6 have number 2, etc.). Now you can use the first field as a groupfield for the columns and the second field as a groupfield for the rows.

Another way that might work (haven't tested this one) is to use a table.

No comments:

Post a Comment