Forum FAQ: How do I achieve column break in a matrix?
SymptomAlthough you can set page break for column group in Reporting Services 2008, page breaks are ignored on column groups. Reference:http://msdn.microsoft.com/en-us/library/ms156434.aspxSolutionHere are some workarounds, available forboth Reporting Services 2005 and2008:Workaround 1Spread 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.Workaround 2The other method is to use a custom code.a. Please copy the following code to the custom code area:Dim FlagTable As System.Collections.HashtableDim Flag AS Integer Function MyFunc(ByVal NewValue As Object) As IntegerIf (FlagTable Is Nothing) ThenFlagTable = New System.Collections.HashtableEnd IfIf (NewValue Is Nothing) ThenNewValue = "-"End IfIf (Not FlagTable .Contains(NewValue )) ThenFlag =Flag + 1FlagTable.Add(NewValue, nothing)End IfMyFunc = FlagEnd Functionb. 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:=Ceiling(Code.MyFunc(Fields!Column_Group.Value)/5)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.Workaround 3Similar 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 TableThe 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:=Ceiling(Fields!ID.Value/5)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.
March 19th, 2010 10:44am
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.
Free Windows Admin Tool Kit Click here and download it now
March 19th, 2010 11:02pm
Hi,
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,
Raymond
March 21st, 2010 7:19am
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.
Free Windows Admin Tool Kit Click here and download it now
December 14th, 2010 9:35pm