MDX Concat Syntax Error

[Former Member]

We have an SAP BW7 BEX Query with 2 characteristics which we want to concatenate together in a new Universe Dimension.

From searching around the syntax should be :

<EXPRESSION>@Select(Financial Item\L03 Financial Item)||@Select(Financial Item\L00 Financial Item GFRM Item Long Text (Key))</EXPRESSION>

Parses OK

Also tried :

<EXPRESSION>[ZS_ITEM 02H2].[LEVEL03].[[2ZS_SBMITM]].[Value]||[ZS_ITEM 02H2].[LEVEL03]</EXPRESSION>

Again Parses OK

When we add this dimension to a Webi report the following MDX error is presented.

Query 1 - Demo Universe - BCS Subset - SubConsolidated BS Report 2

A database error occured.

The database error text is:

The MDX query WITH MEMBER [Measures].[9DB8D426-17C4-4BDB-95,22,9C,9D,3,CF,25,53] AS ' [ZS_ITEM 02H2].[LEVEL03][ZS_ITEM 02H2].[LEVEL03] ' SELECT { [Measures].[9DB8D426-17C4-4BDB-95,22,9C,9D,3,CF,25,53] } ON COLUMNS , NON EMPTY {[ZCONSUNIT].DEFAULTMEMBER} ON ROWS FROM [ZMVBCS_1/BO_FMA_BCS_001_GR_SUBC_BS] SAP VARIABLES [!V000005] INCLUDING [0CS_PLEVEL].[00] : [0CS_PLEVEL].[30] [!V000001] INCLUDING [ZCONSUNIT].[4068] [!V000006] INCLUDING [ZLCGC].[LC] [!V000004] INCLUDING [0FISCPER3].[004] : [0FISCPER3].[004] [!V000003] INCLUDING [0FISCYEAR].[K42008] failed to execute with the error Unknown error. (WIS 10901)

Anyone have any ideas?

Thanks,

Steve

[Former Member · Accepted solution]

These are 2 characteristics which we simply want to concatenate.

We can't do this in the BEX query as it's a hierarchy level (one object being the key and the other the text)

I see from Note 1147344 that string manipulation has been added so a little confused by the responses which suggest that only a numeric value can be returned.

Hope someone can provide us with a solution to what should be a very very simple requirement.

Thanks,

Steve

[Former Member · Accepted solution]

Hi,

You are right, you can manipulate string in an MDX expression but the result MUST be numeric.

For instance you can do that:

IIF([0CALMONTH].CurrentMember = "APR 09", 1, 0)

Didier

[Former Member]

Hi,

@Select is supported in calculated measures as well as a standard expression.

By the way, you have to take into account that an MDX expression in SAP must only return numeric values: string and date are not supported.

I assume you hit this issue.

Last, the parsing tool only parses XML syntax and Designer functions such as @Select and @Prompt: no MDX parsing.

Didier

[IngoH]

Hi Steve,

what are the items that you trying to concatenate here ? are those measures ?

ingo

[Former Member]

Despite the successful parse, I do not believe @Select works with anything beyond Key Figures. And even with Key Figures you can only do very simple statements without MDX errors occurring on refresh...again despite the OK parsing. The best bet is to do everything you can at the BEx Query level and don't expect too much on Universe customization.

The forums and BOB message boards are loaded with WIS 10901 errors on refresh of reports using customized objects in the Universe. Unfortunately, best practice guides and other documentation provide only limited guidance whether it be account authorizations or comlex MDX syntax or something else unknown. A more comprehensive guide on MDX syntax modifications and complex scenarios from real world customer scenarios would be ideal. Being that there is only so much you can do at the Universe level.

So, for time being we really guide customers if an object is created at the UNV level off of BEx and it does not meet reporting requirements...make the change at the BEx level. This is not always ideal, but you will have less headaches.