Split Tablix into Columns with data flowing Down, not Across [Solution]

Question

I figured out how to break a long tablix that uses row groups into multiple columns cleanly, with values descending through columns instead of progressing across rows. I have never come across this solution before, though I am sure many people smarter than me have already figured it out. I hope this helps someone out.

Please forgive the law enforcement context of the data elements. I don't really want to redo all this in AdventureWorks just to illustrate.

Scenario: When summarizing calls for a station, the request was to list counts of calls by the call types... one to many. A sample of the data looks like this: Call Type Count 10851 - GTA JUST OCC 13 10851R - GTA REPORT 3 10852 - VEH TAMPERING RP 1 1124 - ABANDON VEH 1 1125 - TRAFFIC HAZARD 2 1126 - STRAND MOTORIST 1 11351 - POSS HEROIN SALE 1 11364 - PARAPHERNALIA 4 11377A - POSS CNTL SUBST 2 11378 - POSS FOR SALES 1 118 - PERJURY REPORT 1

The challenge is to break the long list into three columns based on the number of items grouped on (distinct number of the row group set) rather than based on the number of rows, which actually reflects the detail counts instead of rows displayed.

This example will split a tablix into three columns, but you can adjust the formulas for more or less.

Step 1: Create your tablix and populate it with a detail (count) field and a row group. Keep it narrow so you have room to the right of it for two more copies.

Step 2: Insert a Rectangle the width of your page and drag your new tablix into the rectangle at the far left. The rectangle will keep your three tablix aligned once they are displayed in the finished report.

Step 3: Select and copy your tablix, then paste it twice into the rectangle, keeping the top edges of each tablix aligned with eachother.

Step 4: On your original tablix, in an unused cell in a header row (not the detail row), enter the following formula as an expression: =RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) Change the szCallType to the name of the field you are grouping on. This will give you the number of rows that will be in the tablix. While you are here, note the Name of the tablix cell you are in... i.e. "Textbox45" in my formulas. This value is the base of everything else we do. This formula should be in ONLY the FIRST tablix created.

Step 5: Set the Row Visibility on the first tablix. This is NOT done in the Properties pane! Right-click on the row header for the detail row, and click Row Properties. Set the Visibility option to "Show or hide based on an expression", then enter the following expression, updating the szCallType to the field you are grouping on and updating the Textbox45 to the text box that you put your formula into in step 4. =iif(RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) < (ReportItems!Textbox45.Value / 3),False,True) Click OK a couple times to confirm and exit the dialog boxes.

Step 6: Now we set the row visibility on the detail rows of the second and third tablix the same way, but with different formulas.

Detail Row Visibility for Second Tablix. Update the field and tablix cell references just like you did in Step 5. =iif(RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) > (ReportItems!Textbox45.Value / 3) andalso RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) <= (ReportItems!Textbox45.Value / 3)*2 ,False,True)

Detail Row Visibility for Second Tablix. Update the field and tablix cell references just like you did in Step 5. =iif(RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) > (ReportItems!Textbox45.Value / 3)*2 andalso RunningValue(Fields!szCallType.Value, CountDistinct, Nothing) <= ReportItems!Textbox45.Value ,False,True)

Step 7: Hide the count you created in step 4. No point in having some stray number floating out there that will only cause questions. Just set the text color of the expression you created to match the background color of the cell.

That's it. For me, the final product looks something like this, though this is only the top portion of the report:final result

I hope this is helpful to someone out there. If anyone spots ways to improve on this, please comment.

I previously tried solutions that rely on counting rows. That method not only falls apart when using row groups, but it also flows the data left to right rather than top to bottom.