Dynamic Grouping with multiple columns

[dmk.3678]

Hi,

I have List report with 10 columns. These columns are Shown/Hide based on the prompt selection and Conditional Variables. So on prompt if I select on 5 column to show, the report output will show on those 5 columns.

Now I want to implement the Dynamic Grouping only on selected columns from prompt. (I now I can add all columns in Grouping & Sorting of List Report Property, and all columns will grouped, and based on Prompt column selection it will show only selected columns.) but I don't want to do that. I trying to figure out that if I select column A, B, C and D on the prompt page. The report will only group it by these 4 columns. If I select only A and B on prompt column selection it should automatically group the report by only A and B.

Please help.
Thanks.

[BigChris]

I haven't tried this, but something like it ought to work...you'll need to do some testing and checking though.

I'm assuming you're using a relational model for this.

Let's say you've got 10 fields called Column1, Column2 etc. and prompts to select them called Prompt1, Prompt2 etc. In your query, could you create 10 new fields which you're going to use for your grouping

Code Select Expand

GroupCol1: 
case ?Prompt1?
when 'Column1' then [YourData].[Column1]
when 'Column2' then [YourData].[Column2]
...
else NULL
end
You'd then do the same up to GroupCol10. Then I think you should be able to group your output by GroupCol1..10 Obviously try it with a couple of columns and a couple of prompts before you do all ten to check the process.

And let me know if it works...I might need to do something like in the future

[dmk.3678]

Chris,

I Understand what you trying to say, but I just gave an example of 10 columns, but actually in my report I have more than 100. And there is one checkbox group prompt to allow users to select which columns they want on output. Now as per your description I would have to endup creating more than 100 new DataItem like GroupClmn1.....GroupClmn100. and then I will have to add these GroupClmn dataitems in Grouping & Sorting property of List Report.

But I was trying something like creating only 1 DataItem say 'GroupColumns' and write some logic in expression which only group the selected column.

Please let me know ur thoughts.

[Lynn]

You should investigate prompt macros with type "token". The article link describes dynamic sorting which is similar to what you are trying to achieve. The bigger challenge will be figuring out how many grouping levels to support.

#prompt('p_SortColumn','token','[Sales (query)].[Order].[Order number]')#

http://www-01.ibm.com/support/docview.wss?uid=swg21397882

[dmk.3678]

You should investigate prompt macros with type "token". The article link describes dynamic sorting which is similar to what you are trying to achieve. The bigger challenge will be figuring out how many grouping levels to support.

#prompt('p_SortColumn','token','[Sales (query)].[Order].[Order number]')#

http://www-01.ibm.com/support/docview.wss?uid=swg21397882

This is also the way, but again I will endup creating 100 New DataItems and Adding them in to List Grouping & Sorting.
I was Looking forsomething like 1 DataItem in which I can include expression for all selected columns from prompt to group.
Or Is it not possible.?

Please Suggest.