- Launch Query Studio
- Query Studio High-Level Interface
- Report Types
- Open an Existing Report
- Create a List Report
- Run the Report
- Change Layout
- Create a Crosstab Report
- Create Filters
- Summarize Report
- Sort Data
- Create Calculations
- Create Charts
- Define Conditional Styles
- In a Nutshell: Reviewing by IBM Cognos BI Audience
This chapter is from the book
This chapter is from the book
This chapter is from the book
Create Calculations
Use calculations to add derived information to the report that is not available in the database such as Ranking the rows to display the top 5 or bottom 5 performers, as shown in Figure 5.19.
)
Figure 5.19. Create calculations in Query Studio.
Steps to Create Calculations
Figure 5.19 illustrates steps to create calculations in Query Studio as described in the list that follows.
- In IBM Cognos Connection, click Launch > Query Studio.
- Select the GO Data Warehouse (query) package when prompted to select a package.
- Click New icon on the Standard toolbar to create a new report.
- Expand GO Data Warehouse (query) > Sales and Marketing (query) > Sales (query).
- On the Insert Data menu, Ctrl+click Order Method Type from Order method and Quantity from Sales fact > click the Insert button.
- Click the Quantity column in Sales Fact.
- Click the Calculate icon on the Standard toolbar.
- In the Calculate window, select Operation type > Analytic.
- In the Calculate window, select Operation > Rank.
- In the Calculate window, for the New item, name the type Rank.
- Click Insert.
- Click the Rank column > click Cut on the Standard toolbar.
- Click the Order Method Type column > click Paste on the Standard toolbar.
- Click the Rank column > click the Edit Data menu > Sort > Ascending.
Calculations cannot be edited, and therefore if they are incorrect, you must delete and re-create them as required.
You can create complex calculations by creating multiple calculated items, for example, calculation A and calculation B. You can then create calculation C based on calculation A and calculation B. Finally, if you want to display only the final calculation, that is, calculation C, then you can delete calculation A and calculation B from the report.
Define Custom Groups
Custom groups enable you to create grouping in data not already defined in the data source, as shown in Figure 5.20.
)
Figure 5.20. Create custom groups in Query Studio.
To create custom groups, perform these steps:
- In IBM Cognos Connection, click Launch > Query Studio.
- Select GO Data Warehouse (query) package when prompted to select a package.
- Click the New icon on the Standard toolbar to create a new report.
- Expand GO Data Warehouse (query) > Sales and Marketing (query) > Sales (query).
- Expand Products > click on Product line > click Insert.
- Expand Sales fact > click Quantity > click Insert.
- Select the Product line column.
- In the Edit Data menu > click Define Custom Groups.
- In the Define custom groups window, in the New group name box, type Summer Promotion > click the down green arrow to move it to the Custom groups box, as shown in Figure 5.21.
Figure 5.21. Custom group options and results.
- In the Available values box, Ctrl+click Camping Equipment, Golf Equipment, and Mountaineering Equipment > click the left green arrow to move it to the Custom group values box.
- In the New group name box, type Other to group all remaining values > click the left green arrow to move it down to the Custom group values box.
- In the Available values box, Ctrl+click remaining values, that is, Outdoor Protection and Personal Accessories.
Click OK.
NOTE: A new column Product line (Custom) is added with values Summer Promotion and Other as defined, as shown in Figure 5.21.
)