Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 5May 15, 2006 Procedure Reporting with Analysis Services KPIs Add KPI Values to the Report Our clone of the Sales Reason Comparisons report, the sole OLAP report in the sample set, will serve as a quick "framework," since it is already in place and has several attributes that will allow us to modify it quickly. We can then add KPI values. 1. Click the Data tab to return to Data view. We arrive at the MDX Query Designer, where we will be working in Design View for this part of our session. 2. Select the sole item (Product) in the Filter pane, in the upper right corner of the Query Designer. 3. Click the Delete button to remove the filter, as shown in Illustration 32.
The filter disappears, and the grid updates (unless Auto Execute is disabled) in the Results pane below. 4. Right click a point with the Results pane. 5. Select Clear Grid to begin a fresh start with the DataSet, as depicted in Illustration 33.
6. In the Metadata Browser pane, expand the Product dimension in the metadata tree by clicking the "+" sign to its immediate left. 7. Drag the Subcategory attribute from the Metadata Browser pane over to drop on the main Results pane, as shown in Illustration 34.
8. In the Metadata Browser pane, expand KPIs in the tree. 9. Drag the Internet Revenue KPI from the Metadata Browser pane over to right of the new Subcategory column, dropping it when the red line appears, as depicted in Illustration 35.
We see that the KPI, which represents a collection of calculations that we can use to support decisions and evaluate success, and which are associated with a given measure group (or, alternatively, all measure groups) within the cube, has brought along four separate members. The member components of the Internet Revenue KPI are as follows:
The Goal and Value components are typically the most significant and common, although Status and Trend, which are based upon Goal and Value, are often in demand, as well. Other calculations are available besides the above examples. For an in-depth discussion of Analysis Services 2005 KPIs, see my article Mastering Enterprise BI: Introduction to Key Performance Indicators. Let's add a couple of filters at this point. While we might select multiple years' operations for analysis, we will limit our practice report to Fiscal Year 2004. In addition, we will filter our null values to generate a more compact report. 10. In the Filter pane, select Date in the Dimension selector. 11. Select Date.Fiscal Year in the Hierarchy selector of the Filter pane, as depicted in Illustration 36.
12. Select Equal in the Operator selector for the Filter. 13. Select FY 2004 in the Filter Expression selector of the Filter pane as shown in Illustration 37.
The Filter pane, with our selections, appears as depicted in Illustration 38.
We are now ready to modify the matrix data region on the Layout tab, where the layout remains the same even though we have effectively redesigned the underlying DataSet. 14. Click the Layout tab. 15. Select the left-most Data field within the matrix to select it, as shown in Illustration 39.
16. Press the DELETE key on the PC. 17. Delete the second and third Data fields in the matrix in similar fashion. The second Data field (along with the respective column) disappears and the third Data field clears, leaving the Data watermark exposed, as depicted in Illustration 40.
Next, we will eliminate the Report Parameter for Product Category (which referenced the Product filter we removed earlier in the Filter pane of the Data tab.) 18. Select Report --> Report Parameters ... from the main menu, as shown in Illustration 41.
19. Select the sole Product Category parameter appearing in the Parameters list on the left side of the Report Parameters dialog, which appears next. 20. Click the Remove button underneath the Parameters list, as depicted in Illustration 42.
21. Click OK to accept the modification and dismiss the Report Parameters dialog. We return to the Layout tab. Next, we will modify some properties of the matrix data region. 22. Click any point within the matrix to cause its gray header and row borders to appear. 23. Right-click the upper left corner of the matrix, as shown in Illustration 43.
24. Select Properties from the context menu that appears (the gray borders disappear, and the matrix data region assumes a semi-transparent outline), as depicted in Illustration 44.
The Matrix Properties dialog opens, defaulted to the General tab. 25. Click the Groups tab. 26. In the Rows list in the top half of the Groups tab, select the sole group (matrix1_Sales_Reason). 27. Click the Edit button, as shown in Illustration 45.
The Grouping and Sorting dialog opens, defaulted to the General tab. 28. Replace the contents of the Name field with the following: matrix1_ProductSubcategory 29. In the Group on section, select the following to replace the current contents of the top row of the Expression list: =Fields!Subcategory.Value The Grouping and Sorting dialog - General tab appears as depicted in Illustration 46.
30. Click OK to accept the modifications and to return to the Matrix Properties dialog – Groups tab. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
