Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
MDX calculation: calculate for each product and then sum

Answered MDX calculation: calculate for each product and then sum

  • Friday, June 22, 2012 11:28 AM
     
     
    Sign In to Vote
    0

    I am trying to create a calculated measure which should work like this:

    Calculate result of a formula for each product from product dimension. Then SUM all of these results together.

    Pseudo algorithm looks like this:

    SUM(
    foreach (product in Dim_Product)
    return RESULT_FROM_SOME_FORMULA
    )

    Does anyone know how to write such type of calculation?

     

All Replies

  • Friday, June 22, 2012 1:20 PM
     
     Answered Has Code
    Sign In to Vote
    0

    So much depends on the Dimension/attribute factors found in your "RESULT_FROM_SOME_FORMULA" and in the way results have to be displayed.

    Some of the items that often come back in loop a-like functionality : members, currentmember, generate(), ..

    A simple example close to your pseudo code

    WITH 
  MEMBER [Measures].[My measure] AS 
    Sum
    (
      [Product].[Product].[Product].MEMBERS
     ,F(measure tuple member related to product dimension)
    ) 
SELECT 
  [Measures].[My measure] ON 0
FROM [cube];

    or

    WITH 
  MEMBER [Measures].[My measure] AS 
    Sum
    (
      [Product].[Product].[Product].MEMBERS
     ,Int([Measures].[Internet Sales Amount] * 3)
    ) 
SELECT 
  [Measures].[My measure] ON 0
FROM [Adventure Works];

    Philip