cancel
Showing results for 
Search instead for 
Did you mean: 

Showing Fiscal Period according to user input in Bex Report

former_member209202
Participant
0 Kudos

Hi All,
I have the following requirment for one of the report i am preparing in Bex query.
The user will input fiscal period (Interval) through selection screen and the report will run for that period which is fine.

But there is another requirement that all the months related that period should be shown in the report. Ineed help on this.

For example:-
If user enter Fiscal period as 008.2011 to 008.2012.
Then the on of the field of the of report should show--
August 2011 or 008.2011
September 2011 or 009.2011

October 2011 or 010.2011

Novemebr 2011 or 011.2011

December 2011 or 012.2011

January 2012 or 001.2012

February 2012 or 002.2012 and so on.

I'll really appreciate the help and would like to know the step by step process to achieve this.

Accepted Solutions (0)

Answers (5)

Answers (5)

former_member209202
Participant
0 Kudos

Hi All,
Thanks a lot for your inputs.

I forgot to mention that depoending on the fiscal period input by user three column field will be generated in the report that is X, Y & Z.
The calculation for Z field should be = X+Y+Z of previous fiscal period.

Please see the report Layout below--

Say the user input for range of Fiscal Period is = 011.2011 - 002.2012





























 

Month


 

 

X


 

 

Y


 

 

Z


 

 

November 2011


 

 

200


 

 

300


 

 

700 (200+300+200(say this balance is the balance of October 2011))


 

 

December 2011


 

 

500


 

 

600


 

 

1800(500+600+700(say this balance is the balance of November 2011))


 

 

Jan 2012


 

 

300


 

 

100


 

 

2200(300+100+700(say this balance is the balance of December 2011))


 

 

Feb 2012


 

 

400


 

 

700


 

 

3300(400+700+2200(say this balance is the balance of Jan 2012))


 

The calculation should be achieved at query level not at the modelling level that is the constraint over here.

Regards
Sourav

Former Member
0 Kudos

Hi Sourav,

I'm just unclear about your requirement.

You got this output. Dot. What next?

Br,

H

former_member209202
Participant
0 Kudos

Hi Haris,
Thanks a lot for your  reply.

The above table is the output i should get.
Here the first column is user input --Fiscal period range.

Second Field is direct key figue value. So is third field.
The fourth field calculation logic as i mentioned above.

Wanted to how can i achieve this??

Regards
Sourav

Former Member
0 Kudos

Hi Sourav,

Fair enough.

First, you have to create an offset variable for fiscal period with offset value as -1.

This would help you get the value of previous month.

Second, create a formula which sums the above output and the remaining key figures.

Br,

H

Former Member
0 Kudos

Hi Sourav,

               Do you have to show the month's on the column headers. If yes, then bring the month to your report. Go to your month IObject. And set the display as MMMYYYY format.

Regards,

Kowshik

ravi_chandra3
Active Contributor
0 Kudos

Hi sourav,

In the variable wizard , select the label or key option and now execute the report. now the 001.2011 will be displayed with month name as january 2011.

Regards,
RaviChandra

Former Member
0 Kudos

Hi Sourav,

When you drill down fiscal year period and sort fiscal year period in your query output, i think your requirement will be achieved.

Br,

H

Former Member
0 Kudos

Hello Sourav,

     I hope that you may have use 0calday in your base  object.

      you can map to 0calmonth to your reproting object by which you can provide the reporting data as per you requirment. By adding 0calmonth in rows..