Dynamic Grouping

[PlayCognos]

Is there a way in Report Studio to dynamically group a list report based on the prompt parameters selected? I have four different prompt parameters all of which are optional. Depending on the parameters choosen, the report needs to display the list grouped on the parameters. I have thought of conditional blocks but that option becomes difficult to manage with the different permutations and combinations possible from the prompt parameters (16 in this case).

Please let me know if you would like me to present an example to understand this better.

Any help will be greatly appreciated.
PC.

[Lynn]

You could create a data item that is populated dependent upon the parameter selections and then group on that derived query item in the layout.

For example, say you have a prompt option to group on either city or state. You would create a data item called something like "Grouped Column" that has an expression like:

case ?groupingPrompt?
  when 'City' then [City]
  when 'State' then [State]
end

Put the "Grouped Column" query item in your layout and group on that. This is a very simple example but perhaps the concept can be expaned upon for your situation.

[PlayCognos]

Lynn,
Thank you for your idea. I will try that now. I do have another question related to this. The scenario I am trying to handle could require grouping anywhere from 1 to 4 fields depending on how many prompt parameters have been choosen. Would you have some suggestion how to handle that? I am visualizing having 16 'case' conditions based on the example you have given. Am I thinking in the right direction?

Thank you.
PC.

[Lynn]

I was wondering if you might have variable columns involved. Consider the following - with a grain of salt since I can't see your data and don't know the parameter values :)

Create 4 query items in your query representing the four fields potentially that appear in the grouping, such as Group1, Group2, Group3, Group4. In situations where not all four are needed based on parameter selection, the un-needed query items case expression would resolve to something bogus like 'No Grouping' or null string. Then use a boolean render variable RenderGroup2, RenderGroup3, RenderGroup4 (presumably you'd always have at least 1 so no need for render on the first grouping). This would conditionally render columns 2 through 4 so you'd see the derived elements that make sense and not show the dummy values in the un-needed columns.

As an example, assume the user has an option to group on geography (Country, Region, State/Province, City - 4 fields ) or on time (Year, month, day - three fields). In your query you'd populate the four derived columns with the appropriate logic. You'd see actual data in all four columns when grouping on geography and all four columns would render in the layout because the render variables evaluate to true. When grouping on time, the fourth field would contain a dummy value and would not render.

Note that data types need to be consistent - a data item can't resolve to a string in one case and a date in another. You may need to cast to strings or something if this is the case for you. Also, there are always lots of ways to solve the same problem, so use the ideas here as a sparking point to see what works for you.

Good luck.