SSRS Drill down report: how to display only first n rows and hide the rest to be toggled?

Question

Firstly, thanks in advance.

I am trying to make a drill down style report in ssrs and was wondering if it was possible to display only the first n rows and hide the remaining rows in a table to be toggled open when needed.

For example, for the following table with primary key id, if i were to group results based on isopen? boolean, i should get firstly, all the stores that are open, and then all the stores that are closed.

I want only the first n open stores to be displayed and the rest to be hidden to be toggled open if needed. Same for the closed stores.

store,  isopen, name,  id

first,   no,    NULL,   1    
second,  yes,   Julie,  2
second,  yes,   Mary,   3
first,   yes,   Katie,  4
third,   no,    NULL,   5
fourth,  yes,   Mark,   6
first,   yes,   Tony,   7
third,   no,    NULL,   8

My first thought was to try the "show or hide based on an expression" option under visibility in the row group's properties (results grouped by isopen). I tried using rownumber() in the expression to determine if rownumber is less than n, then display, else hide. It worked, it only showed the first n rows, but when i toggled open the rest, the first n were hidden. I want all the rows to be displayed when I toggle open the remaining rows.

Thanks very much in advance for any help.

Can you share the expression you used for the Hidden property? It likely needs to be tweaked, but without knowing what expression you used, it's difficult to suggest how to resolve it. — Stacia
where "claim" is the row group in the table used to filter the rows based on whether a store is open or closed. — s1lntz
Now that I see that you're using a parameter to determine the number of rows to show or not, I don't think you can do it this way. There might be a way to do this if you could use a data-driven approach but if you're allowing the user to determine N, you can't control the data the same way. More specifically, when you use the Hidden property along with a toggle, the toggle just switches the current state of the object (textbox, row, etc.) There is no way to conditionally toggle - if something was hidden, it will be visible after the toggle and if it was visible, it will be hidden. — Stacia

Davos Davos · Accepted Answer · 2012-10-11T02:45:51

Create four groupings, not nested; i.e. the brackets that indicate the grouping of the rows when you click in to the tablix are not intersecting in any way:

( Group 1

( Group 2

( Group 3

( Group 4

In the first group, you filter it to leave only the first N open stores.

In the second group, you filter out the first N open stores, and you set it to be hidden, toggled by a text box in the first group - of your choosing.

In the third group, you filter it to the first N closed stores.

In the fourth group, you filter out the first N closed stores, and you set it to be hidden, toggled by a text box in the third group.

You could drive the filtering of all four groups with your parameter, and all four groups are in the same tablix so it has the appearance of one table.

SSRS Drill down report: how to display only first n rows and hide the rest to be toggled?

1 Answers