Custom Calculations and Advanced Data Analysis

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 20.15.

Figure 20.15. 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

Crystal Analysis 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 Experts

The Calculation Experts provided by Crystal Analysis 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 Summaries

The Data Analysis Experts provided by Crystal Analysis 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 Crystal Analysis 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, Crystal Analysis 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. Figure 20.16 shows the Best Fitting Curve dialog and associated calculations.

Figure 20.16. 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 Algorithms.pdf document distributed on the product CD.

Custom Calculations with MDX or Crystal OLAP Syntax

In 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 Calculations tab highlighted in Figure 20.17 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 20.17. This is the Calculations tab of Calculated Members dialog and the supporting Functions Library.

Crystal OLAP Syntax and MDX

Crystal OLAP Syntax and MDX (Multi-Dimensional Expressions) are related but different syntaxes that support the definition and manipulation of multidimensional objects and data. They can be conceptually thought of as a parallel to Structured Query Language (SQL), which is used for querying relational data, but for multidimensional data sources. There is, however, no direct relationship between SQL and either MDX or Crystal OLAP Syntax. Crystal Analysis uses MDX to access SQL Server cubes and Crystal OLAP Syntax for the remaining supported data sources. Thorough descriptions can be found online for MDX at www.msdn.com (search on MDX) and in the Crystal Analysis Help file (look up Crystal OLAP in the Index tab).

Similar to an SQL query, each MDX or Crystal OLAP query requires a data request (the SELECT clause), a starting point (the FROM clause), and a filter (the WHERE clause). These and other keywords provide the tools used to extract specific portions of data from a cube for analysis. Crystal Analysis uses MDX and Crystal OLAP queries to capture data from the underlying multidimensional data sources. When using MDX (against SQL Server cubes), these queries can be viewed and edited through the Edit MDX option on the Tools menu. Additionally, both these syntaxes support extension through use of calculated members. This is generally the focal area for the report designer's exposure to MDX or Crystal OLAP and is covered through some practical examples later in this section.

The Calculations Tab consists of the four major components, shown in Figure 20.17. These components facilitate the creation of Crystal OLAP or MDX statements that can be converted into meaningful fields usable by Crystal Analysis designers and end users:

Once created, a resultant MDX or Crystal OLAP definition created through the Calculations tab appears as just another member in the involved Crystal Analysis report--which could even be used in future custom calculations.

Related

Категории