How to aggregate Dimension values properly

• ktflash

  Default port

  Points: 1400

  More actions

  December 2, 2020 at 9:36 am

  #3815961

  Ahoi,

  im trying to deal with a user request to combine 2 values of a column based on values of a different attribut.

  I managed to make it work like this, creating 2 hidden memembers and combining those in my cube script.

  --Hidden 1
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Webshop]
  AS ([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop]), 
  VISIBLE = 0;
  
  --Hidden 2
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop YTI]
  AS ([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[YTI]), 
  VISIBLE = 0;
  
  --aggregating Hidden 1 and Hidden 2
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Total] AS
  [Order].[Ordertype].[Onlineshop YTI]+
  [Order].[Ordertype].[Onlineshop Webshop],
  VISIBLE = 1;  
  

  Surely there must be a better way to write it using sets,tupples or something, i have been trying alot but i could not manage to make it work.

  I am looking for a solution like the follwing to make the same thing above work, here are my failed attempts to show you what sort of solution im trying to create, these dont work:

  --(()())
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Onlineshop Total]
  AS (([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[YTI])
     ,([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop])), 
  VISIBLE = 1;  
  
  --({(),()})
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Onlineshop Total]
  AS ({([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[YTI])
     ,([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop])}), 
  VISIBLE = 1;  
  
  --{(()())}
  CREATE MEMBER CURRENTCUBE.[Order].[Ordertype].[Onlineshop Onlineshop Total]
  AS {(([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[YTI])
     ,([Order].[Ordertype].[All],[Order].[Belegart MDX Ohne Mandant].&[Webshop]))}, 
  VISIBLE = 1;

  I am really struggeling to work with MDX as you can see and i am trying to get better at it but i am failing very hard. I would appreciate if someone could enlighten me.

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  December 3, 2020 at 10:10 am

  #3816415

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.