Creating a calculated member

Since we want to take advantage of the power of the analytic engine, we want all of the aggregations in our report to be calculated in the cube rather than in our report server. This is an efficient way of handling the calculations.

Let's say we want to find out the variance between the sales goal and the actual sales of each territory in dollars. We can handle this simple calculation using the query designer:

  1. On the query designer, right-click on the empty space in the Calculated Members pane on the lower left-hand side of the designer, as shown in the following screenshot:
    Creating a calculated member
  2. That displays the Calculated Member Builder dialog box. Let's name this calculation Sales Goal Variance.
  3. Expand KPIs in the Metadata pane. Expand Revenue.
  4. Drag Value and Goal to the Expression pane. Insert a minus sign (-) between the two values to indicate subtraction. You can also write the expression directly to the Expression pane.
  5. Format the value by adding a format property to the calculated value like this: Format_String = "Currency".
    Creating a calculated member

    Note

    Find more information on string format from TechNet see FORMAT_STRING Contents (MDX) at http://technet.microsoft.com/en-us/library/ms146084.aspx.

  6. Click on OK when done. You may encounter a parse error but you can ignore it for now. The syntax passes the syntax check.
    Creating a calculated member
  7. Click on OK in the error box.
  8. In the Query Designer window, drag the Sales Goal Variance to the existing table:
    Creating a calculated member
  9. Click twice on OK to return to the report designer.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.12.108.175