Simple formula not working in BO4.0 SP5 but works with SP2!?

[former_member327010]

Hi all,

we are running two BO systems - development and productive. Both systems were BO4.0 SP2 and everything was ok. We imported SP5 to our development system and the "fun" started. Reports which work on producitve with SP2, arent working on development with SP5. We made few modifications and 90% of the reports are working execpt one (but i still hate to modificate almost every report after we apply a new SP). This report is based on BEx query and contains simple formula which goes like this: =[Revenue] where ([Calendar day]=[Last day])

Revenue and calendar day are standard measures and dimensions. "Last day" is a variable =ToDate("31.12.2010";"dd.MM.yyyy")

It gives me #unavailable. On productive system is everything ok.

What is wrong with this formula? Any changes in SP5 ??

Any ideas?

Thank you,

regards Martin Zluky

[former_member327010 · Accepted solution]

Hi all,

looks like i found the solution. It was a smart measure issue.

It is explained here: http://scn.sap.com/thread/2070659

Funny thing is that it is a thread started by me

I created a new measure variable Revenue2=[Revenue] In ([Calendar Day])  and replaced the standard measure "Revenue" in my formula. And it works pretty good.

Regards,

Martin Zluky

[VinaySathya]

Can you try inserting a single blank cell and try the same formula (=ToDate("31.12.2010";"dd.MM.yyyy")).

As I know this should work fine.

Regards,

-Vinay

[former_member327010]

Hi Vinay,

the variable i created is working i guess, it shows that it is "Date and Time" type and when i put it into report it gives me the correct date. Also the formula in the blank cell is working. But when i expand the formula to [Revenue] where ([Calendar day]=ToDate("31.12.2010";"dd.MM.yyyy")) it gives me #unavailable.

Meanwhile i have found a sloution, looks it works, and it goes like this:

=[Revenue] where ([Last day]=[Last day])

EDIT: Its not working, completly wrong

Still fighting with this issue.

Regards,

Martin Zluky

-----------------------------

Message was edited by: Martin Zluky

[Former Member]

Hi Martin,

Please make sure that [Calendar day] has the date format "dd.MM.yyyy"

Otherwise try with FormatDate([Calendar day];"dd.MM.yyyy")

Hope this will help.

Kind Regards,

Rabeb

[former_member327010]

Hi Rabeb,

i tried it but its not working for me

Regards,

Martin Zluky

[erickalberto_romeroneyra]

Hi Colleagues

I had a similar situation with Webi 4.0 + BICS and solved in the following way:

Define each formula as a variable and set the following two things (IMPORTANT):

• Qualification as "Detail"
• Associated dimension: <select one with the same level> (i.e [Calendar Day] for "last day" variable) (for Revenue check if you can associate it to the [revenue ] measure.

Reason is because smart measure functionality with BICS detects a new object (formula) and assume this as new aggregation level and then delegate  the calculation to BW side, but the formula doesn't exists in BW. Webi doesn´t recognize automatically the formula or variable as part of another dimension or as a "detailed" object type, so you need to specify this as I mentioned before.

Hope this helps. Try and let me know

Best Regards

Erick

[former_member327010]

Hi Erick,

it didnt work for me but maybe i did something wrong. But i found a solution described in my previous post.

Regards,

martin zluky

[former_member188911]

Hi Martin,

often the SP levels introduce changes in behavior in the calculation engine, that's why before doing an upgrade it is better to do throrough testing, and adjust where necessary, as you are doing,

As far as this formula is concerned, I suggest to test in Patch 5.2 and if the case open a CSS message providing reproducing steps and database version/middleware

Thanks

SC