Functions and formulas

[Former Member]

Hello, I have an object that contains month and year, like this 01.2010 what I need is to create a new object that looks into this one and extracts year, can I do it in the universe?

I tried with this one but it gives an error when checking formula

subset(year_month,3,4)

Thanks in advance

[Former Member · Accepted solution]

Hi, when posting syntax questions it is best to also post what database you are connecting to as the syntax may change from one to another. I suspect what you are looking for is the "substring" function which may be either substring() or substr() depending on your database. The syntax you have looks fine, you just need the proper database function.

[Former Member · Accepted solution]

Sorry, I am using BW (Bex Query designer) as a source, I can't find substring() or substr() in the formulas area.

You mean by your comment that I can use the formulas of the source?

If is MsSQLServer I can use the ones from this one?

If is BW I can use the ones from this one?

etc.

Thank you

[Former Member · Accepted solution]

Ah, BEx, yes, that's a different situation.

To answer the question, yes, you can use any functions from the target system. So if your universe is pointing to an Oracle database you can use Decode() and NVL() and other Oracle-specific functions. If you're pointing to MS SQL Server you can use charindex() and patindex() and other SQL Server functions. But with BEx I believe you have to use the MDX language, so whatever functions you have there should work. I did a quick search, and it seems that MDX offers a Mid() function that performs the substring purpose. But I don't know if that works against BEx or not.

[Former Member · Accepted solution]

I see...

Thanks for your help but didn't work MID() is not known.

[Former Member · Accepted solution]

Hi, I am not a BEx wizard, or even an apprentice. I have done some searching via Google and found this topic which has some links. At this point I think I am at the end of what I can offer for help in this area.

[Former Member · Accepted solution]

Hi,

SAP MDX expressions only allow numeric values ..

By the way you can return string values only with NAME or UNIQUENAME characteristic attributes.

So here are examples with 0CALMONTH characteristic you can use:

<EXPRESSION>RIGHT([0CALMONTH].currentmember.name,4)</EXPRESSION>

<EXPRESSION>RIGHT([0CALMONTH].currentmember.uniquename,4)</EXPRESSION>

Regards

Didier