YTD, MTD, Prior Year, Prior Month calculations in SAP BW Universe

[Former Member]

I am finding the SAP BW based universe a totally different animal so far and my question comes from my inexperience in this domain.

In a normal universe, one can use CASE Statement and Calendar or Time table to help do these calculations. Since BW based universes are based on a super BEx query, how are these calculations done in the universe? Are these calculations done in the BEx query itself?

Are there any standards of what should be handled on the BEx side vs. what should be handled on the Universe side?

Also, what's the syntax of a CASE statment or using Substring (or similar function) in a BW universe?

p.s. Moderator, I posted this thread in universe design forum and then later realized that there is a specific forum for integration kits and SAP realted stuff. My apologies for this.

Thanks!

Edited by: Kashif Saeed on Sep 9, 2008 9:23 PM

[IngoH · Accepted solution]

Hi Kashif,

could you perhaps share some more details or a concrete example on what you trying to do ?

I have a suspicion about the answer to all your questions but want to make sure I have the correct understanding

ingo

[Former Member · Accepted solution]

Ingo,

Thanks for your response. Here is more detail for you:

Background:

I have a Sales InfoCube in BI which is at Monthly grain. Business Objects is chosen for reporting on this project and I am using XI3.0. The SAP Integration kit is applied and configured and I can build universes using BEx queries.

Reporting Requirements:

The reporting requirements has calculations for Sales Rev like YTD, FY-YTD, Prior Year, Previous Month, Last Three Months etc.

Question:

1- Should these calculations be handled in the BEx super query or in the universe?

2- If these calculations are done in the universe, what would be the syntax of these calculations?

The reason i am asking the second question is because I believe a universe designer has limited capability when it comes to SAP based universes. For these calculations in a non-SAP universe one would use CASE statement, Calendar table and substring functions.......how would these things be done in an SAP universe?

Thanks again for your response!

Kashif

Edited by: Kashif Saeed on Sep 10, 2008 4:09 PM

[Former Member · Accepted solution]

I've a similar requirement too. We are XI 3.0 with Integration kit installed most of the stuff is working great... but when we get to detailed requirements, it is getting harder.

Can I create a pre-defined filter for something similar to the following in an SAP universe?

Transaction_date = systemdate - 1

Looks like dates are coming in as a String?

Can I do String manipulations in SAP dimensions? Something like Name = "First Name" + "Last Name"

Initial = left(first name)

what are the MDX operations we can do in an SAP Universe? Is there a cheat sheet?

Appreciate the help.

Thanks,

Thiag.

[IngoH · Accepted solution]

Hi,

I will try to answer both set of questions here :

-

---

Reporting Requirements:

The reporting requirements has calculations for Sales Rev like YTD, FY-YTD, Prior Year, Previous Month, Last Three Months etc.

Question:

1- Should these calculations be handled in the BEx super query or in the universe?

2- If these calculations are done in the universe, what would be the syntax of these calculations?

-

---

ideally those items would be created in the query already because then you can share them across multiple universes. You can create custom calculations in the universe but you would have to create them in each universe.

-

---

Can I create a pre-defined filter for something similar to the following in an SAP universe?

-

---

Yes that is possible.

Those kind of items are explained in the Universe Designer / OLAP Universe Designer guide for the XIr3 release on help.sap.com

Ingo

[Former Member · Accepted solution]

Thanks for your reply Ingo. Couple of follow-up questions for you:

1. If all of these calculations are done in the query, then it will be tough to consolidate the quries into a Super Bex query as every report has different calculations. Any thought on this?

Also, this would mean heavy dependency on SAP BI developers and to me that defeats the purpose of using Business Objects.

2. The kind of calculations the OLAP designer guide discusses are simple x * y calculations. There are no CASE statement or substring or other examples. Can you please share how these are done in BW based universe?

Thanks,

Kashif

[IngoH · Accepted solution]

Hi,

that is correct and in the real sceanrio one single gigantic query will not be enough. You will have a small amount of queries being used for a larger set of reports.

on the calculations : you can use MDX syntax in the calculation. its not about using formula language.

ingo

[Former Member · Accepted solution]

Ingo,

Do you have any samples on dates, strings & other calculations possible using MDX syntax?

Is there any documentation on MDX syntax we can use... I did some research on that, apparently SAP has different implementation than the syntax Microsoft has published..

appreciate your input in this regard.

Thanks,

Thiag.

[Former Member · Accepted solution]

Thaig,

I have done some research on this as well but no luck. It would be great if we can get some samples or even a document that discusses it.

Ingo - any help on this will be really appreciated.

Thanks,

Kashif

[Former Member · Accepted solution]

Yes a document or some guidance would be greatly appreciated.

I have tried to put the syntax in the select clause and it always fails as invalid xml. Do we have to have any specific transports installed for this to work?

Thanks

[IngoH · Accepted solution]

Hi,

you can find and example in the Universe Designer XIr3 documentation in the chapter for OLAP Universe / Customizing OLAP Universe.

ingo

[Former Member · Accepted solution]

Hi Kashif,

I published a document on SAP site that five you samples of MDX expressions as well as recommendations and hints and tips when using calculted measures and filters in OLAP universes.

Here is the link: https://boc.sdn.sap.com/node/20081

Didier

[Former Member · Accepted solution]

Thanks a bunch for the document Didier!

[Former Member · Accepted solution]

Great Document! Thanks!

[Former Member · Accepted solution]

Didier,

Quick question... I was able to get Previous Month Cost using the format you specified, but when i try to perform CurrMonth Cost - Previous Months Cost, I get an error.

Cost: [Measures].[4B9CL71EYXTY20Z966ZW1S2ES]

Prev Month Cost: 

<EXPRESSION>(@Select(Key Figures\Cost),[0FISCPER].PrevMember)</EXPRESSION>

Dollar Change: 
<EXPRESSION> @Select(Key Figures\Cost) - @Select(Key Figures\Prev Month Cost) </EXPRESSION>

The Dollar Change object errors out in the report. Is this normal?

Thanks,

Kashif

[former_member539471 · Accepted solution]

Please diader i am unable to open this doc, says file not found

help greatly appreciated

[former_member539471 · Accepted solution]

diader,

i am unable to open this doc, it says file not found.

pls help

[Henry_Banks · Accepted solution]

Didier's document has moved to here: https://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/c0a45246-ce76-2b10-e688-f5c8206203eb&overrid...

Hey, SP2 is out now for XI3 - it now has an MDX dictionary in Universe layer. (for advanced condition building)

regards,

Henry

[former_member193430 · Accepted solution]

I am having the same issue and trying to find a solution, If some one wants to share there knowledge on that. Thank you. Toor.

Edited by: Ali Toor Muhammad on May 13, 2010 6:00 PM

[former_member193430 · Accepted solution]

Hi Kashif, would you like to share informatin, if your issue was resolved and how would be the syntax to calculate the Fiscal year and ytd. Thank you.