Export (0) Print
Expand All

How to create a calculated member in a regular cube

SQL Server 2000

To create a calculated member in a regular cube

  1. In the Analysis Manager tree pane, under the database in which you want to create the calculated member, expand the Cubes folder.

  2. Right-click the cube in which you want to create the calculated member, and then click Edit.

  3. In Cube Editor, on the Insert menu, click Calculated Member.

  4. In Calculated Member Builder, in the Parent dimension box, select the dimension that will include the calculated member, or select Measures.

  5. In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member. (Change is unavailable if you selected a one-level dimension or Measures in Step 4.)

  6. In the Member name box, type a name for the calculated member.

  7. In the Value expression box, construct an expression to produce the values of the calculated member. Use any combination of the following methods to add to the expression:
    • Drag items from the Data and Functions boxes.

    • Click an item in the Data or Functions box, and then click Insert.

    • Click the arithmetic operator and number buttons.

    • Type. This method is required to add functions from libraries other than the Microsoft® SQL Server™ 2000 Analysis Services function library.
  8. (Optional.) To register additional function libraries, click Register.

  9. To close Calculated Member Builder, click OK.

  10. To save the cube with the new calculated member, in Cube Editor, on the File menu, click Save.
Example

To manually enter a calculated member that finds the average profit margin by store, which is defined as 1 - (Store Cost/Store Sales), follow these steps:

  1. In the Value expression box, type 1-.

  2. Click the opening parenthesis (() operator button, and then in the Value expression box, click immediately to the right of the parenthesis to place the cursor.

  3. In the Data box, expand Measures and MeasuresLevel to display the individual measures.

  4. Select Store Cost, and then click Insert.

  5. In the Value expression box, place the cursor at the end of the phrase that was just entered, and then type a slash mark (/).

  6. In the Data box, select Store Sales.

  7. Click Insert.

  8. Click the closing parenthesis ()) operator button.

    The following expression appears in the Value expression box:

    1-([Measures].[Store Cost]/[Measures].[Store Sales])
    
  9. In the Member Name box, type Average Store Margin, and then click OK.

See Also

Creating Calculated Members in Regular Cubes

Show:
© 2014 Microsoft