Search code examples
business-intelligencecognos

COGNOS REPORT STUDIO grouping

I am new to cognos.

I have created a list report with the following columns...

ID,Forename,Surname,ProductCode,ProductTitle

I have two value prompts (drop down boxes) for the user to pick two product titles.

I want my list report to only display when a Customer has purchased both products. At the moment I have all Product1 all Product2 and all where Product1 and Product2 are relating to one customer.

I have then grouped by ID,Forename,Surname.

I have tried doing counts and running-counts but can't seem to come up with a solution.

Any advise would be much appreciated.

Solution

  • Filters:

    [ProductCode] = ?product1? || [ProductCode] = ?product2?
count(distinct [ProductCode] for [ID]) = 2

    The first filter limits the rows to just ones where the product matches either prompt. The second counts up the distinct product codes for each customer and excludes customers that don't have two.