Code for User Exit

[Former Member]

Dear Bwers,

I have a financials BEx report that has 12 amount columns, each restricted by posting period (1-12) for current fiscal year. These columns are also restricted by customer exit variables for Version and Value Type. Now the requirement if the user selects posting period 3, then the first three columns should show actual values (Version = 0 and Value Type = 10) and the rest of the columns should show plan data (Version = value should be taken from user entry variable (ZVER), Value Type = 20). Can somebody assist me with the code for this? Thanks in advance for all the help.

Thanks

Raj

[Former Member · Accepted solution]

Jose,

I tried your suggestion and i am getting 4 columns PY-Act,CY-Act, CY-Plan, NY- Plan. However i am not able to see the 36 columns by posting period as i intend to see. It is just showing me the cumulative values for all these four columns. Any thoughts?

Raj

[Former Member · Accepted solution]

You need to include 0FISCYEAR and 0FISCPER3 above the structure you created for PY-Act,CY-Act, CY-Plan, NY- Plan in the columns. This will give you the break down by year and period.

[Former Member]

Thanks a lot Jose. It worked.

Raj

[Former Member]

Jose,

Thanks for your input. However i have a change in my requirement. I need a 36 month trend report with:

1. 12 months last year with Actuals.

2. 12 Months of Current Year with Actual or Plan Values depending on what period the user enters.(eg: if the user enters period 5, then 1-5 should be actuals and 6-12 should be plan)

3. 12 Months of next year with Plan Values based on Version Variable and Value Type = 20.

I have the solution for 1 and 3 but not 2. Please give me your thoughts. Thanks again for the help.

Raj

[Former Member]

Hi Raj

I managed to create your 3 requirements with some small changes to the method i described in the prior post.

Columns

0FISCYEAR

0FISCPER3

Structure

RKF1. Actuals = vtype -10, version -0, year -current fiscal year (variable -1) and posting period - 1-12

RKF2. Actuals = vtype -10, version -0, year -current fiscal year(variable) and posting period - BETWEEN (Period 1 and User Input posting perid value),

RKF3. Plan = vtype -20, version -Variable input, year -current fiscal year and posting period - BETWEEN (User Input posting perid value (Offset +1) and Period 12),

RKF4. Plan = vtype -20, version -Variable input, year -current fiscal year (variable +1) and posting period - 1-12

Also dont forget to set zero suppression on the columns

Josh

[Former Member]

Hi Raj

You can achieve this without using anycode.

In the columns area of the query designer drag in posting period, then under that create a structure, then create 2 RKF,

Actuals, vtype -10, version -0, year -current fiscal year and posting period - BETWEEN (Period 1 and User Input posting perid value),

Plan, vtype -20, version -Variable input, year -current fiscal year and posting period - BETWEEN (User Input posting perid value (Offset +1) and Period 12),

Then in the query properties on the Rows/Columns tab set the zero suppresion Active (All = 0) and effect on Coumns, then click on the structure and tick zero row suppresion on the display tab.

the query should run as follows if you enter period 3, 1-3 actual, 4-12 plan with version entered and prompt.

Period 1 2 3 4 5 6 etc

Act Act Act Plan Plan Plan

Characteristic

You could do this with code but you would need to create a variable for each period in order to make the value type dependent on the period,

Hope this helps

Josh