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?
是否可以确定Reporting Services报表中显示的列数,并在单独文本框的“可见性”表达式中使用此列?
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.
我的客户端有一个Reporting Services 2005报告,其中包含一个矩阵,根据所选参数显示可变数量的列。有时,一些用户生成了包含大量列的报告。这会导致服务器在随后尝试导出到Excel时出错,因为Excel 97-2003格式限制为256列。
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).
因此,当矩阵中显示超过256列时,我希望在报告顶部显示一条消息,告知用户此限制并建议导出为CSV的解决方法(如果他们有,则在Excel 2007中查看)。
3 个解决方案
#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.)
不确定你的数据和矩阵是如何设置的,我做了类似的事情,我必须知道列的数量,以便我可以绘制一个合适的边界线。但我认为你应该能够依靠数据集或分组来获得你想要的东西。 (如果需要,我可以在星期一粘贴一些代码。)
#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.
我在数据集上创建了一个计算列,其值为1。我将此列添加到矩阵中的最低粒度列分组(层次结构中的最低值)。然后我在矩阵中添加了另一列,并使用数据集中的计算列在列上执行了RunningValue。 RunningValue可以采用Scope参数,在这种情况下,范围是层次结构中最低级别分组的名称。这给了我一个等于矩阵中列数的值。
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.
可能有更好的方法来做到这一点,我会为你调查,但这是我头脑中的第一个想法。
#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.
围绕这个(大量列)的工作是在这么多列之后“包裹”矩阵。您可以通过将矩阵放在一个表或另一个矩阵中,外部表/矩阵控制包装,方法是检查数据源中的“指针”或分组编号。为此,您还需要增加此指针,该指针控制每n行的分组,其中n是您希望在矩阵报告中显示的最大列数。
Example of the datasource that is transformed into the matrix: [Group Pointer], [Matrix Columnn Dimension], [Matrix Row Dimension], [Measure/Data]
转换为矩阵的数据源示例:[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.
每当[Group Pointer]中断每个,例如20 [Matrix Column Dimensions]时,外表/矩阵将包裹内部矩阵。
#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.)
不确定你的数据和矩阵是如何设置的,我做了类似的事情,我必须知道列的数量,以便我可以绘制一个合适的边界线。但我认为你应该能够依靠数据集或分组来获得你想要的东西。 (如果需要,我可以在星期一粘贴一些代码。)
#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.
我在数据集上创建了一个计算列,其值为1。我将此列添加到矩阵中的最低粒度列分组(层次结构中的最低值)。然后我在矩阵中添加了另一列,并使用数据集中的计算列在列上执行了RunningValue。 RunningValue可以采用Scope参数,在这种情况下,范围是层次结构中最低级别分组的名称。这给了我一个等于矩阵中列数的值。
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.
可能有更好的方法来做到这一点,我会为你调查,但这是我头脑中的第一个想法。
#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.
围绕这个(大量列)的工作是在这么多列之后“包裹”矩阵。您可以通过将矩阵放在一个表或另一个矩阵中,外部表/矩阵控制包装,方法是检查数据源中的“指针”或分组编号。为此,您还需要增加此指针,该指针控制每n行的分组,其中n是您希望在矩阵报告中显示的最大列数。
Example of the datasource that is transformed into the matrix: [Group Pointer], [Matrix Columnn Dimension], [Matrix Row Dimension], [Measure/Data]
转换为矩阵的数据源示例:[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.
每当[Group Pointer]中断每个,例如20 [Matrix Column Dimensions]时,外表/矩阵将包裹内部矩阵。