Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Crystal Report Issue

 6 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
JeanneS
Veteran Member
Posts: 49
Veteran Member
4/7/2010 1:07 AM
    I have a crystal report that uses an union query to pull the actual and budget amounts from the GLAMOUNTS and FBDETAIL. The query lists the amounts all on the same row as shown below (12 debit fields and 12 credit fields).

    FY AU Account debitperiod1 creditperiod1 debitperiod2 creditperiod2 debitperiod3 etc.

    I want to list my data as shown below where the net amount is the total of the debit and credit for each period and the period is 1, 2, 3 etc:

    FY AU Account Net Amount Period

    I need to make a chart based on the data so I need one amount field and a field to identify the period.

    Does anyone have any suggestions as to how to do this? I tried using an if else statement but it only lists the first period.
    TracyO
    Veteran Member
    Posts: 97
    Veteran Member
    4/7/2010 11:25 AM
      Jeanne
      Here is the formula we used to get the glamounts by period.
      Hope this helps

      if {?Fiscal Year} = {glamounts.fiscal_year} then
      if {?Period} = 1 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01}
      end if
      if {?Period} = 2 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02}
      end if
      if {?Period} = 3 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03}
      end if
      if {?Period} = 4 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04}
      end if
      if {?Period} = 5 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05}
      end if
      if {?Period} = 6 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06}
      end if
      if {?Period} = 7 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07}
      end if
      if {?Period} = 8 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07} + {glamounts.db_amount_08} + {glamounts.cr_amount_08}
      end if
      if {?Period} = 9 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07} + {glamounts.db_amount_08} + {glamounts.cr_amount_08} + _
      {glamounts.db_amount_09} + {glamounts.cr_amount_09}
      end if
      if {?Period} = 10 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07} + {glamounts.db_amount_08} + {glamounts.cr_amount_08} + _
      {glamounts.db_amount_09} + {glamounts.cr_amount_09} + {glamounts.db_amount_10} + _
      {glamounts.cr_amount_10}
      end if
      if {?Period} = 11 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07} + {glamounts.db_amount_08} + {glamounts.cr_amount_08} + _
      {glamounts.db_amount_09} + {glamounts.cr_amount_09} + {glamounts.db_amount_10} + _
      {glamounts.cr_amount_10} + {glamounts.db_amount_11} + {glamounts.cr_amount_11}
      end if
      if {?Period} = 12 then
      formula = {glamounts.db_beg_bal} + {glamounts.cr_beg_bal} + {glamounts.db_amount_01} + _
      {glamounts.cr_amount_01} + {glamounts.db_amount_02} + {glamounts.cr_amount_02} + _
      {glamounts.db_amount_03} + {glamounts.cr_amount_03} + {glamounts.db_amount_04} + _
      {glamounts.cr_amount_04} + {glamounts.db_amount_05} + {glamounts.cr_amount_05} + _
      {glamounts.db_amount_06} + {glamounts.cr_amount_06} + {glamounts.db_amount_07} + _
      {glamounts.cr_amount_07} + {glamounts.db_amount_08} + {glamounts.cr_amount_08} + _
      {glamounts.db_amount_09} + {glamounts.cr_amount_09} + {glamounts.db_amount_10} + _
      {glamounts.cr_amount_10} + {glamounts.db_amount_11} + {glamounts.cr_amount_11} + _
      {glamounts.db_amount_12} + {glamounts.cr_amount_12}
      end if
      end if


      Matthew Nye
      Veteran Member
      Posts: 514
      Veteran Member
      4/7/2010 11:30 AM
        Additionally, take a look at GLCONSOL and GLAMOUNTX tables. You may have to set some flags in your system in order to get them populated but if they meat your needs from a granular stand point it may save you some heartache.

        hth
        Matt
        If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
        JeanneS
        Veteran Member
        Posts: 49
        Veteran Member
        4/8/2010 10:23 AM
          What is your formula for period? Thanks!
          TracyO
          Veteran Member
          Posts: 97
          Veteran Member
          4/8/2010 1:40 PM
            In my formula {?Period} is just a prompted paramater field. You could probably use system period if that would work for your scenario.
            Adam Jacobson
            Veteran Member
            Posts: 69
            Veteran Member
            4/8/2010 1:54 PM
              Here is a little more advanced formula:
              numbervar array CR_amounts :=
              [{GLAMOUNTS.CR_AMOUNT_01},{GLAMOUNTS.CR_AMOUNT_02},{GLAMOUNTS.CR_AMOUNT_03},
              {GLAMOUNTS.CR_AMOUNT_04}, {GLAMOUNTS.CR_AMOUNT_05},{GLAMOUNTS.CR_AMOUNT_06},
              {GLAMOUNTS.CR_AMOUNT_07}, {GLAMOUNTS.CR_AMOUNT_08},{GLAMOUNTS.CR_AMOUNT_09},
              {GLAMOUNTS.CR_AMOUNT_10}, {GLAMOUNTS.CR_AMOUNT_11},{GLAMOUNTS.CR_AMOUNT_12}];

              numbervar array db_amounts :=
              [{GLAMOUNTS.DB_AMOUNT_01},{GLAMOUNTS.DB_AMOUNT_02},{GLAMOUNTS.DB_AMOUNT_03},
              {GLAMOUNTS.DB_AMOUNT_04}, {GLAMOUNTS.DB_AMOUNT_05},{GLAMOUNTS.DB_AMOUNT_06},
              {GLAMOUNTS.DB_AMOUNT_07}, {GLAMOUNTS.DB_AMOUNT_08},{GLAMOUNTS.DB_AMOUNT_09},
              {GLAMOUNTS.DB_AMOUNT_10}, {GLAMOUNTS.DB_AMOUNT_11},{GLAMOUNTS.DB_AMOUNT_12}];

              numbervar period;
              numbervar totalamt;
              // only calculate if the fiscal year is the starting fiscal year
              //if {GLAMOUNTS.FISCAL_YEAR} = {?Starting Fiscal Year}
              // then
              period := 1;
              totalamt := {GLAMOUNTS.CR_BEG_BAL}+{GLAMOUNTS.DB_BEG_BAL};
              while period < {?Thru Period} do
              (totalamt := totalamt + cr_amounts[period] + db_amounts[period];
              period := period + 1;
              );
              ;

              numbervar AccountTotal := totalamt;
              AccountTotal;
              This formula calculates year to date. But it could be easily modified to do a period range (load period with starting period parameter and then continue to an ending period)
              JeanneS
              Veteran Member
              Posts: 49
              Veteran Member
              4/11/2010 12:14 AM
                Could you please elaborate on how the period is being used with the formulas listed as the GLAMOUNTS table does not have a period field. Are you creating a formula to create a period field?