Relative date prompt in SAP BW universe

building semantic layer
1

I want to select a measure between two dates. The start date will be the prompt input date minus one month and the end date will be the prompt input date plus 3 months. I am using SQL server.

In the normal universe we will use the dateadd function to derive this. However this does not seem to be working in the OLAP universes. If some has implemented can you please share it with me?

The syntax I tried for end date is as follows:

<FILTER KEY="[Z_CH_332].[LEVEL01]"><CONDITION
OPERATORCONDITION="Less"><CONSTANT CAPTION="dateadd(MM,3,@Prompt('Enter Sales Date','D',,mono,free))"
/></CONDITION></FILTER>

Syntax for start date:

<FILTER KEY="[Z_CH_332].[LEVEL01]"><CONDITION
OPERATORCONDITION="Greater"><CONSTANT CAPTION="dateadd(MM,-1,@Prompt('Enter Sales Date','D',,mono,free))"
/></CONDITION></FILTER>

Any help will be greatly appreciated.

Thanks
Regards
Nathan

nathan_nathan (BOB member since 2008-10-27)

2

I can only recommend that you create this as a calculated key figure in the Bex query instead (using SAP variables, etc.), then base your OLAP universe on that Bex query.

Andreas :de: (BOB member since 2002-06-20)

3

Hi Andreas,

If I dont want to write BEx for this :nonod: , can I do such date calculation in Designer or WebI reports. My requirement is to show the data for last 7 days from selected date from Xcelsius Dashboard.

I am using webi report as source in Xcelsius dashboards with LiveOffice connections.

Any reference for date calculations in SAP BI based universe.

Environment Details:
BOXI R3.1
SAP BI Universe
Xcelsius 2008

sandeep.manocha :us: (BOB member since 2009-06-17)

4

No need to do in BEx…

Create those two conditions in Universe itself.

When you drag them to BO query and run it, it prompt you to enter the dates.

Hope it helps you…!!

Thanks

BOCP (BOB member since 2007-07-02)

5

Hi BOCP,

Thanks for the reply!

My question was how can i do the date calculations on SAP based universe. For e.g. today is 21-July-2009, I want to substract 7 days from this date and want 14-July-2009 as the result. How can I do this in SAP based universe.

sandeep.manocha :us: (BOB member since 2009-06-17)

6

You can do it in Universe level, Search in forum.

Or

Through User Exit from SAP BW.

Create a variable which shows 7 days before data based on user entry/selection.

Thanks

BOCP (BOB member since 2007-07-02)

7

BOCP, I would be curious to know how it it exactly would work in an OLAP universe regarding creating a dynamic date condition in the universe for date ranges (last 3 months starting from a prompted date), etc.
Please, can you be more specific in your answers?

Andreas :de: (BOB member since 2002-06-20)

8

Hello Andreas,

Please have a look at this: Showing quarters dynamically

From that only i came to know…

Thanks

BOCP (BOB member since 2007-07-02)

9

Hi BOPC,

and referred

https://bobj-board.org/t/135061

But my requirement is to do the date calculations on SAP BI cube based universes.

It would be great if someone can put some examples. I have added one example case in my post in same thread

sandeep.manocha :us: (BOB member since 2009-06-17)

10

Well, again this is OLAP universes :!: , the traditional approach you mentioned will not work. Anyway, thanks for your efforts…

Andreas :de: (BOB member since 2002-06-20)

11

Hello,

I suggest create one variable in BEx query which results data for last 7th day based on prompt input.(User entry/selection), You need to write ABAP code to get 7th day.

Eg:

Step-1 ::Place Filter on Fiscal Day in Bex query.(I think you aware), in properties Check ready for Input and UserExit.

Step-2:: Create a key figure which contains data like [rev] or [Amt] etc…, and Fiscal day with that user Exit which gets last 7th day data.

Step-3 :: Use this in BO.

Hope you understood…

Thanks

BOCP (BOB member since 2007-07-02)

12

Thanks for the reply!!

But my requirement is not create any BEx and do this at universe level only.

sandeep.manocha :us: (BOB member since 2009-06-17)

13

after a substantial struggle… the conclusions from my side are: No dimensional calculations can be done from SAP BW cube universes (only measures can be used in calculations). All calculations can be done only at the infocube or the Bex query.

The work around :For each date populate a relative date column. For eg if the date is 23/07 then populate month-1 23/06 on the same record- so you can use this in the select clause. If you want to use it as a filter then populate month +1 ie 23/08. You will use month+1 = @prompt('enter date). So if the user enters 23/08 the data returned will correspond to 23/07.

The OLAP functions can also be used. However it needs a proper hierarchy to be set up in SAP BW. I could do it with limited success, but due to my limited knowledge in sap bw, could not complete it.

I will be happy if someone could provide a better solution than the above suggested.
Hope this helps
Nathan

nathan_nathan (BOB member since 2008-10-27)

14

Hello Nathan,

I told you in my earlier post itself…, To accomplish that you need to write ABAP coding and need to create variables on BEx query with User Exit.

Ask your BW folks to accomplish it.

Thanks

BOCP (BOB member since 2007-07-02)