Additional Calculations and Data Analytics not provided in a report's underlying data source can be added to the data presented in the worksheet through the Calculations and Data Analysis menu, which you access by right-clicking on any member header. These are available at design time and at end-user delivery time if context menus have been enabled on the involved Worksheet object. A number of the most common calculations are provided in the Calculated Members dialog and include drag-and-drop parameter-based experts. Additional experts are provided under the Data Analysis tab on the same Calculated Members dialog. To add any of these default calculations or to create a completely new calculation, right-click on a member or a dimension name, and then choose the Calculated Member option. This opens the Calculated Members dialog shown in Figure 19.51. Figure 19.51. The Calculated Members dialog provides the report designer and end user with the capability to create custom calculated members not available in the OLAP data source.Note OLAP Intelligence automatically chooses a name for any of the predefined calculations. You can change this by typing the preferred name in the Calculation Name edit box. The Calculation ExpertsThe Calculation Experts provided by OLAP Intelligence on the Calculation Expert tab are
Each of the Calculation Experts requires the specification of a different set of parameters in the Calculated Members dialog. These parameters can be set by either clicking and dragging the appropriate members to the involved parameter field or right-clicking on the chosen member and selecting the appropriate destination from the subsequent pop-up menu. The Data Analysis Experts and SummariesThe Data Analysis Experts provided by OLAP Intelligence under the Data Analysis Expert tab are
Each of the Data Analysis Experts requires you to specify a different set of parameters in the Calculated Members dialog. For a more thorough discussion on these calculations and how they are derived, please consult the Reference section of the OLAP Intelligence User Manual provided in the docs directory of your install CD. Caution It is very important to understand the scope under which the Data Analysis Experts operate. When you select any of these experts, they operate across the entire set of members for the dimension that has been selected regardless of whether they are displayed on the current worksheet or viewpoint. Not taking this into account can lead to suspicious-looking data when not all members are displayed. For scenarios where this assumed scope needs to be modified, the underlying MDX or Crystal OLAP Syntax created by the Data Analysis Expert can be modified under the Calculation tab of the Calculated Members dialog. This is introduced later in this chapter. In addition to the predefined Data Analysis Experts, OLAP Intelligence also provides analytic summaries such as Mean, Variance, Standard Deviation, and Best Fitting Curve. Access these summaries by right-clicking any member header and choosing Data Analysis. You can access common analytic summaries through the Summary Statistics menu option and advanced curve estimations through the Best Fitting Curve menu option. Figure 19.52 shows the Best Fitting Curve window and associated summaries. Figure 19.52. You access supporting statistics in the Best Fitting Curve dialog box by choosing the Data Analysis menu option.For detailed insights into the statistics behind the Data Analysis summaries, review the Data Algorithms.pdf document distributed on the product CD. Custom Calculations with MDX or Crystal OLAP SyntaxIn addition to all the experts introduced in the previous two sections, there are times when additional calculations are required to meet a designer or end user's need. The Calculation tab highlighted in Figure 19.53 enables you to create such calculationsor, as is often the case, modify existing calculations (for example, to change the scope of application for a Moving Average or Other Calculation). Figure 19.53. This is the Calculation tab of Calculated Members dialog and the supporting Function Library.
The Calculation tab consists of the four major components, shown in Figure 19.53. These components facilitate the creation of OIQL or MDX statements that can be converted into meaningful fields usable by OLAP Intelligence designers and end users:
Once created, a resultant MDX or OIQL definition created through the Calculations tab appears as just another member in the involved OLAP Intelligence reportwhich could even be used in future custom calculations. |