Dynamic Grouping in SSRS Reports

  • Been using Dynamic grouping for quite awhile within SSRS now. I even apply this over multiple groups within the same report to give an even more valuable piece of work.

    It does away with creating the same report over and over again with different groups so it's a winner for me!

  • Appriciate your work Andy.

    This excellent Trick to perform dynamic grouping and sorting.

    Each single point has a very clear explanation and there is no mystery or left unexplained.

    It is very easy to understand and useful for novice or experienced SSRS Developer.

  • Great article.

  • This is what we call thinking out of the box. Good job AndyOwl

  • Very interesting article and indeed very clever. I will definitely use this sometime in the near future.

    Awesome work.

  • Nice article but I don't understand why you don't enter column names in the "value" column in figure 9.

    If you change:

    Label - Value

    Country - Country

    Director - Director

    Studio - Studio

    To:

    Label - Value

    Country - CountryName

    Director - DirectorName

    Studio - StudioName

    Then you have the following advantages:

    1) Flexibility

    You don't have a limitation to group by a column suffixed with "Name", or any other hardcoded value in plenty of formulas

    2) Expression complexity/length reduced

    You can remove in your expressions all the ' & "Name"'

    Example, your "complicated expression" changes from:

    =Parameters!GroupField.Label & ": " & Fields(Parameters!GroupField.Value & "Name").Value

    To

    =Parameters!GroupField.Label & ": " & Fields(Parameters!GroupField.Value).Value

  • Nice article, thanks.

  • Nice article, but could you provide the report file?

  • When I do dynamic grouping, I use a calculated field with a simple switch( Parameters!Parameter1.Value="Text1", Fields.Field1Value ... ) It's easy to read, I can add as many switches as I want to a single field/parameter combo, not restrained by naming convention of fields in dataset, so the switch can easily use fields that are named wildly differently, logic is stored in exactly one place, so the one field can be referenced by sorting, grouping, labeling, whatever. If I need to make a change or addition, I don't need to remember all the places the expression was duplicated. It's also then easy to pair up multiple functionality with more than one parameter/calc.field combo. So for example, I might have a "color by" combo, a "sort by" combo, and a "group by" combo. User makes their selections, calc fields respond, and visualization/table is generated accordingly. If I need to remap, or add/remove selections available in parameter, it's just the one place to go editing, no matter how complex the table/viz gets.

  • I am wondering how you got your table to look like it did in figure 5. Mine is missing the three lines in the line after the headers:

  • robins80 (7/31/2015)


    I am wondering how you got your table to look like it did in figure 5. Mine is missing the three lines in the line after the headers:

    Never mind, I figured out where I went wrong.

  • liver.larson (7/31/2015)


    When I do dynamic grouping, I use a calculated field with a simple switch( Parameters!Parameter1.Value="Text1", Fields.Field1Value ... ) It's easy to read, I can add as many switches as I want to a single field/parameter combo, not restrained by naming convention of fields in dataset, so the switch can easily use fields that are named wildly differently, logic is stored in exactly one place, so the one field can be referenced by sorting, grouping, labeling, whatever. If I need to make a change or addition, I don't need to remember all the places the expression was duplicated. It's also then easy to pair up multiple functionality with more than one parameter/calc.field combo. So for example, I might have a "color by" combo, a "sort by" combo, and a "group by" combo. User makes their selections, calc fields respond, and visualization/table is generated accordingly. If I need to remap, or add/remove selections available in parameter, it's just the one place to go editing, no matter how complex the table/viz gets.

    Nice!

  • I would like to see this file as well please.

  • I will get an error when adding Nothing to the parameters.

    When choosing dont group i get the error NothingName does not exists.

    What am i doing wrong?

    Also I dont understand how to get the grouping above the fields. when i add a grouping on a row, it always shows the grouping on the left and not above as shown in your example

Viewing 14 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply