Change existing reports - IBM Cognos

Lynn Cope, an Advanced Business User of a GO Americas subsidiary, needs to analyze which product lines result in the greatest number of returns so that the GO Americas management can focus on these product lines to decrease the number of returns.

Lynn knows that there is one chart inside the IBM Cognos workspace of the Great Outdoors company that already displays the return quantity by product lines. So, Lynn decides to use this existing report as a base for her analysis. To make this possible and easy, the IBM Cognos interface provides a seamless integration between the IBM Cognos Business Insight and IBM Cognos Business Insight Advanced products.

Using this integrated approach, Lynn follows these steps:

  1. Open the IBM Cognos workspace of Great Outdoors by clicking My Folders →Business Insight GO Sales Dashboard_on IBM Cognos Connection
  2. Find the chart for which she is looking, and click Do more on the top of the report’s frame).

Business Insight and Business Insight Advanced integration: Modifying an existing report
Business Insight and Business Insight Advanced integration: Modifying an existing report

After Lynn clicks Do more, the IBM Cognos Business Insight Advanced interface replaces the IBM Cognos Business Insight interface and shows the selected report.

Sort data

With the IBM Cognos Business Insight Advanced interface opens, Lynn makes improvements to the report to meet her needs. First, she changes the sorting configuration for the chart to allow her to see the product lines in the chart sorted by the return quantity of their products. To execute this task:

  1. Click in the chart area to see the chart components
  2. Selecting the chart area
    Selecting the chart area

  3. Click Product line in the Series area
  4. On the top toolbar, click Sort, and then click Edit Layout Sorting.
  5. In the Sort type section, click Descending.
  6. Click Intersection (tuple), and click the ellipsis (...), which opens a window that allows you to insert a tuple.
  7. Advanced Layout Sorting option: Set Sorting window
    Advanced Layout Sorting option: Set Sorting window

  8. Click GO Data Warehouse (analysis) Sales and Marketing (analysis) Returned items Returned items.
  9. GO Data Warehouse package tree: Expanding Returned items folder
    GO Data Warehouse package tree: Expanding Returned items folder

  10. Click the Return Quantity data item under the Returned items metrics folder, and drag it onto the Intersection members and measures area
  11. After the Return quantity member has been added
    After the Return quantity member has been added

  12. Click OK. The Set Sorting window opens again.
  13. Click OK to close the Set Sorting window.

After executing these steps, Lynn determines that Outdoors Protection is the product line that has the worst performance in terms of the return quantity of all Great Outdoors subsidiaries,

Report after sorting has been performed
Report after sorting has been performed

Filter data

To meet her business needs, Lynn needs to filter the report to display only GO Americas and 2007 (current year) totals.

Using Context filters

Because Lynn is working with a dimensional data model, she can easily filter her data using the Context filter feature by following these steps:

  1. Navigate to GO Data Warehouse (analysis) Sales and Marketing(analysis) Sales Organization.
  2. Click GO Americas under the Organization dimension, and then drag it onto the Context filter area.

Adding a Context filter
Adding a Context filter

After performing these steps, Lynn notices that, in fact, the performance of the Outdoors Protection product line and the performance of the Camping Equipment product line in terms of return quantity are extremely close,

Report after GO Americas Context filter is applied
Report after GO Americas Context filter is applied

The current report shows the total of Return Items for all of the years (2004, 2005, 2006, and 2007). To have the best insight about the return quantity for the current scenario, Lynn needs to filter the report to show only the data for the current year (2007). To achieve this result, she follows the same steps to apply another Context filter for 2007

  1. Navigate to GO Data Warehouse (analysis) Sales and Marketing(analysis) Sales
  2. Drag 2007 under the Year dimension to the Context filter area.
  3. Report after 2007 Context filter is applied
    Report after 2007 Context filter is applied

Now, Lynn can determine that, in fact, only one product line, the Camping Equipment product line, has an extremely high number of returns.

Perform calculations

If we think about the business problem, a Product line with a high number of returns for its products does not necessarily mean a large percentage of returns based on the number of products sold for the product line. To find more meaningful information, Lynn needs to calculate the percentage of returns against the number of products sold.

Lynn can create this calculation easily by using the Query Calculation object in the Insert Objects pane, following steps:

  1. Click Query Calculation inside the Insert Objects pane. Drag this object to the same place as the Return quantity metric - Default measure (y-axis),
  2. In the Name field, type % of Items Return.
  3. Click Calculated measure.

Measures: To create this calculation, use the following measures:

  • One measure from the Sales folder
  • One measure from the Returned Items folder
  • Do not set the Measure Dimension.

    Creating a query calculation
    Creating a query calculation

  • Open the Data Items tab. Drag the [Return quantity1] measure to the Expression Definition area.
  • Inserting a member from the Data items tab into Expression Definition
    Inserting a member from the Data items tab into Expression Definition

  • Open the Function tab, expand the Operators folder, and drag the forward slash character (/) into the Expression Definition area.

Inserting operator into Expression Definition

  • Open the Source tab, and navigate to Go Data Warehouse (analysis) Sales and Marketing (analysis) Sales Sales fact.
  • Drag [Quantity] to the Expression Definition
  • Inserting [Quantity] item into Expression Definition

  • To validate the expression, click the Validate icon at the top of the window
  • Starting Expression Definition validation
    Starting Expression Definition validation

    Validation results
    Validation results

    After these changes, Lynn determines that the Camping Equipment product line has 1.8% of its items returned

    Report with the new percent of Returned items metric
    Report with the new percent of Returned items metric

After performing these steps, Lynn realizes that the sorting is not working as expected. To solve this issue, she follows these steps:

  • Click Product line in the Series area.
  • On the top toolbar, click the Sort icon, and then click Edit Set Sorting.
  • Under the Intersection (tuple), click the ellipsis (...).
  • In Intersection members and measures, click Return quantity.
  • Click the left arrow.
  • Click the Calculate members and measures tab.
  • Click % of Returned items.
  • Click the right arrow
  • Members window after the Return quantity member has been removed
    Members window after the Return quantity member has been removed

  • Click OK to close the Members window.
  • Click OK to close the Set Sorting window.

After performing these steps, the chart shows the bars in the correct order

Report after sorting change to percent of Returned items

Report after sorting change to percent of Returned items

Set the right level of detail for the analysis To complete her analysis, now Lynn needs to discover which brands have the highest percentage of returned items. She uses the drill-down and drill-up features as follows:

  1. To drill down into the data, double-click Camping Equipment, and the report is updated to show the product types
  2. Drill down on the product line
    Drill down on the product line

  3. After this action, double-click again on the Lanterns product type
  4. Drill down on the product type
    Drill down on the product type

  5. After this action, Lynn is satisfied with the results, but she needs to fix the sorting of the report. To fix the sorting
    • Click in the chart area.
    • In the Series section, click the <#children(Lanterns)#> data expression.
    • On the top toolbar, click the Sort icon.
    • Click Edit Set Sorting.
    • On the Set Sorting window, click Descending.
    • Click Intersection (tuple).
    • To the right of the Intersection (tuple) field, click the ellipsis (...).
    • Click the Calculated members and measures tab.
    • Click % of Returned items.
    • Click the right arrow.
    • Click OK to close the Members window.
    • Click OK to close the Set Sorting window.

Set Sorting properties for percent of Returned items
Set Sorting properties for percent of Returned items

After performing these steps, Lynn has a report that shows the products that have a higher percentage of returned items

Report after drilling down, ordered by percent of Returned items
Report after drilling down, ordered by percent of Returned items


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics