Reporting Services - Determine Number of Columns Shown in Matrix

Question

Is it possible to determine the number of columns displayed in a Reporting Services report, and use this within the Visibility expression of a separate textbox?

I face the following scenario:

My client has a Reporting Services 2005 report, containing a matrix, which displays a variable number of columns, depending on the parameters selected. On occasion, some users have generated reports with a very large number of columns. This causes a server error when they subsequently attempt to export to Excel, as the Excel 97-2003 format is limited to 256 columns.

I would therefore like to display a message at the top of reports when more than 256 columns are shown in the matrix, advising users of this limitation and suggesting the workaround of exporting to CSV (and viewing in Excel 2007 if they have it).

Answer 1

Would you be able to do a count on the dataset to get your column count?

Count(Fields!FieldUsedAsColumnGrouping.Value,"DatasetName")

Not sure how your data and matrix are setup, I do something similar where I have to know the number of columns so that I can draw an appropriate border line. But I think you should be able to a Count on either the dataset or grouping to get what you are looking for. (I can paste some code come Monday if needed.)

Answer 2

This can be done, I just created a test to prove my theory; here are the basics.

I created a calculated column on my dataset with a simple value of 1. I added this column to the lowest grain column grouping in the matrix (the lowest one in the hierarchy). I then added another column in the matrix and performed a RunningValue on the column with my calculated column from the dataset. RunningValue can take a Scope parameter and the scope in this case is the name of the lowest level Grouping in the hierarchy. This gives me a value equal to the number of columns in the matrix.

There may be better way to do this and I will look into it for you but this was the first idea that popped in my head.

Answer 3

A work around this (large number of columns) is to "wrap" the matrix after so many columns. You can do this by putting the matrix within a table or another matrix with the outer table/matrix controlling the wrapping by checking a "pointer" or a grouping number in the datasource. For this to work, you will also need to increment this pointer that controls the grouping every n number of rows where n is the maximum number of columns you want to appear in the matrix report.

Example of the datasource that is transformed into the matrix: [Group Pointer], [Matrix Columnn Dimension], [Matrix Row Dimension], [Measure/Data]

Everytime the [Group Pointer] breaks every, say 20 [Matrix Column Dimensions], the outer table/matrix will wrap the inner matrix.