Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
For 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.
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
If (NewValue Is Nothing) Then
NewValue = "-"
If (Not FlagTable .Contains(NewValue )) Then
Flag =Flag + 1
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:
Step 3: Drag the matrix onto the list
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 firstname.lastname@example.org to discuss how we can help support your team!