0

Good afternoon,

I'm busy making reports in Cognos Report Studio 10.2.2 and as part of this, I need to group a number of accounts based on a group designation, so I can report on all records where the accounts from these groups are mentioned.

I've made a prompt page with a Value Prompt:

Query: Prompt_Group
Use Value: ID
Display Value: Group
Parameter: SelectedGroup

The query Prompt_Group contains:

Data Item: ID
Data Item: Group
Detail Filters: Group <> ''

To finish this, there's a query with the data to display, containing:

Detail Filters: AccountID IN ?SelectedGroup?

Now, I want to aggregate the Prompt_Group by Group, which is a text field, but I've ran into the following problems:

In the current set up, there's multiple occurrences of the same group, it'll show something like this:

GROUP: A
GROUP: A
GROUP: B
GROUP: B
GROUP: C
GROUP: C
GROUP: C
GROUP: D

If I change Use Value of the Value Prompt to Group, it shows the groups correctly (as distinct values), but my Detail Filter in the display query stops working, because it doesn't receive IDs.

I've tried all the Aggregate Functions on the Data Item: Group in the Prompt_Group query, but it will either show as the earlier list, or show as a list of 1's.

I'm at a loss here, how do I aggregate the ID by Group so that my prompt will only show the distinct groups, but receives -all- related IDs to the Group when I select it in the prompt?

Many thanks in advance!

Kind regards, Martin

Fengshen
  • 1
  • 2
  • You are using a display value of Group and a use value of ID. Given the groups are repeating, you probably have multiple unique IDs for each group. If that is the case, then what ID would you want to pass if someone selects Group A? – Daniel Wagemann Jun 09 '20 at 17:07
  • Hi Daniel, apologies for my slow reaction! If someone selects Group A, I want the variable to contain -all- ID's where Group is A, so, for example: {1, 2}, so I can check for AccountID IN this array. – Fengshen Jun 15 '20 at 10:31
  • You'll need to do this in the receiving query, so use and display Group, then on receiving query filter based on group to get the desired accountIDs. – Daniel Wagemann Jun 15 '20 at 13:09

1 Answers1

0

We'll call your output query Account and assume it has data items named AccountID and AccountGroup...

Set the Use Value to Group and update your detail filter to:

AccountGroup IN ?SelectedGroup?

If a grouping value (like AccountGroup) is not available in the query you are trying to filter:

  1. Add a query.
  2. Add a join to the new query.
  3. Join Account to Prompt_Group on Account.AccountID = Prompt_Group.ID
  4. Add all data items from Account to the new query.
  5. Add Group from Prompt_Group to the new query.
  6. Add a detail filter Group in ?SelectedGroup?
  7. Select your visualization (list, etc.) and change the query it uses to the new query.
dougp
  • 2,810
  • 1
  • 8
  • 31