Adding a dimensional calculation to a blank reportAdded by IBM on February 11, 2013 | Version 1 (Original)
|Create a report using a dimensional calculation to query the PowerCube and retrieve the tuple representing the intersection between different dimensions of the data.
About this task
In reports, basic data items use counts drawn directly from the PowerCube. The example reports showing total "update" and "create" events for the Connections applications simply query the Metrics PowerCube directly for those totals and require no additional calculation. A more complex report might present the number of "create" events for the "file" item in the Files application only (that is, it would show the number of new files created in the Files application). This count is not stored directly in the PowerCube; however the "create" event counts, the different data items, and the different Connections applications are all included in the PowerCube. Each of those values is represented in a dimension
of the PowerCube; the point where they intersect is called a tuple
. You can create a dimensional calculation
for a report to query the PowerCube for the value stored in the tuple that represents that intersection.
The instructions that follow demonstrate the procedure for creating a report using a dimensional calculation to query the PowerCube and retrieve a tuple. Follow the steps in the example to create a report using a dimensional calculation to query the Metrics PowerCube and retrieve the tuple representing "new files created in the Files application for the years 2010, 2011, and 2012" and then display the result in a trend chart.
For more information on the dimensions available in the Metrics PowerCube, see PowerCube dimensions
. For more information on working with Cognos
Report Studio, see the Report Studio User Guide
Parent topic: Creating a report from a blank layout
- Create a blank report as described in Creating a blank report from the PowerCube model.
- Add a trend chart to the report showing the total number of "create" events for all Connections applications for the years 2010, 2011, and 2012, as described in Adding a chart to a blank report.
- View the PowerCube query that generates the data for the basic trend chart:
When you created the trend chart, the query was generated automatically based on the measure, categories, and data series that you placed in the chart.
- Click View -> Queries to display the queries used in the report.
- Double-click Query1 to open the query definition window.
- In the Properties window, expand Data and locate the Query attribute.
- Define the tuple that you want to display in the report:
For this example, the tuple represents the intersections of the following data items:
- The FILES category (the Connections application being report on)
- The FILE item (the report will only count files in the selected application and will ignore other others such as comments)
- The CREATE measure (the report will only count "create" events)
- Click the Toolbox tab. tab.
- In the Toolbox window, select the Data Items object and drag it to the Data Items area within the query definition window. object and drag it to the Data Items area within the query definition window.
You can use the Intersection (Tuple)
object to define a tuple but the Data Item object because is more flexible and because it supports more functions and calculations.
- In the Data Item Expression window, type tuple(...) in the Expression field.
- Click the Sources tab. tab.
- Expand SOURCE and click FILES; drag it to the Expression field and drop it between the ( ) in the expression.
The expression now reads tuple( [FILES]) and represents the Files application.
- Return to the Sources tab, expand ITEMS and click FILE; drag it to the Expression field and drop it between the ( ) in the expression.
The expression now reads tuple( [FILES], [FILE]) and represents the intersection of the Files application and the file item.
- Return to the Sources tab, expand EVENT -> EVENT -> Members -> EVENT nd click CREATE; drag it to the Expression field and drop it between the ( ) in the expression.
The expression now reads tuple( [FILES], [FILE], [CREATE])
and represents the intersection of the Files application, the file item, and the create event. This expression merely retrieves the value that resides at the intersection of the three data items; it does not actually perform any calculations on that value. For a list of the functions available for use in the data item expression, click the Functions
- Click OK to save the expression and close the Data Item Expression window.
- Rename the new data item so you can easily find and work with it later:
- Click the data item (the tuple) that you just created.
- In the Properties window, locate the Data Item section.
Data items are named in the order in which they were created. This report has a single data item, named DataItem1 by default.
- In the data item, locate the Name attribute; edit the name and replace it with a meaningful name; for example: NumOfNewFiles.
The new data item name is reflected in the Data Items area within the query definition window.
- Modify the trend chart to use the new NumOfNewFiles data item:
- Click View -> Report Pages.
- Double-click Page1 to return to the base report.
- In the chart, delete the CREATE date item from the Series area.
- Click the Data Items tab. tab.
This tab appears now that you have a data item defined.
- In the list of data items, click NumOfNewFiles and drag it to the Series area of the chart to replace the CREATE data item that you deleted.
- Click Save to save the updated report.
- Click Run -> Run Report - HTML to test the report by viewing it in the Cognos Viewer; then close the viewer when ready to proceed.