ChrisG

Cross tab reporting conundrum

Discussion created by ChrisG on Oct 30, 2012
Latest reply on Oct 31, 2012 by ChrisG

Dear FMP gurus

 

I have created a cross tab report where the months of the year run across the top, and down the side is various financial information. I then built a method where I could change the stating month, for example: A normal year goes from Jan to Dec, however a financial year may go from Mar to Feb or whatever the user chooses. This all works OK with the months re-arranging themselves accordingly. After having a good look at this I noticed that the months were simply being repositioned. Meaning, if 2012 started in March, then January and February of 2012 were displayed after December when what I really want is January and February of 2013 displayed after December.

 

This is how I got my months to repostion: I have a global field called YearMonthStartNumber...

 

Case(

YearStartMonth = "January"; 1;

YearStartMonth = "February"; 2;

YearStartMonth = "March"; 3;

YearStartMonth = "April"; 4;

YearStartMonth = "May"; 5;

YearStartMonth = "June"; 6;

YearStartMonth = "July"; 7;

YearStartMonth = "August"; 8;

YearStartMonth = "September"; 9;

YearStartMonth = "October"; 10;

YearStartMonth = "November"; 11;

YearStartMonth = "December"; 12

)

 

I then have a field called YearMonthNumber...

 

If ( ( Month ( Date ) - @ Globals::YearStartMonthNumber ) + 1 ≤ 0;

 

 

( 13 - @ Globals::YearStartMonthNumber ) + Month ( Date );

 

 

( Month( Date ) - @ Globals::YearStartMonthNumber ) + 1 )

 

and then I put the data into the correct place...

 

Case( Get( CalculationRepetitionNumber ) = Extend( YearMonthNumber ); Extend( Amount ) )

 

 

In hind sight it is clear that all I'm doing is shifting the month display around. I need to find some way to include the following year's data. Does anyone know how to do this?

 

thank you.

 

Chris G

Outcomes