[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using previous group sum in current group calculation

Posted on 2004-03-25
9
Medium Priority
?
501 Views
Last Modified: 2010-07-23
SQL Server and CR 8.0 and ODBC

I'm creating a summary executive report for accounts receivable trends.  The report is grouped by month (using a date/time field) with the sums based on formulas that are in the detail section (which is hidden).

Report currently looks like this:

Month  Sales  Cash  Balance
Jan     XXX    XXX    XXX
Feb      etc...
Mar      etc...

I would like to add a column for Sales Two Months Prior and then be able to perform a calculation on that field.  So the March line would include the January sales sum.  Is there any hope?

0
Comment
Question by:nandand
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 10686349
This formula will produce the value two prior - substitue your own field name of course.

shared numberVar TwoPrior;
shared numberVar OnePrior;
whileprintingrecords;
if RecordNumber = 2 then
(
    OnePrior := Previous({TABLE.NUMBERFIELD});
);
if RecordNumber > 2 then
(
    TwoPrior := OnePrior;
    OnePrior := Previous({TABLE.NUMBERFIELD});
);

TwoPrior;
0
 

Author Comment

by:nandand
ID: 10706701
I could not get the previous function to work.  However I do have the solution which is a 4 formula method.  
In Group Header section:
whileprintingrecords;
numbervar CurMonth := 0;

In Detail section:
whileprintingrecords;
numbervar CurMonth := CurMonth + {table.sales});

Create a second group footer section.  In the first group footer:
whileprintingrecords;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder(month(table.datefield},2) = 0 then
LastEvenMonths
else
LastOddMonths

In the second group footer section:
whileprintingrecords;
numbervar CurMonth;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder (month(table.datefield},2) = 0 then
LastEvenMonths := CurMonth
else
LastOddMonths := CurMonth

I do not take credit for this. I got the advice from a different tech help site.  This did exactly what I needed it to do.
Thanks for your help, though.
0
 

Author Comment

by:nandand
ID: 10706963
Before I close this question I'd like to clarify and ask a question - I needed to grab a group sum that was two months old to use in the current group sum.  I could not get Previous to work with the group totals.  If I'm incorrect in my assumption, please clarify.  Otherwise I will close question and ask for a refund.  

P.S. I'm very grateful I got a response.  Thank you for your time.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:frodoman
ID: 10707049
Hey nandand,

You are correct that previous isn't going to work.  

Just curious though what didn't work about my formula?  Did you put it in the group footer?  I'm just asking because a) I've used it several times before and it works fine for me and b) sounds like the answer to your latest question.

In any event, I'm glad you have it working.

frodoman
0
 

Author Comment

by:nandand
ID: 10708286
frodoman,

I'd love to get your solution to work if for no other reason than I will learn from it. If I can figure it out - I'll give you the points.

Question: I'm confused as to what you mean by the Previous({TABLE.NUMBERFIELD}); ....Since this formula is in the group footer what field should I use?  My sales field?  That simply yields the last detail record when I need the sum amount.  If I use the group sum, I get an error that says there is no previous data.  Same for a running total.  Is there another formula not stated?

Next Question: The remainder also gives me the last record's record number which is a very high number since I have lots of data.  So what does the 2 get me?

I'm sorry for being so slow.  I really would like to understand your concept if you have the time.  If I need to explain my problem better please let me know.  Thank you!
0
 
LVL 42

Accepted Solution

by:
frodoman earned 2000 total points
ID: 10708436
You know, I think that I didn't realize you were looking for the 2 month ago summary at first and I gave you code for the 2 month ago record - this may have contributed to your confusion.

Regardless, the same concept can be used with the addition of another variable.  Here's the code for a formula that should go in GF1 - I've included comments but feel free to ask for more info if needed.

===============================================

// These variables hold the current group total as well
// as the previous and 2nd previous totals.  Since we can't
// use the Previous() function on a group total we'll
// store the values and pass them in variable form.
shared numberVar TwoPrior;
shared numberVar OnePrior;
shared numberVar ThisOne;
whileprintingrecords;

// If this is the first record, just save the group summary
if RecordNumber = 1 then
(  
    ThisOne := Sum ({Table3.fldnumber}, {Table3.fldnumber});
);

// If this is the 2nd record, copy the previous group
// summary into the OnePrior variable then save this
// group summary
if RecordNumber = 2 then
(
    OnePrior := ThisOne;
    ThisOne := Sum ({Table3.fldnumber}, {Table3.fldnumber});
);

// If this is > 2 then copy OnePrior into TwoPrior, then
// copy ThisOne into OnePrio, and finally save this
// group summary into ThisOne
if RecordNumber > 2 then
(
    TwoPrior := OnePrior;
    OnePrior := ThisOne;
    ThisOne := Sum ({Table3.fldnumber}, {Table3.fldnumber});
);

// Output TwoPrior value
TwoPrior;

===============================================
0
 
LVL 42

Expert Comment

by:frodoman
ID: 10708477
In answer to your "remainder" question, the remainder function gives you just that:

Remainder(5,2) = 1   ( 5 divided by 2 leaves a remainder of 1 )
Remainder(72,2) = 0  (72 divided by 2 leaves a remainder of 0 )

The remainder function is commonly used to get even multiples.  For example to do something every 14th record you would do a formula:  If Remainder(RecordNumber,14) = 0 Then...

Hope that makes sense.
0
 

Author Comment

by:nandand
ID: 10714899
I haven't tried your formulas (too many reports to write!) but how about using groupnumber instead of recordnumber.  Does that make sense or am I off base?
0
 
LVL 42

Expert Comment

by:frodoman
ID: 10715051
That makes sense to me.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 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