MDX always returns Description

[Former Member]

Hi,

I am using MDX statment to retrieve result. The statement is as follows:

SELECT NON EMPTY {[3A7PQ1PWB93XPLS4ENBRGNUGR].[75FX3HW29XHBF3ZD279PGG5UR]} ON COLUMNS,

NON EMPTY

{[0MATERIAL Z00KSTDHIERARCHY].LEVELS(01).MEMBERS} ON ROWS FROM [Z_PYMNTS/ZZZ_MDXTEST_QUERY] SAP VARIABLES [ZC_YRWK] INCLUDING [0CALWEEK].[200401] [ZC_CC] INCLUDING [ZC01CNTRF].[X]

The result always contains description of Hierarchy nodes. I want the result to show node value of hierarchy . How should we adjust the .MEMBERS Part above.

The attributes of 0material in the query is Key.

Best Regards,

Anil

[Former Member · Accepted solution]

Hi,

please add the properties:

SELECT NON EMPTY {[3A7PQ1PWB93XPLS4ENBRGNUGR].[75FX3HW29XHBF3ZD279PGG5UR]} ON COLUMNS,

NON EMPTY

{[0MATERIAL Z00KSTDHIERARCHY].LEVELS(01).MEMBERS}

properties

[0MATERIAL].[20MATERIAL]

ON AXIS(1) FROM [Z_PYMNTS/ZZZ_MDXTEST_QUERY] SAP VARIABLES [ZC_YRWK] INCLUDING [0CALWEEK].[200401] [ZC_CC] INCLUDING [ZC01CNTRF].[X]

kind reg.

Michael

[Former Member · Accepted solution]

Hi Michael,

The hierarchy contains some other char(external chars) or text nodes in level one.

I tried the option you gave..it still gives the same result.

Somehow I have to say that the techncial code of hierarchy should be shown(materials are in level-2 onwards in hierarchy).

If I run the same report in RSCRMBAPI, I get code space description. It does not come when I write the MDX statement manually.

Best Regards,

Anil

[Former Member · Accepted solution]

Hi Anil,

could you please try to use the flat function in transaction MDXTEST and

properties

member_name

instead of

properties

[0MATERIAL].[20MATERIAL]

Kind regards

Michael

[Former Member · Accepted solution]

HI Michael,

But my requirement is to retrieve show hierarchy node - Hierarchy node description, keyfigure Amount.

At material level the data will be huge and none of our users are interested in materal data. They are interested data at one level of hierarchy(ofcourse I do some churning on this data ).

I do not understand how bapi_crm is doing this. I even saw the MDX generated by BAPI_CRM and pasted in MDXTEST..The result in MDX test is always same...description not the node code.

Best Regards,

Anil

[Former Member · Accepted solution]

Hello Anil,

if I add

properties

member_name

in the MDX and I execute the MDX statement in the MDXTEST transaction (Run Query Multidim.), then the system gives me the result with the description and the node key...

like

MDX:

SELECT

[Measures].MEMBERS ON AXIS(0) ,

NON EMPTY

[0REGION Z_LW_REGION].[LEVEL00].Members

properties

member_name

ON AXIS(1)

FROM [FABCUBE5/Z305TESTEXTR1]

RESULT:

WELT |WELT 0HIER_NODE |77.408,00 EUR

Not Assigned Region (s) |REST_H 1HIER_REST |81.293,00 EUR

The first column is the description and the second is the key of the node.

If I do not want to have the description I have to remove the return parameter of the BAPI: BAPI_MDDATASET_GET_AXIS_DATA --> l_s_mndtry_prptys-mem_cap

In the returnparameter l_t_option_prptys_vals (l_s_opv-PRPTY_VAL) is the key of the node.

The MDXTEST Transaction is just an example how the BAPI's are working and to test the MDX. The coding of the CRM Bapi and the MDXTEST transaction are using the same BAPI's, but the not the same RETURN parameters...

You can test this, If you do a breakpoint in Program: RRT_MDX_SAMPLE Source Code of RRTMDXF02 line 272 at CALL FUNCTION 'BAPI_MDDATASET_GET_AXIS_DATA'

I hope this helps?

Kind reg.

Michael

[Former Member · Accepted solution]

Hi Michael,

Thank you for your help.

I do not understand what value I should give for member_name .I tried giving [20VENDOR] as the hierarchy is for supplier..anyway I understood from your message that it works fine in BAPI but not in MDXTEST.

Now I will work on the BAPI and get back to you .

What did you put for member_name in your MDX statement

Best Regards,

Anil

[Former Member · Accepted solution]

Hello Anil,

please try to put in just "member_name" itself nothing else! This works then for all your characteristics!

As I mentioned in the MDX.

Kind regards

Michael

[Former Member · Accepted solution]

I have the follwoing MDX which returns the data values for 0SALESORG,how do I get the values and not texts for 0DISTR_CHAN and 0MATERAIL as well

SELECT

[Measures].MEMBERS ON AXIS(0) ,

NON EMPTY [0SALESORG].[LEVEL01].MEMBERS

• [0DISTR_CHAN].[LEVEL01].MEMBERS

• [0MATERIAL].[LEVEL01].MEMBERS

properties[0SALESORG].[20SALESORG]

ON AXIS(1)

FROM [EPSDC108/ZTD_EPSDC108_Q002]

[Former Member · Accepted solution]

could you please open a new message and post it into the forum!

thanks and reg.

michael