University of Delaware Advanced Topics
Cognos Training
Cognos Training – Level II - Advanced Topics
JOINS
In Report Studio, data can be incorporated from multiple queries to create more complex reports. You will use Query Explorer to build/manage the queries and join them to create the relationship between the queries. Keep these items in mind when using joins:
· Joins can only be used within the same package
· A query must be created for the report layout (results) as well as for the data queries
· The report can be started with a List, Crosstab, Chart or Blank report type
A. Simple example of a join, starting with a LIST
You will create a report using transaction data from two different fiscal years by creating two data queries and one report layout query.
1. Open Report Studio using UD Financial Data Mart (in FDM packages)
2. Click Create a new report or template
3. Double-click the List icon
4. Click Query Explorer on the Explorer Bar (click Yes on the pop-up)
5. Click Queries
6. From the Toolbox, drag & drop two additional Queries to the work area.
7. Rename the three Queries (this will make working with them easier)
a. Click on Query1
i. Go to Properties, scroll to Miscellaneous/Name (at the bottom)
ii. Change name to Report
b. Click on Query2
i. Change name to Trans FY 2008
c. Click on Query3
i. Change name to Trans FY 2009
8. From Insertable Objects, drag & droop a Join next to the Report query,
9. It will look like this:
10. Save your report and name it Join Trans 2FY Totals
11. Drag the Trans FY 2008 query to the top box
12. Drag the Trans FY 2009 query to the bottom box.
13. Double-click the Trans FY 2008 query, this will open the data window
14. Drag these 4 fields from UD Financial Data Mart/Insertable Objects
Field Name / Query Subject / FolderPURPOSE / Chart of Accounts / Purpose
ACCOUNT / Chart of Accounts / Account
TRANS_AMOUNT / Trans Detail
FISCAL_YEAR / Trans Detail / Dates
…to the Data Items pane:
15. Add these two Filters and validate each one:
a. [FISCAL_YEAR]=2008
b. [PURPOSE]='TREA112112' - use one of your own Purpose codes
16. Add these three pre-written Filters:
a. ACTUALS Ledger Filter
b. Journal ID not blank Filter
c. Statistics Code not ENP ENU Filter
17. Click the Run menu item and choose
18. View Tabular Data to verify data is good
19. Jot down the number of Accounts
20. Save your work
21. Open Query Explorer and click Trans FY 2009 query
22. Add the same 4 fields from UD Financial Data Mart to Data Items:
Field Name / Query Subject / FolderPURPOSE / Chart of Accounts / Purpose
ACCOUNT / Chart of Accounts / Account
TRANS_AMOUNT / Trans Detail
FISCAL_YEAR / Trans Detail / Dates
23. Add these two Filters and validate each one
a. [FISCAL_YEAR]=2009
b. [PURPOSE]='TREA112112' - use one of your own Purposes codes
24. Add these three pre-written Filters:
a. ACTUALS Ledger Filter
b. Journal ID not blank Filter
c. Statistics Code not ENP ENU Filter
25. Click the Run menu item and View Tabular Data to verify data is good
a. Again, jot down the number of Accounts
26. Save your work
27. Click Query Explorer, click Queries
28. Double-click the Join
29. In the Join Relationship window, click the New Link button
a. The join should be between Account and Account
b. Change the join by clicking on a field name on either side
30. Set the Cardinality to 1..1 = 1..1 and click OK
(There is one-to-one relationship between the two queries with regards to ACCOUNT and each ACCOUNT will be in each query only once.)
31. Save your work
32. While still in Query Explorer, double-click the Report query
a. You must first add fields to the Report query before the fields will be available for the report’s layout (the List in this case).
33. From Trans FY 2008, drag all the fields to the Data Items pane
34. And drag all the fields from Trans FY 2009
35. Click Page Explorer, and then Page 1
The List is empty; you must add the fields you want.
36. Click Data Items tab and double-click these 3 fields from the Report query:
Change the Trans Amount column headings to something more meaningful.
1. Click column heading called TRANS_AMOUNT
a. Go to Properties
b. Data Item/Label – change to FY08 Total
2. Click column heading called TRANS_AMOUNT1
a. Go to Properties
b. Data Item/Label – change to FY09 Total
Add totals to the renamed TRANS_AMOUNT columns
1. Click the FY08 Total column heading and click Aggregate and Total
2. Click the FY09 Total column heading and click Aggregate and Total
3. The List should look like this:
4. Save your work
5. Run the report
Fix the report to include the Accounts not included in BOTH queries!
1. Click Query Explorer, Queries and click Join
a. Change the Cardinality to 0..1 = 0..1
b. Click OK
2. Click Page Explorer and click Page 1
a. Click the tab
b. Click and drag ACCOUNT1 to the work area as shown below:
3. Save your work
4. Run the report (it could take up to 5-7 minutes)
Add a column to show the percentage difference between FY2008 and FY 2009, this is the math: (FY09 – FY08) / FY08 (Query Calculation)
1. Click the Toolbox tab
2. Click and drag a Query Calculation to right of the last column (look for the thin, blinking line)
a. Name it % Diff FY08 to FY09 and click OK
b. Add this expression using the fields in the Available Components
([Trans FY 2009].[TRANS_AMOUNT]-[Trans FY 008].[TRANS_AMOUNT])/[Trans FY 2008].[TRANS_AMOUNT]
c. Validate the expression
d. Click OK
3. Click the column body where it says <%Diff FY08 to FY09> and Properties
e. Click Data/Data Format – click the ellipses
f. Format type – select Percent
g. Properties/No. of Decimal Places – select 1
h. Click OK
4. Save your work
5. Run the report
Other Possible Enhancements to Join A (Join Trans 2FY Totals)
Add Account Description to the report
1. Query Explorer, click on Trans FY 2008 query
2. From Insertable Objects, open Chart of Accounts and Account
3. Drag ACCOUNT_DESCR to the Data Items pane (under FISCAL_YEAR)
4. Query Explorer, click on Trans FY 2009 query
5. From Insertable Objects (Account should already be open)
6. Drag ACCOUNT_DESCR to the Data Items pane (under FISCAL_YEAR)
7. Query Explorer, click on Report query
8. Drag ACCOUNT_DESCR (from FY08)to the Data Items (to bottom of list)
9. Drag ACCOUNT_DESCR (from FY09)to the Data Items (to bottom of list)
10. Page Explorer, click Page1
11. Click Data Items tab
12. Drag ACCOUNT_DESCR (from FY08) to work area between ACCOUNT and FY08 Total
13. Drag ACCOUNT_DESCR1 (from FY09) to work area between ACCOUNT and FY09 Total
14. Save
15. Run the report
Add a separate List report to verify your data
1. Click the Toolbox tab
a. Click a List
b. Drag it under the existing list (look for thick, black line)
c. Your work area should look like this:
2. Click Source tab
3. Add these fields: PURPOSE, ACCOUNT, and TRANS_AMOUNT
4. Add this Filter and Validate it:
a. [PURPOSE]='TREA112112' - use the same Purposes code as above
5. Add these three pre-written Filters:
a. ACTUALS Ledger Filter
b. Journal ID not blank Filter
c. Statistics Code not ENP ENU Filter
6. Click TRANS_AMOUNT column heading
a. Click Aggregate and click Total
7. Add pre-written prompt Fiscal Year Prompt
8. Click the Toolbox tab to add a dynamic field for the FY value
a. Drag a Layout Calculation to the new list
b. Make it the 4th column
9. In the Report Expression, click the Parameters tab
a. Drag the fiscal year prompt to the expression box and
b. Validate and click OK
10. Save
11. Run the report
B. Another join with different data queries
You will create a report that returns chartfield and row security (viewers/approvers) information for a specific Speedtype. (Based on EZQ_DV_SPEEDTPYE)
1. Open Report Studio using UD Financial Data Mart
2. Click Create a new report or template
3. Double-click the List icon
4. Click Query Explorer on the Explorer Bar
5. Click Queries
6. From Insertable Objects, drag two additional Queries to the work area.
7. Rename the three Queries (this will make working with them easier)
d. Go to Properties, scroll to Miscellaneous/Name (at the bottom)
i. Click on Query1; change name to Joined Data
ii. Click on Query2; change name to Speedtype
iii. Click on Query3; change name to Row Security
8. From Insertable Objects, drag a Join next to the Joined Data query
9. Save your report and name it Join Speedtype and Row Security
10. Drag the Speedtype query to the top box
11. Drag the Row Security query to the bottom box.
12. Double-click the Speedtype query, this will open the data window
13. Drag the following 11 fields from UD Financial Data Mart to the Data Items pane:
Field Name / Query Subject / FolderSPEEDTYPE_KEY / Chart of Accounts / Speedtype
DESCR / Chart of Accounts / Speedtype
Speedtype CHARTFIELD1 (Purpose) / Chart of Accounts / Speedtype
PURPOSE_DESCR / Chart of Accounts / Purpose
PURPOSE_EFF_STATUS / Chart of Accounts / Purpose
DEPTID1 / Chart of Accounts / Speedtype
PROGRAM_CODE1 / Chart of Accounts / Speedtype
FUND_CODE1 / Chart of Accounts / Speedtype
Speedtype CHARTFIELD2 / Chart of Accounts / Speedtype
PROJECT_ID1 / Chart of Accounts / Speedtype
PROJECT_DESCR / Chart of Accounts / Project
14. Add one Filter and validate it
a. This is a temporary filter to test the query:
b. [SPEEDTYPE_KEY]='CHEM322239' (you may use your own Speedtype or this one)
15. Click the Run menu item and choose View Tabular Data to verify data is good
16. Save your work
17. Open Query Explorer and click Row Security query
18. Add these three fields from UD Financial Data Mart to the Data Items pane:
Field Name / Query Subject / FolderRow Level CHARTFIELD1 / FIN Row Security
Operator ID Descr (name of person) / FIN Row Security
Row Level Approver/Viewer Flag / FIN Row Security
19. Add one Filter and validate it
a. This is a temporary filter to test the query:
b. [Row Level CHARTFIELD1]='CHEM322239' (use the same Speedtype as above)
20. Click the Run menu item and View Tabular Data to verify data is good
(The values in Row Level Approver/Viewer Flag are Y and N – we’ll improve this later.)
21. Disable the temporary filters for CHEM322239 on both queries
a. Row Security query is open, click on the filter in the Detail Filters pane
· Go to Properties
· Click General/Usage and change Required to Disabled
b. Open Query Explorer and click the Speedtype query
· Click on the filter in the Detail Filters pane
· Go to Properties
· Click General/Usage and change Required to Disabled
22. Add the pre-written Purpose Prompt to both queries
a. With Speedtype query open, click the Source tab in Insertable Objects
· Open the Prompts folder
· Drag & drop the Purpose Prompt to the Detail Filters pane
b. Open Query Explorer and click the Row Security query
· Drag & drop the Purpose Prompt to the Detail Filters pane
23. Click Query Explorer, click Queries
24. Double-click the Join
25. In the Join Relationship window, click the New Link button
a. The join is between: Speedtype CHARTFIELD1 and Row Level CHARTFIELD1
b. Set the Cardinality to 1..1 = 1..n and click OK
26. Save your work
27. Open Query Explorer, double-click the Joined Data query
a. You must first add fields to the Joined Data query before the fields will be available for the report’s layout (the List in this case).
28. From Speedtype, drag all the fields to the Data Items pane
29. And from Row Security, drag all the fields to the Data Items pane
30. Click Page Explorer, and then click Page 1
The List is empty; you must add the fields you want.
31. Click the Data Items tab and add all the fields from the Joined Data query (except Row Level CHARTFIELD1 near the bottom – it’s redundant):
Suggestion: click and drag Joined Data to the work area to add all fields and then click the column header for Row Level CHARTFIELD1 and Cut it from the list.
32. Run the report
33. Change Row Level Approver/Viewer Flag values (Y/N) to the words “Approver” and “Viewer”
a. We’ll add a “case when” statement by adding a Data Item Expression (not a Filter!!)
b. In the work area, scroll all the way to the right
c. Double-click the Row Level Approver/Viewer Flag heading
d. A Data Item Expression window pops-up
e. In the expression box enter:
case when [Row Security].[Row Level Approver/Viewer Flag] = 'Y' then 'Approver' when [Row Security].[Row Level Approver/Viewer Flag] = 'N' then 'Viewer'
else ' '
end
f. Validate the expression
g. Click OK
34. Save your work
35. Run the report
Enhancements to the Join Speedtype and Row Security report
►Group the columns to make the report easier to read
1. Click SPEEDTYPE_KEY heading, scroll to the right and [Shift]-click PROJECT_DESCR heading to include all the headings in between
2. Click the Group/Ungroup button