Using Query Studio, users with little or no training can quickly design, create and save reports to meet reporting needs not covered by the standard, professional reports created in Report Studio.
Example- Create a Query
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
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:
Procedure
1. Open IBM Cognos Connection:
a. Start your Web browser.
b. In the address bar, type the URL supplied by your administrator, and then press Enter. The URL looks something like this: http://servername/cognos
2. In the IBM Cognos Welcome page, click Query my data, and select the GOData Warehouse (query) package. A blank report appears.
3. Add columns to the report:
a. From the Menu, click Insert Data.
b. 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.
c. 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.
d. Expand Returned items (query), and Returned items fact, and drag Return quantity to the work area.
4. Create a calculation to show the lost revenue (Return quantity * Unit cost = Lost revenue):
a. 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.
b. Click Calculate.
c. In the Operation box, select * (multiplication).
d. In the New item name box, type Lost revenue.
e. Click Insert.
f. Delete the Unit cost column.
5. Format the calculated column that you added:
a. Right-click the heading for the Lost revenue column and click Format Data.
b. In the Category list, click Currency.
c. In the Thousands separator list, click Yes, and then click OK.
6. Create a calculation to show the percentage of returns (Return quantity /Quantity)*100):
a. In the work area, click the heading for the Return quantity column, and Ctrl+click the heading for the Quantity column.
b. Click Calculate.
c. In the Operation type box, select Percentage.
d. The content of the Operation box changes to percent (%).
e. In the New item name box, type % Returned.
f. Click Insert.
7. Sort the % Returned column in descending order:
a. Right-click the heading for the % Returned column, and click Sort.
b. Under Sort order, click Descending (9 to 1), and then click OK.
8. Apply conditional formatting to highlight products with a lost revenue greater than $200,000:
a. Click the heading for the Lost revenue column.
b. From the Menu, click Change Layout, and click Define Conditional Styles.
c. In the New value: box, type 200000, and click Insert.
d. 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.
9. Apply a template to the report:
a. From the Menu, click Change Layout, and click Apply Template.
b. Select Apply a template, and click Select a template.
c. In Public Folders, click Samples, Sample Template. Select Great Outdoors Template, and click OK twice.The report appears with the styles from the template.
10. Add a title to the report:
a. Above the report, click the report title.
b. In the Title box, type Returns by Product Type. Clear the Subtitle box, and click OK.
11. Save the report:
a. From the toolbar, click Save.
b. In the Name box, type Returns by Product Type.
c. Accept the default save location, and click OK.
The final report looks like this:
Example- Create a Query
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
1. Open IBM Cognos Connection:
a. Start your Web browser.
b. In the address bar, type the URL supplied by your administrator, and then press Enter. The URL looks something like this: http://servername/cognos
2. In the IBM Cognos Welcome page, click Query my data, and select the GOData Warehouse (query) package. A blank report appears.
3. Add columns to the report:
a. From the Menu, click Insert Data.
b. 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.
c. 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.
d. Expand Returned items (query), and Returned items fact, and drag Return quantity to the work area.
4. Create a calculation to show the lost revenue (Return quantity * Unit cost = Lost revenue):
a. 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.
b. Click Calculate.
c. In the Operation box, select * (multiplication).
d. In the New item name box, type Lost revenue.
e. Click Insert.
f. Delete the Unit cost column.
5. Format the calculated column that you added:
a. Right-click the heading for the Lost revenue column and click Format Data.
b. In the Category list, click Currency.
c. In the Thousands separator list, click Yes, and then click OK.
6. Create a calculation to show the percentage of returns (Return quantity /Quantity)*100):
a. In the work area, click the heading for the Return quantity column, and Ctrl+click the heading for the Quantity column.
b. Click Calculate.
c. In the Operation type box, select Percentage.
d. The content of the Operation box changes to percent (%).
e. In the New item name box, type % Returned.
f. Click Insert.
7. Sort the % Returned column in descending order:
a. Right-click the heading for the % Returned column, and click Sort.
b. Under Sort order, click Descending (9 to 1), and then click OK.
8. Apply conditional formatting to highlight products with a lost revenue greater than $200,000:
a. Click the heading for the Lost revenue column.
b. From the Menu, click Change Layout, and click Define Conditional Styles.
c. In the New value: box, type 200000, and click Insert.
d. 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.
9. Apply a template to the report:
a. From the Menu, click Change Layout, and click Apply Template.
b. Select Apply a template, and click Select a template.
c. In Public Folders, click Samples, Sample Template. Select Great Outdoors Template, and click OK twice.The report appears with the styles from the template.
10. Add a title to the report:
a. Above the report, click the report title.
b. In the Title box, type Returns by Product Type. Clear the Subtitle box, and click OK.
11. Save the report:
a. From the toolbar, click Save.
b. In the Name box, type Returns by Product Type.
c. Accept the default save location, and click OK.
The final report looks like this: