Filter SSRS Tablix into 2 columns that updates dynamically for menu page links

0
votes

I have this SSRS report which I'm using as a menu page, with an action assigned to each report name to take the user to it. But as you can see with more and more reports being added, it's slipping over the page.

enter image description here

I want to create a menu page which has the report names in two columns and don't know how to go about this.

In design view I have one tablix which currently looks like this

enter image description here

With this code in the dataset:

SELECT [ReportOrder],[ReportID],[ReportPath],[Folder],[ReportName],[ItemType]FROM [dbo].[DimSSRSReportList] WHERE Folder = 'Customer Services' AND ItemType = 'Report'

My initial thought was to have two Tablix side by side and filter the left to top 50% and right to bottom 50% but this is just repeating the same reports on either side

enter image description here Can anyone help?

Thank you muchly

--------EDIT!--------

I can't quite get the column grouping using the MOD function to work. I've added the expression to the top and it's returning everything as 1. This is how it currently looks

enter image description here

2
reporting-servicesssrs-tablixssrs-2016
Does your top 50% work in the first tablix? I'm thinking after you get both the tablix to work, you can put them in a rectangle and that should be contained on one page as long as your top half does not go over. – papermoon88
No the 50% doesn't work on the first tablix. It just shows everything. – Pinkybloo

2 Answers

0
votes

This is easier if you can add a Row Number to your data. 

SELECT ReportOrder, ReportID, ReportPath, Folder, ReportName, ItemType, 
    ROW_NUMBER()OVER(PARTITION BY ReportID ORDER BY ReportOrder) AS ROW_NUM
FROM [dbo].[DimSSRSReportList] 
WHERE Folder = 'Customer Services' AND ItemType = 'Report'

Then use a Tablix to show your data, with Column Grouping of 

=Fields!ROW_NUM.Value MOD 2

and Row Grouping of 

=INT((Fields!ROW_NUM.Value - 1) / 2)

The MOD function will return 1 or 2 for the column that the report name will appear while the Row Group will divide the data into groups of 2.

0
votes

This is an extension to Hannover's answer but it should make things easier as there are no calculations in the report design.

Change the dataset query to the following...

SELECT ReportOrder, ReportID, ReportPath, Folder, ReportName, ItemType
    , CAST(((ROW_NUMBER() OVER(ORDER BY ReportOrder)-1)  / 2) as INT) as RowN
    , ((ROW_NUMBER() OVER(ORDER BY ReportOrder)-1) % 2)  as ColN
FROM [dbo].[DimSSRSReportList] 
WHERE Folder = 'Customer Services' AND ItemType = 'Report'

If you run this query in SSMS you'll see you get data similar to this simplified exmaple

ReportOrder, ReportName, RowN, ColN
1            FirstReport    0     0
2            SecondReport   0     1
3            ThirdReport    1     0
4            FourthReport   1     1
5            FifthReport    2     0

Next Add a matrix, and set row group to group on RowN and the column group to group on ColN

That should be it.