Use IBM® Cognos® Query Studio to retrieve
information from a relational data source when you want to create
reports that answer simple business questions. Also use Query Studio
to publish reports for audiences who do not require complex layout
or formatting.
In this exercise, you will learn how
to
- Add columns
- Add calculations
- Add conditional formatting
- Apply a template to the report
- Save the report
You are a business analyst for the Sample Outdoors Company.
You want to create a report that shows which products have the highest
return rate and how much revenue was lost from product returns.
To
perform this exercise, you must have the appropriate licensing and
security permissions.
Things to notice:
- Data that you can add to the report has a different icon depending
on the data type.
- The reports that you create in Query Studio can be viewed and
edited in IBM Cognos Report Studio.
Procedure
- Open IBM Cognos Connection:
- Start your Web browser.
- In the address bar, type the URL supplied by your administrator,
and then press Enter. The URL looks something like this: http://servername/cognos
- In the IBM Cognos Welcome page,
click Query my data, and select the GO
Data Warehouse (query) package. A blank
report appears.
- Add columns to the report:
- From the Menu, click Insert
Data.
- Expand Sales and Marketing (query), Sales
(query), and Products. Drag Product
type and Product to the work area.
The work area is the area within a studio that contains the report,
analysis, query, or agent that you are currently using.
- From Sales (query), expand Sales
fact, and drag Quantity and Unit
cost to the work area. The item Unit cost will
not appear in the final report, but you need it to build a calculation.
- Expand Returned items (query),
and Returned items fact, and drag Return
quantity to the work area.
- Create a calculation to show the lost revenue (Return
quantity * Unit cost = Lost
revenue):
- Select the column headings that you want for the calculation.
In the work area, click the heading for Return quantity and
Ctrl+click the heading for the Unit cost column.
- Click Calculate .
- In the Operation box, select *
(multiplication).
- In the New item name box, type Lost
revenue.
- Click Insert.
- Delete the Unit cost column.
- Format the calculated column that you added:
- Right-click the heading for the Lost revenue column
and click Format Data.
- In the Category list, click Currency.
- In the Thousands separator list,
click Yes, and then click OK.
- Create a calculation to show the percentage of returns
(Return quantity / Quantity)*100):
- In the work area, click the heading for the Return
quantity column, and Ctrl+click the heading for the Quantity column.
- Click Calculate .
- In the Operation type box, select Percentage.
- The content of the Operation box
changes to percent (%).
- In the New item name box, type %
Returned.
- Click Insert.
- Sort the % Returned column in descending
order:
- Right-click the heading for the % Returned column,
and click Sort.
- Under Sort order, click Descending
(9 to 1), and then click OK.
- Apply conditional formatting to highlight products with
a lost revenue greater than $200,000:
- Click the heading for the Lost revenue column.
- From the Menu, click Change
Layout, and click Define Conditional Styles.
- In the New value: box, type 200000,
and click Insert.
- For the first range, in the Style column,
select Poor, and click OK.
The cells with a lost revenue greater than $200,000
appear with a red background.
- Apply a template to the report:
- From the Menu, click Change
Layout, and click Apply Template.
- Select Apply a template, and
click Select a template.
- In Public Folders, click Samples, Sample
Template. Select Great Outdoors Template,
and click OK twice.
The report appears with the styles from the template.
- Add a title to the report:
- Above the report, click the report title.
- In the Title box, type Returns
by Product Type. Clear the Subtitle box,
and click OK.
- Save the report:
- From the toolbar, click Save.
- In the Name box, type Returns
by Product Type.
- Accept the default save location, and click OK.
The final report looks like this:
Notice that the
Star Gazer 2 tent has the highest lost revenue rate.
What to do next
For information about using Query Studio, see the Query
Studio online help.