Search code examples
reporting-services

How to connect a parameter with a toggle tablix grouping?

I have a grouped table with collapsed rows. Users can use a parameter field to enter some keywords to get the right rows in the table.

After the user enter a keyword they get see the right rows but the table is collapsed. I want:

" If someone use the search parameter then open all collapsed groups else let them collapsed "

table

Solution

  • You can do this quite easily.

    On the row group that has the "Account Description" column (this might be the 'details' row group), set the Hidden property to an expression such as

    =LEN(Parameters!myParam.Value) = 0

    This simply checks if the myParam parameter has anything in it, I've assumed that the parameter allows blanks, but hopefully you get the idea.

    When the report is first run and the parameter is blank, all rows at this level are collapsed. If an entry is made in the parameter, when the user runs the report, all rows at this level are expanded.

    If you want only a selected node to be shown If you want everything collapsed and only expand the node that matches the paramater value then you can do that too.

    In my example I had some simple client by city data group by cities.

    Setting the Hidden expression to this..

    =LEN(Parameters!myParam.Value)= 0 OR Fields!city.Value <> Parameters!myParam.Value

    Gave the following results

    enter image description here

    and when the parameter is set to "C"

    enter image description here

    (Ignore that fact that city A appears twice, I used an old report that I'd used in an old SO answer. It's not a bug and has nothing to do with this answer)