Combining Groups into single Prompt

[Good Friend]

Hi Friends,

I have a requirement where I need to combine the groups coming from the same table and show it in a single prompt.
We have multiple groups with different data which are coming from the same table and I have mentioned only Group1 to Group4 in the attachment for the sake of conversation.
In the framework I have created a single prompt with multiple OR Conditions for each group, so no matter what group user selects and it will fall into any of those groups and filter out the data for that columns and also users can drag and drop that prompt in the report when needed. When it comes to prompt display, All the groups are not coming into single prompt when I test the report because the Problem here is prompt is picking only Group 1 values which I mentioned as Actual Group Prompt where I need to show all the Group values combined into single prompt(Kind of Union) and shown as Expected Group Prompt.. Any insight on how to proceed with this. Can this be done at framework level so report users can use this prompt for adhoc purposes or it can be done only at the report studio level.Please let me know if it doesn't make sense. Thanks.

[CognosPaul]

If I understand, you have four levels of a hierarchy that you want to prompt for. You need to make a filter in Framework that will generate a prompt, with all 4 levels unioned together, when used in an ad-hoc report.

Do you have separate name and key fields for these groups?

[Good Friend]

Thanks for the reply Paul.You are right with the first line. I'm not sure whether I can say these as hierarchies but this is a relational model and there are like around 10 groups Group 1...Group 10 with 10 different columns and coming from the same table.Lets say if each group has 4 rows of data, then the 10 groups combined and showed in a single prompt will be 4*10 = 40 rows. Then the prompt display should show all the 40 rows, so what ever they choose in the prompt display, those respective values will be passed into their individual group filter with OR Condition in the Framework prompt. I'm not sure about separate name and key fields ? Are you referring to business key and member captions or parameter maps in this context.

[CognosPaul]

Yes, I meant business key and caption. Do any of these groups share the same ID?

Easiest solution will be to create a union query in FM (I think it's something like a query set). In this case it might be worth just doing a hand-written SQL to union the tables - but this may cause issues if you're using certain postgres databases. Let's call this new model query - unionedGroups.

In your filter, you would do something like:

Code Select Expand

[namespace].[tableName].[Group 1] in (#prompt('Group','string','','','[namespace.[unionedGroups].[Groups]'#)
or [namespace].[tableName].[Group 2] in (#prompt('Group','string','','','[namespace.[unionedGroups].[Groups]'#)
or [namespace].[tableName].[Group 3] in (#prompt('Group','string','','','[namespace.[unionedGroups].[Groups]'#)
or [namespace].[tableName].[Group 4] in (#prompt('Group','string','','','[namespace.[unionedGroups].[Groups]'#)