• Search Forums
    Advanced Search Topics Posts
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
Posts: 49
Veteran Member
4/8/2010 10:23 AM
What is your formula for period? Thanks!
TracyO
Veteran Member Send Private Message
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.
Deleted User
New Member Send Private Message
Posts: 0
New 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 Send Private Message
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?