Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Forum FAQ: How do I achieve column break in a matrix?

Genel Tartışma Forum FAQ: How do I achieve column break in a matrix?

  • Friday, March 19, 2010 7:44 AM
     
     
    Sign In to Vote
    2

    Symptom

    Although 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.aspx

    Solution

    Here are some workarounds, available forboth Reporting Services 2005 and2008:

    Workaround 1

    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.

    Workaround 2

    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

    End If

    If (NewValue Is Nothing) Then

    NewValue = "-"

    End If

    If (Not FlagTable .Contains(NewValue )) Then

    Flag =Flag + 1

    FlagTable.Add(NewValue, nothing)

    End If

    MyFunc = Flag

    End Function

    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:

    =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 3

    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:

    =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.

     

All Replies

  • Friday, March 19, 2010 8:02 PM
     
     
    Sign In to Vote
    0

    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 AM
    Moderator
     
     
    Sign In to Vote
    0

    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

     

     
  • Wednesday, December 15, 2010 2:20 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    Hi,

    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.

    Thanks,

    Venkata

     
  • Friday, February 22, 2013 6:50 PM
     
     
    Sign In to Vote
    0

    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?

    Thank You!

     
  • Thursday, March 21, 2013 6:47 PM
     
     
    Sign In to Vote
    0
    You saved the day... Well not the whole day, but definitely a few hours.   Thanks!