0FISCPER - dynamic 12 months

[Former Member]

note to remeber i do not have posting period or calmonth in the cube.

If the user enters 0FISCPER how to get all the 12 months in the query and Sum of 12 months dynamically

example - user enters 11/2007 ,it should populate 01/2007 to 12/2007 12 columns in a query and then SUM of 12 columns in query.

WHat ar the ways of achieving it with Bex and with user exits??

thanks

[Former Member · Accepted solution]

Milt,

You can do this using user exit.

One exit to populate data for month and use offsets for the remaining months in separate columns.

Another one to get aggregated data from first month till 12th month. Again without this variable you can simple addup all the months data in a new formula.

And if you create a formula varibale which populates 01/2007 till 12/2007 use it in the columns and then put the column structure, the overall result will be the sum of all the months.

Checkout which way you want to do.

[Former Member · Accepted solution]

Please note user enters only 0FISCPER,so now how do i proceed from here.Please also give me steps to create a formula variable

How to get the format in this way using he approach as mentioned above

1 2 3 4 5 6 7 8 9 10 11 12 YTD

YTD should not be repeated for every months..its should be shown only once at the end of 12 months as shown above.

Can you please give the baby steps would really help as am not good in bex

thanks

[Former Member · Accepted solution]

First create a formula variable and populate the date 01/2007 or if the user enters some other year let that year get populated by using user exit.

Then in the next new forumula use the same variable and use offsets to give next month. This shoudl be repeated for all 12 columns with offsets.

Again create another formula variable with processing type as user exit and populate from from first month till last month in that. This will get the aggregated data for all 12 months. Else you can use a new formula and add up all the 12 columns in that to get aggregated data.

[Former Member · Accepted solution]

Thanks Venkat,

this looks good from high level and who knows Bex good as i said am not that good in bex atleast with this variable types and user exits and formula variables.

can you please give me baby technical steps right from step 1.

basically can you put in technical terms of creating the formula variable from the time user enters the input variable 0fiscper.

It seems without user exit it is not possible as user can enter any year and period(0fiscper) ,is that correct???

I am not able to relate all of this and figure where and how to start

thanks again.

[Former Member · Accepted solution]

Milt,

Put all filters and free chars as per your tech spec.

Again put what all chars you need against which the key figures should be displayed.

Now coming to columns:

In a new selection restrict fiscper with the variable which the user enters. u can use any key figure here. Just hide this column.

Create a new selection. In that selection create a new variable on 0FISCPER which is not ready for input and processing type as "customer exit". These are all the options to be used while creating a formula variable.

While writing code in CMOD read the variable value eneterd the user and then populate the required date. Suppose the user enters 07/2006, then in the code read the year and attach it to the output which should be 01/2006. Now this column will have restriction value for 01/2006 month.

Again use the same variable in the second new selection. right click on that variable and set offsets +1. This will get the restrcition value 02/2006.

Repeat the same for all 12 columns.

For 13 th column, create a new formula and add up all the 12 columns which will give you the sum of all 12 months.

This wil give you the output as desired.

[Former Member · Accepted solution]

Thanks venkat again for your help

Can you please verify

First new selection is for user entry and hide

2nd new selection is for formula variable with user exit correct ??else it becomes RKF ??

If it is Formula variable ,I did not see the option of creating an offset like you can create it for other input variables.

I think the logic makes perfect sense if that’s the case ,can you please also give me the CMOD abap code would really help me out as all our ABAP guys are on thanks giving vacation no one to support.

Like you said user enters 07/2006 and formula variable should get it as 01/2006

[Former Member · Accepted solution]

Not the formula variable in the second column. you can create a varibale on 0FISCPER itself whcih should be not ready for input and processing type should be customer exit.

Willl give you the ABAP code shortly. Before that even you can try out.....

[Former Member · Accepted solution]

Hi Milt,

Please make use of the below code. I hope you will make small changes to replace certain things in the code and make it effective.

Let the user entry variable be ZUSRENT, where the user will be entering 11/2007

Let the other variable on 0FISCPER be ZPPYEAR.

Data: itab like i_t_var_range,

yvar(4) type N, (to hold year value)

WHEN ZPPYEAR.

IF i_step = 2.

LOOP AT i_t_var_range INTO itab WHERE vnam = 'ZUSRENT'.

CLEAR: yvar.

yvar = itab-low+0(4).

concatenate yvar '01' into l_s_range-low.

l_s_range-sign = 'I'.

l_s_range-opt = 'EQ'.

APPEND l_s_range TO e_t_range.

EXIT.

ENDCASE.

ENDLOOP.

ENDIF.

Follow the other steps as mentioned in the above reply.

[Former Member · Accepted solution]

Perfect answer

Thanks again Venkat

Regards

Milt