Friday, March 19, 2010 7:44 AM
Although you can set page break for column group in Reporting Services 2008, page breaks are ignored on column groups. Reference:
Here are some workarounds, available forboth Reporting Services 2005 and2008:
Spread the columns from one matrix into several matrixes. You can first copy one matrix and then paste it into several ones you want. Then set the filter for each column group to make sure that the total columns’ length in one matrix just fit a page’s width.
The other method is to use a custom code.
a. Please copy the following code to the custom code area:
Dim FlagTable As System.Collections.Hashtable
Dim Flag AS Integer
Function MyFunc(ByVal NewValue As Object) As Integer
If (FlagTable Is Nothing) Then
FlagTable = New System.Collections.Hashtable
If (NewValue Is Nothing) Then
NewValue = "-"
If (Not FlagTable .Contains(NewValue )) Then
Flag =Flag + 1
MyFunc = Flag
b. Create a list in your report.
Imagine thatthe column group of a matrix is grouped bythe field ‘Column_Group’, then set the detail group of list withthe expression like this:
Note: This means the Max number of column in matrix will be five after you follow step C.
c. Sort the dataset by column group field, and then drag the matrix into the list. Click Preview.
Similar to the second method, you need to modify the dataset.
a. Create an ID column for the column group in your dataset.
For example,there isa datasetwith the following query:
SELECT * FROM Table
The column group is grouped on the field “Group1”.Then, modify the query like this:
SELECT *, Dense_Rank()OVER(order by Group1) AS ID FROM Table
b. Create a list in your report, set the detail group of the list with the Expression like this:
Note: This meansthat the Max number of column in matrix will be five after you followthe step C.
c. Sort the dataset bythe column group and then drag the matrix into the list. Click Preview.
- Edited by Raymond-LeeMicrosoft Employee, Moderator Friday, April 16, 2010 5:02 AM Regarding to stepc of workaround2, for different version of SSRS, you can refer to the below post.
Friday, March 19, 2010 8:02 PM
I am working on an application where this is exactly what I need. I am trying to use option #2.
When the report runs the matrix has 60 row of 26 columns each. I would like to break at column 5-6 so I can use portrate mode instead of landscape.
1. I copied your code exactly as shown and pasted to code area.
2. I inserted a list. Clicked on "Edit Detail Group". In the "Group On:" Expression area I keyed your code using my field name =Ceiling(Code.MyFunc(Fields!CDATE.Value)/6).
I do not get what I was hoping for. It just seems to put the data everywhere.
3. I could not just drag the matrix to the list I had to cut and paste. If you drag it just seems to treat as 2 separate entities instead of 1 entity.
Sunday, March 21, 2010 4:19 AMModerator
In SSRS 2005, you need to sort the dataset by column group field. In SSRS 2008, edit the details group of the list, and then sort the data with column group field.
Hope this helps,
Wednesday, December 15, 2010 2:20 AM
I think another simple workaround is to put the Matrix into a table with one group, and only one column. The cell needs to be the same dimensions as the Matrix, and allowed to grow (in width).
The table group is grouped on the same value as the Column group you want to page break, and set to page break after, and the body dimensions are set to fit within the page margins.
I'm not sure if it is considered bad form, but I generally find adding tables within tables, or matrices within tables, to be a very handy way to group data and especially include page breaks.
Tuesday, February 15, 2011 5:09 AM
I am new to SSRS, Can you help me how we can make the Max Number of columns to be displayed as Dynamic? As my columns are coming as an output in my Query.
Friday, February 22, 2013 6:50 PM
I realize this was posted quite some time ago, but I recently used this method (method 2 above), to fix a pagination issue I was facing. However, it raised another question. I need to produce this same functionality, but I need it to count columns that are hidden (as duplicates) in the processing. Currently this method is ignoring hidden columns, and therefore the pagination is not functioninig as required. Does anyone know how to fix this issue?
Thursday, March 21, 2013 6:47 PMYou saved the day... Well not the whole day, but definitely a few hours. Thanks!