Search code examples
expressionssasmdxmember

SQL in Calculated member expression

I need to use a value, selected from database table, in my Calculated member expression. How I can refer to objects outside the cube?

For example: I have measure "Sales" in my Cube. And I want to create a Calculated member which will be a multiplication of "Sales" and some constant, selected from database table.

CALCULATE;   
CREATE MEMBER CURRENTCUBE.[Measures].MultiSales
 AS [Measures].[Sales] * <and here I need to add constant from database table>, 
VISIBLE = 1;

How I can refer to database table? (this table in same datasource as cube)

Solution

  • you cant. The data on your calculation "must" be on the cube. I say must in quotes because it depends on your storage mode. If you have a ROLAP storage mode the data wont actually reside on the cube. But that is a storage configuration.

    If you want to use a value on your calculated member, create a measure group based on the table you have this value