Cognos : create custom groups in report studio

Question

I am new to Cognos and I know SQL but it seems I can’t figure out cognos logic for some basic stuff. It's been two days I am trying and I have been looking all over the internet without finding anything. Here’s the problem. I have a Dimension Product that has two dimensions under it: type of product and article (in this order, article is below type of product in terms of hierarchy). Let’s simplify and say I have this table:

Product line | Article   | Sales
-------------------------------
 Shoes       | Article1  | 1000
             | Article2  | 2000
             | Article3  | 10
             | Article4  | 20
             | Article5  | 30
 Bags        | Article6  | 100              
             | Article7  | 100
             | Article8  | 30
 Balls       | Article9  | 50
             | Article10 | 50

I want to display the sales per product line and per article for article1 and article2 and the sales per product line only for the rest. I want my final result to look like this:

Product line | Article   | Sales
-------------------------------
 Shoes       | Article1  | 1000
             | Article2  | 2000
             | Other     | 60
 Bags        | Other     | 330              
 Balls       | Other     | 100

I created an elementary data with the following expression “if [article-name] in (‘article1’,’article2’) then ([article-name]) else (‘other’) but it gives me this:

Product line | ArticleNEW| Sales
-------------------------------
 Shoes       | Article1  | 1000
             | Article2  | 2000
             | Other     | 10
             | Other     | 20
             | Other     | 30
 Bags        | Other     | 100              
             | Other     | 100
             | Other     | 30
 Balls       | Other     | 50
             | Other     | 50

I thought Cognos would group by automatically but it seems it does not when you create a new expression…. Please note that I have thousands of articles and I cannot create a data that would say “article3+article4+article5 etc.”. If anyone has an idea on this, it would be great! Thank you in advance!

Answer 1

I believe the issue is with the model. If you have access to Framework Manager and the project/metadata, this would change my answer

Try this method: 3 queries

1) Query 1 just have product line and article

2) Query 2 product line, article, sales

3) Next go to queries, then tool box, find the join.

Drag that over. There will be spots to add query 1 and query 2

In the middle is how you define the join

Connect the product line and article (there should be a button to add links so you should have 2 lines). This will be 1 to many (1.1 to 1.n). The first part represents the type of join, 1 being inner, 0 being outer. The second part is the relationship (either 1 or n for many).

We can group by query 1 and aggregate query 2 the way we want

Double click on query 3 and drag the data items (from query 1 and query 2)

Grab sales from query 2, and everything else from query 1

Now you should be able to set the aggregate property for Sales (either total or sum)