Tuesday, November 2, 2010

Multi Column Report in Reporting Services

Reporting Services in all of its awesomeness is lacking in one area- multi-column (or newspaper style) reports.  It can do it, but only certain rendering formats support it.  One way around it in RS 2008+ is to use the oh so handy tablix control and the TSQL row_number() function.  Here’s how it works:
      1.  Create a query that returns the information you want.  Include 2 additional columns: Row Number and Column Number.  The purpose of these is to show the x/y coordinates in the tablix group.  The example query I use will pull a list of tables and columns from the information_schema view:
declare @numberOfColumns int = 3

select table_name
      ,[row_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1 ) / @numberOfColumns
      ,[column_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1) % @numberOfColumns
     2. Create a new report, place a matrix (tablix) on the report body, and create the groups as follows:

3.  For the column title I set the value of the expression to show the column number returned by the query: ="Column " & Fields!column_number.Value

4.  Add some alternating background coloring to make the report more readable : =iif(RunningValue(Fields!table_name.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

And presto!  When you view your report it will show you a nicely formatted multiple column report.  For fun, you can make the @numberOfColumns a parameter for the report and allow dynamic column counts.

No comments:

Post a Comment