2 Replies Latest reply on Oct 31, 2012 3:46 AM by ChrisG

    Cross tab reporting conundrum

    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