Reporting Services - determining the number of columns displayed in a matrix

Question

Is it possible to determine the number of columns displayed in a Reporting Services report and use this in the Visibility expression of a single text field?

I come across the following scenario:

My client has a Reporting Services 2005 report that contains a matrix that displays a variable number of columns depending on the selected options. Sometimes some users generate reports with a very large number of columns. This causes a server error when they subsequently try to export to Excel, as the Excel 97-2003 format is limited to 256 columns.

Therefore, I would like to display a message at the top of the reports when more than 256 columns are shown in the matrix, informing users about this restriction and offering a workaround for exporting to CSV (and viewing in Excel 2007, if any).

Answer 1

Can you do the count in a dataset to get the number of columns?

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

You don’t know how your data and matrix are configured, I do something similar when I need to know the number of columns so that I can draw the corresponding border. But I think that you should be able to count either a data set or a group in order to get what you are looking for. (I can insert the code, if necessary, Monday will come).

Answer 2

This can be done, I just created a test to prove my theory; here is the basic information.

I created a calculated column in my dataset with a simple value of 1. I added this column to the grouping with the smallest grainy column in the matrix (the lowest in the hierarchy). Then I added another column to the matrix and ran RunningValue in the column with my calculated column from the dataset. RunningValue can accept the Scope parameter, and the area in this case is the name of the group with the lowest level in the hierarchy. This gives me a value equal to the number of columns in the matrix.

There may be a better way to do this, and I will consider it for you, but it was the first idea that appeared in my head.

Answer 3

Working around this (a large number of columns) is to “wrap” the matrix after so many columns. You can do this by placing the matrix in a table or other matrix with an external table / matrix controlling the wrapper, by checking the "pointer" or grouping number in the data source. To do this, you also need to increase this pointer, which controls the grouping of each n number of rows, where n is the maximum number of columns that you want to display in the matrix report.

An example of a data source that is converted to a matrix: [Group pointer], [Matrix column size], [Matrix row size], [Measure / Data]

Each time the [Group Pointer] pointer breaks down, say, 20 [Matrix column sizes], the outer table / matrix will wrap the inner matrix.