How to build Report Studio reports using multidimensional cubes as a source

Are you building Report Studio reports using multidimensional cubes as a source? Are you wondering how to use multidimensional functions such as ancestor, periodsToDate, or cousin? Would you like to know how to create a report that shows the top 10 customers that are purchasing your products? Would you like to learn how to make your cube sourced reports more efficient? Then the IBM Cognos BI Report Studio v10.2 Cube Sourced Reporting course is for you!

Here is an example of just one of the many reports we create in this class:

Focus Your Report by Using the Exclude and Top Count Functions

Objective: You will create a report using the except( ) function to display only those retailers in the Americas region. You will use the topCount( ) function to display the top 20 revenue producing retailers from the Americas region.

Step 1: Create an unfiltered crosstab report.

  1. Using the Sales and Marketing (cube) package, create a new Crosstab report.
  2. On the Source tab, expand the Time dimension, and then the Time hierarchy.
  3. Drag the Year level to the Columns drop zone.
  4. Expand the Measures dimension, and then drag Revenue to the Measures drop zone.
  5. Expand the Retailers dimension, and then the Retailers hierarchy.
  6. Drag the Region level to the Rows drop zone.
  7. Drag the Retailer name level to the right of <#Region#> in the Rows drop zone to nest as a child.
  8. Click Run Report and page down to see more than one region.The results are as follows:
  9. Close IBM Cognos Viewer.

Step 2: Show only retailers from the Americas region.

  1. On the Menu above the toolbar, click Structure.
  2. Click Create Crosstab Nodes in the drop-down list to clear the option.This menu item will allow you to add another data item into the rows as a parent of the retailers already included in the rows without creating another crosstab node in the rows.
  3. On the Source tab, click Region and drag the level to the row, placing it as a peer of <#Region#>.
  4. Right-click <#Region1#>, and then click Edit Set.
  5. In the Set Definition dialog box, click New, and then click Exclude.
  6. In the Available members: pane, expand Retailers, select Central Europe, Asia Pacific, Northern Europe, and Southern Europe, click the green right arrow to add it to the Members pane.
  7. Click OK.If you hover the mouse over the Exclude icon in the Set Definition window, the excluded members will display in the tooltip.
  8. Click OK to close the Set Definition dialog box.Another option with which to create the new set definition would be to use the except( ) function in a query calculation.except([sales_and_marketing].[Retailers].[Retailers].[Region],set([Central Europe],[Asia Pacific],[Northern Europe],[Southern Europe]))The Region1 row contains only Americas retailers, so you will remove the Region row from the report layout.
  9. In the report layout, click <#Region#>, and then on the toolbar click Delete.
  10. Click <#Region1#>, in the Properties pane, in the Data Item section, click Name.
  11. Change the property to Americas Retailers.
  12. On the Structure menu drop down list, click Create Crosstab Nodes to reset this option to create nodes.
  13. In the report layout, click <#Americas Retailers#>, click the Summarize button, and then select Automatic Summary from the drop down list.
  14. To make this name more meaningful to the nature of the report, click Summary and in the Properties pane, change the Labelproperty in the Data Item section to Summary (All Americas Retailers).
  15. Click Run Report, and click Bottom to confirm the summary totals for the Americas region.The results are as follows:
    Now that the total for all retailers in the Americas region is computed, you would like to limit the Americas retailers to the top 10 revenue producing retailers for all time.
  16. Close IBM Cognos Viewer.

Step 3: Show the top 10 retailers in the Americas region.

  1. In the report layout, right-click <#Retailer name#>, and then click Edit Set.
  2. Click New, and then click Top.
  3. In the Top or Bottom dialog box, set the top count to 10 in the Number of items section.
  4. In the Based on intersection (tuple): click the ellipsis.
  5. In the Available members and measures: expand Measures and add Revenue to the Intersection members and measures:area.An alternate method would be to add a query calculation as a nested row such as this:topCount([Retailer name],10,[Revenue])
  6. Click OK twice.In the Set Definition dialog box, you will see the Top 10 icon.
  7. Click OK to close the Set Definition dialog box, and then Run Report.The results are as follows:
    The top 10 Americas retailers are shown in this report. The summary row, however, reflects the total revenue for all Americas retailers. In the next step, you will summarize the nested rows to reflect the total for just the top 10 retailers.
  8. Close IBM Cognos Viewer.
  9. In the report layout, click <#Retailer name#>, and then on the toolbar click Summarize.
  10. In the drop-down menu, click Automatic Summary.
  11. In the report layout, click Summary, in the Properties pane, in the Data Item section, click Label.
  12. Change the property to Summary (Top 10 Americas Retailers).
  13. Click Run Report.The results are as follows:

    The report now shows the top 10 retailers in the Americas region. The two summary rows allow you to compare the aggregation of just the Top 10 in the Americas region with the summary of all retailers in the Americas region. By modifying the expressions to exclude all other regions but Americas and to show only the top 10 retailers you have retrieved a limited amount of records from the cube.
  14. Close IBM Cognos Viewer.

You can find this two-day course, and many others, on our website. The link to our course catalog is:https://www.dataclaritycorp.com/services/education-services.html

You can access our course catalog at this location and register for courses here as well. Please contact us if you have any questions at all about the course, the schedule or just general education questions. Use the “chat” feature in the lower right hand corner of the screen.

Thank you for considering DataClarity Education for your training needs and we look forward to having you in class!

About the Author:

Leave A Comment