none
MDX Query not working while browsing the cube

    General discussion

  • Hi All,

    i have one mdx  query below:-

    sum

    ([Transaction].[Transaction Type Code].members ,

    {

    iif ([Transaction].[Transaction Type Code].CURRENTMEMBER

    IS [Transaction].[Transaction Type Code].&[35],[Measures].[Written Premium],null) , iif ([Transaction].[Transaction Type Code].CURRENTMEMBER

    IS [Transaction].[Transaction Type Code].&[40], [Measures].[Written Premium],NULL),iif ([Transaction].[Transaction Type Code].CURRENTMEMBER

    IS [Transaction].[Transaction Type Code].&[45], [Measures].[Written Premium],NULL),iif ([Transaction].[Transaction Type Code].CURRENTMEMBER

    IS [Transaction].[Transaction Type Code].&[CACA], [Measures].[Written Premium],NULL),iif ([Transaction].[Transaction Type Code].CURRENTMEMBER

    IS [Transaction].[Transaction Type Code].&[CARS], [Measures].[Written Premium],NULL)

    }.

    item(0))      when i fire this query on my mdx query window it is giving me the correct result but when i create a calculated measure from this query and when i deploy and process the cube . i am getting below error:- the level '& [CACA]' object was not found in the cube when the string , [Transactio].[TransactioTypeCode].[CACA] was parsed. I know that the CACA & CARS are not present in my database but i have to include the CACA and CARS i n my code because in future i'll get the data  for these two also. so the above code shouls work includin CACA & CARS after processing the cube.


    Can anybody help me out?
    • Edited by Surbhi128 Monday, September 17, 2012 6:13 AM
    Monday, September 17, 2012 6:12 AM

All replies

  • this if-ing is kinda ugly. i would add an attribute to the [Transaction] dimension called "show premium", values Yes and No. you can get a list of such members with

    existing([Transaction].[Transaction Type Code].members,  [Transaction].[Show Premium].[Yes])

    and you can test a certain member various ways, like for example

    generate([Transaction].[Transaction Type Code].currentmember, [Transaction].[Show Premium].currentmember) is [Transaction].[Show Premium].[Yes]

    but may there are less obscure methods.

    Monday, September 17, 2012 8:57 AM
  • Hi Krisztian,

    Its a production database , so not possible to add any attribute in the transaction dimension . is it possible to solve it only through MDX.

    Monday, September 17, 2012 9:23 AM
  • then you could use names:

    [Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename = "[Transaction].[Transaction Type Code].&[CACA]"

    Monday, September 17, 2012 9:43 AM
  • Hi Krisztian,

    I have modified the code like below as you suggested=

    sum

    ([Transaction].[Transaction Type Code].members ,

    {

    iif ([Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    = "[Transaction].[Transaction Type Code].&[35]",[Measures].[Written Premium],

    null)

    ,

    iif ([Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    ="[Transaction].[Transaction Type Code].&[40]", [Measures].[Written Premium],

    NULL),

    iif ([Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    =" [Transaction].[Transaction Type Code].&[45]", [Measures].[Written Premium],

    NULL),

    iif

    ([Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    = "[Transaction].[Transaction Type Code].&[CACA]", [Measures].[Written Premium],

    NULL)

    ,

    iif ([Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    = "[Transaction].[Transaction Type Code].&[CARS]", [Measures].[Written Premium],

    NULL)

    }.

    item(0))   but it is giving me sum of written premium of the transaction code 35  only and  its not giving the sum of  all the written premium mentioned in the code.

    Monday, September 17, 2012 11:22 AM
  • probably the unique names are different for them. usually unique names contain levels and/or path. 

    you can use .currentmember.name = "CARS" or .currentmember.properties("KEY")="CARS" instead.

    or check the unique names with selecting the members, and examining the resulting cellset. like

    with member [nm] as [Transaction].[Transaction Type Code].CURRENTMEMBER.uniquename

    select [nm] on 0, [Transaction].[Transaction Type Code].members on 1

    from ...

    Monday, September 17, 2012 11:38 AM
  • Thanks a Bunch Krisztian ................ Your solution works for me............
    Monday, September 17, 2012 12:23 PM