Question:
Hi All
First I searched the forum , but could not get the solution for my problem . What I have to achieve in Bex query is as follows :-
I have got sales data which I got Monthwise (0CALMONTH) , Now I want to display revenue key figures as -
I am entering value of variable for 0CALMONTH as May 2003 , my query should give me revenue values for May 2003 as well as for all months from Jan 2003 ( current year ) till May 2003 like below :-
Jan 03 Feb 03 Mar 03 April 03 May 03
23 30 35 45 56
So in short I want to have revenue values for all months till the month which I put as value of OCALMONTH variable.
But I will not be able to make use of the offset as 0CALMONTH-1, 0CALMONTH-2....etc since the value of variable for 0CALMONTH is not fixed. Like If I am entering month of Jul 2003 the query should show revenue figures from Jan 03 , Feb 03 , Mar 03 , Apr 03 , May 03 , Jun 03, Jul 03.
Regards
BW_CONS
Answer:
You will need to create a customer exit variable for this. There is a great How-To paper on service.sap.com. It is called How to derive a variable value from another variable. Have fun.
thanks,
jlaruss
Answer:
Hi jlaruss1
If I don't want to use customer exit for variables then is there any work around for this ?
BW_CONS
Answer:
Follow these steps to solve your problem:
1- In the char 0calmonth, restrict it with variable for single value (this one should come with business content.
2- Still in the restriction screen, make SELECTION = 'value range' and 'Between'
3- flip to the Fixed values tab, and drag Jan 2003 over to the right side and drop it on the variable you have selected
4- the restriction should now read jan 2003 - variable name
since 0calmonth is compounded with year, you must change the fixed value if you wanted to report in a new year i.e. 01.2004.
This works better if you use posting period.
good luck
Answer:
Hi Guys
Your suggestions are fine . But what BWinCanada suggested is value range for calmonth from Jan 03 to Jan 04 . I don't want value range. What I want is if I am entering variable value for 0CALMONTH as Mar 03 , the query should give me revenue values for months of
Jan 03
Feb 03
Mar 03
Like these three distinct columns ie monthwise revenue values till Mar 03 ?(User entry ) .
Suppose if I enter Jun 03 it should give me revenue values for 6 months seperately monthwise
Jan 03
Feb 03
Mar 03
Apr 03
May 03
Jun 03
thanks
BW_CONS
Answer:
Please read point 1 carefully. Use a SINGLE value variable here. It should only request user to enter one month/year value as you specified in your question. The restriction then fills in the previous months automatically.
Answer:
So you need this to build your columns dynamically based upon the date range? Is this correct. What about already having all of the columns built and then only the numbers show up in the relevant columns? This can be done with RKF and restrict each column on the month only. One way to dynamically build the columns is the use the 0calmonth infoObject in the columns and based upon your data, it will build your columns.
Thanks,
jlaruss
Answer:
Other (easy!) ways:
1:
Use Fiscal Year and Posting Period (two different time characteristics).
Put Fiscal Year in the Filter section, and restrict it with a single value variable.
Put Posting Period in the Columns section, and restrict it with a single value variable, using Value Range <= the variable value.
The user will be asked for the Fiscal Year and for the Ending month in that year. The output will give a column for each month up to the user-specified month.
2:
Use 0CALMONTH as you've specified.
Put this characteristic in the Columns section, and restrict it based on an Interval variable. The user will then have to specify both the beginning and end months to include in report. The output will give a column for each month in the range specified by the user.
Answer:
Do you not have access to the Current Calender Month variable? If you are using 0CALMONTH, then you should and you can do your variable off-set for however many months back you want to go.
- Stephen
Answer:
May I break in ?
I strongly suggest BWCONS to reread the posting of BWinCanada, as this is the easiest solution to his problem, if you except the problem about "in a new year i.e. 01.2004".
If this is really a problem, then you can bypass it using the user-exit (ther you calculate what is the start month of the interval based on date entered). Of course, if you -as you state- don't want to use the tools that are there to help you, I think you'd better close the topic.
The suggestion 2. of the guest about 0calmonth is also an easy one, while I understand you don't want your user to have to enter the start of the period manually.
Ch
_________________
_
There are only 10 types of people in the world :
those who understand binary and those who don't.