Blogs Home
Tuesday, June 29, 2021 11:00 AM

SSRS: Limit the number of columns on a matrix

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

RWS_Blog_SSRSFor today's blog post, we want to help solve this common problem: how to limit the number of columns in a matrix to prevent it from exceeding the page width, when there may be any number of columns.

The following information is based on Limit no of columns in SSRS matrix report By Challen Fu, Microsoft.


Our examples are drawn from a report which provides the locations in MEDITECH (6.x) where MIS documentation sections are used.

Each location is represented by the AttachedToPath column in the dsMain dataset.

At the end of the report a matrix is built showing the number of times an individual MisDocSectID is used in each AttachedToPath, but we don’t want that to blow out the page. Thus, we need to limit the display to the TOP X counts.


RWS image #1

RWS image #2 6-14-2021

RWS image #3 6-14-2021

Our matrix contains a row group, grpAttachedToPath (value = Fields!AttachedToPath) and a column group, grpMisDocSectID (value = Fields!MisDocSectID).

The summary expression = CountDistinct(Fields!AttachedToPath.Value + "^" + Fields!AttachedToID.Value)


Step 1: Right click on the report, Report Properties, Code

Add the following code:

Dim FlagTable As System.Collections.Hashtable

Dim Flag AS Integer

Function MaxCol(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

MaxCol= Flag

End Function

Step 2: Add a list to the report

Right Click the Details group, Group On, Fx button, add:



Fields!MisDoctID.Value = will be the column group of the matrix 9 = maximum number of columns you want to display on the matrix.

Right-click on the list, Properties, Sort

Sort by the column of the matrix:
RWS image #4 6-14-2021

Step 3: Drag the matrix onto the list

RWS image #5 6-14-2021

Sample output:

RWS image #6 6-14-2021

These examples are part of a much bigger set of reports we could deploy for you which let you find where document sections, queries, interventions, etc. are attached in your MEDITECH configuration.

Want more DR-related tips, knowledge-base articles, how-to videos, standard reports and a whole constellation of pre-built functions, module-specific views, stored procedures and additional capability for your DR? Consider the DR Resource Center!


As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email to discuss how we can help support your team!