MDX Calculated Member

Question

I am trying to create a TFS report which requires MDX. I am making a report that requires a calculated member. I am not 100% sure how this is done. The member i am trying to do is a difference calculation.

For example:

Table

               Sept 1    Sept 2    Sept 3
Actual         0         32        58
Remaining      163       140       132
Difference     0         9         50

The calculation for the difference is as follows: Actual Effort for that day - (Work remaining previous day - Work remaining that day) 32 - (163 - 140) = 9

I have installed Business Intelligence Development from SQL Server 2008 to use to create TFS Reports In Visual Studio 2008. When I add a new report a query builder window opens. This is where Measures and other information to get the data is done. This is where I am trying to create the Calculated member. Any suggestions??

Answer 1

Calculated members are created when designing/building the cube, just like measures and dimensions. If you really want to add the calculated member, you will have to add it to the cube itself.

You can however, created calculated memebers inside your own query, something like this:

WITH 
   MEMBER [Measures].[Special Discount] AS
   [Measures].[Discount Amount] * 1.5
SELECT 
   [Measures].[Special Discount] on COLUMNS,
   NON EMPTY [Product].[Product].MEMBERS  ON Rows
FROM [Adventure Works]
WHERE [Product].[Category].[Bikes]

Here's a link to the MSDN article that describes how to do this: Creating Query-Scoped Calculated Members (MDX)