?
Solved

I need to display a running total in VS2008 C# CR

Posted on 2011-04-20
12
Medium Priority
?
369 Views
Last Modified: 2012-05-11
Hey CR guys!!

I need to display a running total for each statement printed for a monthly statement period.
I have a beginning balance which should be added to an Item total for each transaction TYPE which could be 'CHG' which would always be positive, 'PAYMENT' which would always be a negative, or 'ADJUSTMENT' which could be either.  These would be totaled and printed at the bottom with a "Please pay this amount" label and then set to 0 for the next statement.

Thanks for your help!!
Jerry
0
Comment
Question by:JDL129
  • 5
  • 5
  • 2
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35437269
Try this idea.

I assume you have a group on the account number

In the report header add a formula
WhilePrintingRecords;
Global NumberVar MonthlyTotal;
""

In the group header add a formula to reset the total to the current balance
WhilePrintingRecords;
Global NumberVar MonthlyTotal;
MonthlyTotal := {CurrentBalanceField};
""

In the details calculate the total
WhilePrintingRecords;
Global NumberVar MonthlyTotal;
MonthlyTotal := MonthlyTotal + {ItemTotal};
""

IN the group footer to print the amount due
WhilePrintingRecords;
Global NumberVar MonthlyTotal;
MonthlyTotal

mlmcc
0
 

Author Comment

by:JDL129
ID: 35437619
mlmcc!!!
Thanks for the response!!

In the detail formula I need to evaluate the type of transaction to decide if the total is to be added or subtracted:

If {sp_Statement;1.TYPE} = "CHG" then
    MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};

If {sp_Statement;1.TYPE} = "PAYMENT" then
    MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE};

If {sp_Statement;1.TYPE} = "ADJUSTMENT" then
    IF {sp_Statement;1.ITEMPRICE} > 0 THEN
        MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};
    IF {sp_Statement;1.ITEMPRICE} < 0 THEN
        MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE};

This is what I need it to do but can't make it work.

Thanks,

Jerry
This does subtract a PAYMENT but doesn't add a CHG.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35437733
Are you sure there are charges?

Is ItemPrice always positive for charge and payment?

Does it handle adjustments correctly?

Did you copy and paste the formula from your report to here?

mlmcc
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:JDL129
ID: 35438264
mlmcc!!  Do you never sleep?  :)
There are charges but the type is "CHG" and they are always to be added to the monthlyTotal.
The PAYMENTS are always positive but should be subtracted from the MonthlyTotal.
The ADJUSTMENTS can be positive or negative and should be handled accordingly.
Yes I did post the formula I came up with but only for the detail.  The formula as is doesn't work.  I had to change the numbervar to currencyvar.  If I just do the PAYMENTS it will work but when I add CHG or ADJUSTMENTS I just get zeros.

Thanks for hanging in there with me!!

Jerry
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35438287
I prefer the if then else if structure

Try it this way

WhilePrintingRecords;
Global CurrencyVar MonthlyTotal;
If {sp_Statement;1.TYPE} = "CHG" then
    MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};
Else If {sp_Statement;1.TYPE} = "PAYMENT" then
    MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE};
Else If {sp_Statement;1.TYPE} = "ADJUSTMENT" then
    IF {sp_Statement;1.ITEMPRICE} >= 0 THEN
        MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};
    Else
        MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE};
""

Do all types use the same field for the value?

mlmcc

0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 300 total points
ID: 35439275
The tests to see if ITEMPRICE is > 0 or < 0 seem to be unnecessary, or don't make sense, or both.  You're saying if it's > 0, add it, but if it's < 0, subtract it.  But if you subtract a negative, you're adding it.  For example, 1 - (-3) = 1 + 3 = 4.

 Do you actually want to add the value if it's negative?  IOW, do you want to ignore the sign and just always add ITEMPRICE if the TYPE is "ADJUSTMENT"?  If so, you could just use abs to remove the sign:

If {sp_Statement;1.TYPE} = "ADJUSTMENT" then
    MonthlyTotal := MonthlyTotal + Abs ({sp_Statement;1.ITEMPRICE});


 If you actually want to subtract the value if it's negative, just add the field.  1 + (-3) = 1 - 3 = -2

If {sp_Statement;1.TYPE} = "ADJUSTMENT" then
    MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};


 James
0
 

Author Comment

by:JDL129
ID: 35440810
mlmcc!!
Thanks for the response!!  My CR doesn't like the else if.  When it hits the first Else If the error message says that the remaining text does not appear to be part of the formula.  It will run if I take out the Else's and just make 3 If statments but it appears to only run the last If statement because with PAYMENT as the last all of the CHG values are 0.00.  The odd thing though is that the group footer formula has the correct total.  Go figure???

James,
Good idea!!  You can tell I was never good at arithmetic.  :)

Jerry
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35444875
Remove the ; before the elses

mlmcc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35444884
It would be this taking in James comment

WhilePrintingRecords;
Global CurrencyVar MonthlyTotal;
If {sp_Statement;1.TYPE} = "CHG" then
    MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE}
Else If {sp_Statement;1.TYPE} = "PAYMENT" then
    MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE}
Else If {sp_Statement;1.TYPE} = "ADJUSTMENT" then
     MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};
""

A slightly better way if you can only have the 3 values for type

WhilePrintingRecords;
Global CurrencyVar MonthlyTotal;
If {sp_Statement;1.TYPE} = "PAYMENT" then
    MonthlyTotal := MonthlyTotal - {sp_Statement;1.ITEMPRICE}
Else
    MonthlyTotal := MonthlyTotal + {sp_Statement;1.ITEMPRICE};
""


mlmcc
0
 

Author Comment

by:JDL129
ID: 35450782
Upping points!
0
 

Author Closing Comment

by:JDL129
ID: 35450796
Thanks to both of you for the responses!!!!!!!!!!!!!  I hope the split is appropriate.

Jerry
0
 
LVL 35

Expert Comment

by:James0628
ID: 35452625
You're welcome.  Glad I could help.

 James
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question