'Report Studio 10.2.2: Showing distinct values in value prompt

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

cognoscognos-10report-studio


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dougp

Related Questions