How to create a variable in Bex Query Designer

[Former Member]

Hi guys,

I am writing a query for a travel management report using the SAP Standard cube. I would like to restrict the query so it only shows me the last two months excluding the current month. I have checked BI content in RSA1, but I canu2019t find a relevant SAP standard variable that I can use.

Can anyone please tell me if I can create my own? And if so, how I can do this.

Many Thanks

Forhad.

[Former Member · Accepted solution]

lets say u want to display following picture:

-

---

sept$-----august$

City

-chicago123$--

---

44$

so in Query designer go to Default values section.

Bring in Calendar month

right click calendar month

and hit Restrict

now select variable

use standard sap variable Current Month.

hit okey

now select Variable and hit Red button

Result u restricted current month.

in columns bring in keyfigure $

right click and hit edit

bring in time field calendar month

right click and hit restrict

bring in Current month

right click and select - SET OFFSET FOR VARIABLE

select -1

hit okey

repeat the above process

and this time select offset = -2

this way u restricted current month

but displayed last 2 months for keyfigure values.

[Former Member]

thanks for your help

Forhad

[Former Member]

Thanks Raj,

but is this solution for 3.x version, as we are on version 7.0.

becasue when i right click the KF i do not get the specify offset option.

[Former Member]

Hi,

In 7.0, go to the default values/filter pane.

there right click on the characteristic and choose variable and on the right you will see a button "set offset for variable".

[Former Member]

Raj,

how do i do this, is there any worksteps i can follow.

Thanks

[Former Member]

Right click the KF --> Specify Offset ---> Offset = -1, -2.

[Former Member]

I have tried to create a new variable, but the problem is I donu2019t know how to set the default values setting so that it restricts by the last two months excluding the current month. I was wondering if this requires some kind of coding?

[Former Member]

yes you can create your own variable. right-click on the characteristic and choose restrict. there you should be able to create a variable.

http://help.sap.com/saphelp_nw70/helpdata/en/43/0c406b324f6e95e10000000a11466f/content.htm

[Former Member]

no. all you need to use is an offset for the variable to bring the values of the previous two months.