Key figure Comparison for Two time periods

[Former Member]

Hi SAP Gurus,

Iam a novice in BI 7.0. I currently need some advice and directions on the following situation.

The datawarehousing holds data based on calendar dates. The BI system extracts the data from an MS SQL Server.

The report needs to compare key figures for two time periods. The time period may either be Monthly/Quarterly/Yearly or for that case can be a date range also. The key figures has to be displayed in two columns and only two columns (one each for the time periods).

Kindly do advice me on how to model and design the report based on the time-period and restricting it to only two columns.

I would much appreciate all kinds of suggestions.

Thanks,

Arun.

[Former Member · Accepted solution]

Hi,

create two key figures restricted to your time periods.

Create two variables processed by user input, selection options.

Easiest solution:

Your key figures will be restricted only by one time characteristic: either MTH, QUART or YEAR.

Advanced solution:

If you want to let the time period free (user could enter values for year or month...)then you'll have to create 6 variables and check with exits which periods he wants to compare. In addition you'll have two create 2 aditional VARS processed by user exit (not visible and not ready for input) holding the values for CALMONTH since this is the most common denominator for YEAR, QUART and MONTH.

These two vars will be calculated in function of what the user has entered

Example:

6 VARS, user, ready for input.

0YEAR: YEAR1, YEAR2

0CALMONTH: MTH1, MTH2

0CALQUART: QUART1, QUART2

2 VARS: exit, not ready for input.

0CALMONTH: MONTHP1, MONTHP2

Your 2 KF:

KF_PER1 restricted to MONTHP1, resp KF2/MONTHP2

It is assumed that the two key figures will be compared by YEAR OR CALMONTH OR QUARTER.

in the exit

WHEN MONTH1.

IF I_STEP = 2.

get values entered in YEAR1 or MTH1 or QUART1; if several then you'll have to decide or raise and error.

calculate MONTHP1 based on YEAR1 or MTH1 or QUART1

same for MONTH2

at then very end WHEN I_STEP = 3 you can validate what users have entered and if for instance user has entered MONTH1 and QUART2 and he should have entered MONTH2 instead you can raise an exception and return to the variable input screen..

As you can see the solution may get easily very complex....

please let me know should you need further clarification

Olivier.

[Former Member · Accepted solution]

Dear Olivier, Thanks for the reply.

Although I get what you are trying to say, I don't know how to design in the BeX to get the User inputs for the variables.

Kindly do help me on the same.

Regards,

Arun.

[Former Member · Accepted solution]

in the QDesigner create a new query based on your InfoProvider.

In the Column frame under Key figures, right click, New Selection.

In this popup you'll see on the left all objects available.

Drag your key figure to the right side.

Drag your time period (0CALYEAR for a start) to the right

Right click now 0CALYEAR on the right and Restrict.

select the variable tab.

now you can use an already existing var and create your own...

hope this helps.

Olivier.

[Former Member · Accepted solution]

Dear Olivier,

Thanks for your reply and support.

I followed your directions and created the report. But in the report, the user wants to dynamically change the value of the variable (In this case, say there is only one variable, the Year).

Kindly do advice me on proceeding further.

Regards,

Arun.

[Former Member · Accepted solution]

ok,

so did you create your own var? or did u use an already existing one?

once the user is in the report, he can always go back to the variable selection screen.

it depends now on how the variable has been defined.... if it is "can be changed in query nav" it won't show up again in the var screen because the user can change it by filtering the YEAR itself.

In your case since you have a restricted KF on the year, it makes no sense to have 0CALYEAR as free char in your report; therefore your var should NOT be marked "can be changed..." and the user will have to step back to the var sel screen.

hope this helps.

[Former Member · Accepted solution]

Dear Olivier,

Trust Ur doing fine. Thnx for the support and help. I created a separate variable and solved the issue.

Sorry for the belated reply, I was on travel.

Regards,

Arun.