How to use string functions (substr or ltrim or replace) in OLAP universe.

[Former Member]

cost element (0COSTELMNT) - 10 CHAR

Controlling area (0CO_AREA) - 4 CHAR

[0COSTELMNT].[LEVEL01].[[20COSTELMNT]].[Value]

cOST ELEMENT is compounded/prefixed with Controlling Area. I just want to see cost element without conrolling area in the BO report.

Currenlty BO unierse is build based on bex query. I am able to suppress the compounding object in bex query by chaning controlling area to 'No display'. But still BO Webi report displaying compounded values in the report. (Bex report works as expected)

eg: Current display in reort.

controlling area/cost element.

AB00/2222

AB00/2223

AB00/2224

Wanted like below:

2222

2223

2224

I think by using string fucntions (substring, ltrim or replace etc.), I can get the required result. But I am having issues with syntax. I have used like below.

substr(0COSTELMNT ; 5 ; 10)

substr(0COSTELMNT; 5 ; Length(0COSTELMNT)-5)

substr(0COSTELMNT; Pos(0COSTELMNT;"/")+1;10)

ltrim(0COSTELMNT,'AB00/')

What is the syntax for substring/replace functions in OLAP universe. Technical name of cost element in OLAP universe is [0COSTELMNT].[LEVEL01].[[20COSTELMNT]].[Value].

I want to fix this at universe level not at report level as i am using cost element in filter/variable section of the report. Please provide me syntax for above example.

[Former Member]

As you need them at Universe level, so better try to use substring functions available at datasource/back-end level.

@Report you can easily achieve this(but you don't want it)

Gracias...!!

[former_member142702]

SAP BW MDX does not support string processing operators. That's also the reason why it cannot be done in the universe.

Regards,

Stratos

[Former Member]

I mean, do the String manipulations at Datasource or Back-end level, not @Universe.

Hope you got what I talked about...!!

Gracias...!!

[former_member142702]

Well I am not really sure that the option to avoid using compound characteristics is a real alternative here.

Regards,

Stratos

[Former Member]

Hi,

In fact SAP BW MDX supports limited string manipulation and only with NAME and UNIQUENAME attributes.

Here are some samples that you can use in universes:

MID([0COSTELMNT].currentmember.NAME,1,4)
LEFT([0COSTELMNT].currentmember.NAME,2)
RIGHT([0COSTELMNT].currentmember.NAME,3)
MID([0COSTELMNT].currentmember.UNIQUENAME ,1,4)
LEFT([0COSTELMNT].currentmember.UNIQUENAME ,2)
RIGHT([0COSTELMNT].currentmember.UNIQUENAME ,3)

Didier

[former_member142702]

Thanks Didier,

that's really valuable info. Is a minimum patch level required on the BW side for this?

Regards,

Stratos

[Former Member]

Hi,

I assume Yes but I don't know which patche is needed.

I encourage you to read SAP notes related to MDX and string functions.

Didier

[Former Member]

Thanks for the useful information, do you know if functions like LPAD are available?

I mean I read notes 1147344, 1087077, 963284

Edited by: Pabloj on Oct 26, 2010 9:35 AM

[Former Member]

Unfortunately LPAD is not supported.

Didier

[former_member142702]

String functions are currently not supported in SAP BW universes.

Regards,

Stratos