Skip to Content
avatar image
Former Member

MDX query question

i have very little SAP experience and am hoping that someone can help me with what i hope is a trivial problem.

i want to build and compare reports off a BW cube using 2 methods - BEx and XMLA.

i am able to run a bex-analyzer query against a cube to obtain reports on key-figures against chareteristics . i am also able to create reports of key figures against an attribute of a characteristic e.g. 0MAT_PLANT is the characteristic and the report was run on the 0PROFIT_CTR attribute of 0MAT_PLANT. (trivial enough).

i now want to obtain the results using the equivalent MDX query, my ultimate goal is to have the query submitted via XMLA to obtain results. i am trying the query out in the MDXTEST first. i am able to refer to a charecteristic of the cube in the MDX and obtain results e.g. material-plant.

e.g.

SELECT {

[Measures].[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT].MEMBERS}ON ROWS

FROM [<my-cube>]

but i am unable to reference the profit-center attribute in the MDX query. i am guessing that i am running into a syntax issue. i have tried to refer to the attribute as [0MAT_PLANT__0PROFIT_CTR], [0MAT_PLANT].[0PROFIT_CTR]

but the MDXTEST always errors out.

how can i refer to the profit_center or any attribute in MDX?

i hope that i have got all the SAP terminology straight.

thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Jul 01, 2004 at 04:55 AM

    Hello JT,

    the attributes are equivalent to MDX properties (of a member). You can find the mapping in the online help at http://help.sap.com/saphelp_bw33/helpdata/en/9b/c1993c54966f3ae10000000a114084/frameset.htm.

    In MDXTEST select your InfoCube from the dropdown box (not a query cube). Then drill down in the available object frame to characteristic 0MAT_PLANT. Under "Properties" you can find the attributes. There's a naming convention for the key value and the various texts. I think you are looking for [0MAT_PLANT].[20PROFIT_CTR]

    Regards,

    Marc

    SAP NetWeaver RIG, US BI

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      hello marc,

      thank you for the reply. i tried the following in the MDXTEST editor

      SELECT { [Measures.[0ACT_DL_QTY] } ON COLUMNS,

      { [0MAT_PLANT].[20PROFIT_CTR] } ON ROWS

      FROM [<my-cube>]

      i got an "invalid MDX command with <internal>" error.

      anything else that i can try?

      jt.

  • avatar image
    Former Member
    Jul 01, 2004 at 06:23 AM

    Hi,

    as far as I know, navigational attributes are not available directly on the InfoProvider. You need to create a query in the Query-Designer with all relevant characteristics key figures and navigational attributes in order to use them. Release the Query for OLAP (for example your test query you used in BEX).

    Of course you can use display attributes. But as far as I understood your question, you want to aggregate on the attribute.

    As Marc already stated, you should have a look on ta mdxtest.

    The correct syntax should be

    SELECT {

    [Measures].[0ACT_DL_QTY] } ON COLUMNS,

    { [0MAT_PLANT__0PROFIT_CTR].MEMBERS}ON ROWS

    FROM [<my-cube>]

    Heike

    Add comment
    10|10000 characters needed characters exceeded