Sunday, January 1, 2012

Cognos 10 - Advanced Report Studio - Joins, Unions, Intersect

Cognos 10 Training - Advanced RS - Creating Unions - Intersect - Part 7 of 25 Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Join Query
Application: Join on the report is used when, there is no join defined at the Framework Manager Model level.

Steps for Creating a Join at report Level:
1- Create two queries in report studio
2- Query-1  Drag City, Year, Sales Quantity and Sales Amount
3- Query-2 Drag Country, City     
4- Goto Query Explorer: Drag and Drop Join from the left hand side.                                    
5- Drag Query-1 on the first Box in the Join Query.
6- Drag Query-2 on the Second Box in the Join Query.
7- Specify the Join condition
8- double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
9- Goto Page explorer  & Drag and Drop a list.
10- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
11- From Data Items Tab: Drag the columns from Query-3 in the new list
12- Run the report
SO THATS IT.....

********************************************************************************************
Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Union Query
Steps for Creating a UNION at report Level:
1- Create two queries in report studio
2- Query-1  Drag Country, City, Sales Quantity and Sales Amount 
3- Apply filter for Country-USA,UK for Query-1
4- Query-2  Drag Country, City, Sales Quantity and Sales Amount
5- Apply filter for Country-IN,UK for Query-2
6- Please note that for UNION, the columns and data type across two queries should be same.
7- Goto Query Explorer: Drag and Drop UNION from the left hand side.     
8- Drag Query-1 on the first Box in the UNION Query.
9- Drag Query-2 on the Second Box in the UNION Query.
10- Double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
11- Goto Page explorer  & Drag and Drop a list.
12- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
13- From Data Items Tab: Drag the columns from Query-3 in the new list
14- Run the report

Steps for Creating a intersect at report Level:
15- Goto Query Explorer: Drag and Drop INTERSECT from the left hand side.                                    16- Drag Query-1 on the first Box in the intersect Query.
17- Drag Query-2 on the Second Box in the intersect Query.
18- Double click the Query-4: Drag and Drop the columns as required from Q-1 and Q-2.
19- Goto Page explorer  & Drag and Drop a list.
20- Associate Query-4 to this list; Select the entire list and in the properties pane, Link Query-4
21- From Data Items Tab: Drag the columns from Query-4 in the new list
22- Run the report

SO THATS IT.....

No comments:

Post a Comment