CODEWITHSUNDEEP
sqlsql-serverssasmdxolap
user3323059
user3323059

Reputation: 25

How to filter set on sum function in calculation mdx?

I want to make calculation in olap cube in ssas. This will be "avg sku by outlets".

There is my expression:

CREATE 
  MEMBER CURRENTCUBE.[Measures].[Avg Prod Art Id Distinct Count By OldcID] AS 
    Avg
    (
      Descendants
      (
        [dimOutlets].[OLDC ID].CurrentMember
       ,[dimOutlets].[OLDC ID].[OLDC ID]
      )
     ,[Measures].[Prod Art Id Distinct Count]
    );

It works fine, but when I tried to add some logic to it and something went wrong. I want to filter some docs by status or type.

This is what I did:

CREATE 
  MEMBER CURRENTCUBE.[Measures].[test Calculated Member] AS 
    Avg
    (
      NonEmpty
      (
        NonEmpty
        (
          {
            [dimInvoice].[Doc Type].&[0]
           ,[dimInvoice].[Doc Type].&[1]
           ,[dimInvoice].[Doc Type].&[2]
           ,[dimInvoice].[Doc Type].&[3]
           ,[dimInvoice].[Doc Type].&[7]
           ,[dimInvoice].[Doc Type].[All].UnknownMember
          }
         ,{
            [dimInvoice].[Status].&[1]
           ,[dimInvoice].[Status].&[2]
           ,[dimInvoice].[Status].&[3]
           ,[dimInvoice].[Status].&[4]
           ,[dimInvoice].[Status].[All].UnknownMember
          }
        )
       ,Descendants
        (
          [dimOutlets].[OLDC ID].CurrentMember
         ,[dimOutlets].[OLDC ID].[OLDC ID]
        )
      )
     ,[Measures].[Prod Art Id Distinct Count]
    ) ;

When I try to test this on browser in visual studio, it thinks for a long time and I do not receive a reply.

Is there a best and fast way to do this?

PS. Sorry about my English.

Upvotes: 0

Views: 973

Answers (2)

user3323059
user3323059

Reputation: 25

This is work (thanks whytheq), but slowly and now i find solution to do this more faster. Wondering whether there is a possibility to do it a measure of the cube?

   CREATE DYNAMIC SET CURRENTCUBE.[DocTypesSet]
     AS nonempty
            (
              {
                [dimInvoice].[Doc Type].&[0]
               ,[dimInvoice].[Doc Type].&[1]
               ,[dimInvoice].[Doc Type].&[2]
               ,[dimInvoice].[Doc Type].&[3]
               ,[dimInvoice].[Doc Type].&[7]
               ,[dimInvoice].[Doc Type].[All].UnknownMember
              }
             *{
                [dimInvoice].[Status].&[1]
               ,[dimInvoice].[Status].&[2]
               ,[dimInvoice].[Status].&[3]
               ,[dimInvoice].[Status].&[4]
               ,[dimInvoice].[Status].[All].UnknownMember
              }
             *Descendants
                (
                  [dimOutlets].[OLDC ID].CurrentMember
                 ,[dimOutlets].[OLDC ID].[OLDC ID]
                )
            ) 

  CREATE MEMBER CURRENTCUBE.[Measures].[test msr]
  AS Sum(DocTypesSet,[Measures].[Prod Art Id Distinct Count] )

Upvotes: 1

whytheq
whytheq

Reputation: 35557

Maybe move the set out into a named set:

CREATE 
  SET CURRENTCUBE.[DocTypes] AS 
    NonEmpty
      (
        NonEmpty
        (
          {
            [dimInvoice].[Doc Type].&[0]
           ,[dimInvoice].[Doc Type].&[1]
           ,[dimInvoice].[Doc Type].&[2]
           ,[dimInvoice].[Doc Type].&[3]
           ,[dimInvoice].[Doc Type].&[7]
           ,[dimInvoice].[Doc Type].[All].UnknownMember
          }
         ,{
            [dimInvoice].[Status].&[1]
           ,[dimInvoice].[Status].&[2]
           ,[dimInvoice].[Status].&[3]
           ,[dimInvoice].[Status].&[4]
           ,[dimInvoice].[Status].[All].UnknownMember
          }
        )
       ,Descendants
        (
          [dimOutlets].[OLDC ID].CurrentMember
         ,[dimOutlets].[OLDC ID].[OLDC ID]
        )
      );


CREATE 
  MEMBER CURRENTCUBE.[Measures].[test Calculated Member] AS 
    Avg
    (
      [DocTypes]
     ,[Measures].[Prod Art Id Distinct Count]
    ) ;

Upvotes: 0

Related Questions