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

The Calculation Experts provided by OLAP Intelligence on the Calculation Expert tab are

  • Contribution (%) Calculates how much each member of a hierarchical dimension contributes to its parent. For example, how much does each week, period, and quarter contribute to total sales?

  • Growth Calculates how much a value has changed from one period to the next. For example, what is the percentage growth in sales week on week, period on period, and quarter on quarter?

  • Ranking Calculates the rank of each member in a dimension, usually based on a measure. For example, rank each product based on sales.

  • Variance Compares the value of one dimension member with a target value; the resulting variance can be expressed as an absolute value or a percentage variance.

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 OLAP Intelligence under the Data Analysis Expert tab are

  • Trend Line The Trend Line Expert calculates the straight line that best fits all members of the dimension specified in the Series Dimension list. This is done for the measure specified in the Trend Of box. The least squares method is used: minimizing the sum of the squares of the differences between the actual values specified and the regression line values.

  • Moving Average The Moving Average Expert calculates a centered moving average over all the members within each level of a specified dimension. This is done for the measure specified in the Moving Average parameter box.

  • Linear Regression The Linear Regression Expert calculates the straight line that best fits all the members within each level of the dimension. The members of this dimension form the columns of the worksheet (assuming you are adding a calculated member as a row) where the X and Y values of the points are given by the members specified in the X Values box and Y Values box, respectively. The least squares method is used: minimizing the sum of the squares of the differences between the actual Y values specified and the regression line values. The regression line is evaluated at these same X values as specified by the member in the X Values box. Use the Linear Regression Expert when the data values you want to regress are not evenly spaced.

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


OLAP Intelligence Query Language Syntax and MDX

OLAP Intelligence Query Language (OIQL) syntax and Multi-Dimensional Expressions (MDX) 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 OIQL. OLAP Intelligence uses MDX to access SQL Server cubes and OIQL 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 OLAP Intelligence Help file (look up OLAP Intelligence Query Language in the Index tab).

Similar to an SQL query, each MDX or OIQP 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. OLAP Intelligence uses MDX and OIQL 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 OIQL and there are some practical samples available for download from www.usingcrystal.com.


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:

  • Calculation Definition This is the actual MDX (if you're using SQL Server) or OIQL syntax that is calculated by or through OLAP Intelligence against the underlying data source.

  • Dimension and Member Selectors These components facilitate the selection of dimensions and dimension members to be used in creating the custom calculation. When dimensions or members are selected through a double-click, the appropriate syntax for referencing them is transposed into the calculation definition for future editing. It is worth noting that the transposed text might not always reflect the exact user-friendly member syntax displayed in the Member Selector.

  • OLAP Function Library Clicking on the Function Library button provides a library of MDX or OIQL functions that might be used in the creation of the involved custom calculation. A few of the most common and useful functions are described in the next section.

  • Basic Operations Keypad A keypad providing and basic math operations and numerics for use in creating the calculation definition.

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.




Crystal Reports XI(c) Official Guide
Crystal Reports XI Official Guide
ISBN: 0672329174
EAN: 2147483647
Year: N/A
Pages: 365

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net