Crystal Reports V10 - Report using next function in Running Totals

I have a report that is grouped on

Quarter (startdate of meeting, printed for each quarter)
Status of event (ContractStatusID)
MeetingID

Row data is displayed in the MeetingID header (GH3a); this is info on the meeting (ie. name, fees, etc.)  Then there is meetingID change data (in tblMeeting_changes, connected to tblMeeting) displayed in GH3b and the detail section of the report. This table tracks and stores data that a user has changed on the main meetingID record.  My main issue in this report is that I am trying to sum up quarterly data across all statuses in the report footer, as well as by quarter and by status in the report footer.  I could not make these totals get anywhere near correct, since it would add up the fees multiple times (for as many records as there were in the meeting changes table).  I was advised to use the "next" function along with meetingID<>meetingID formula.  I tried this in the RT on evaluate using a formula.  It works most of the time, but of course that's not good enough.  It doesn't work when there is a row of data at the end of a status with multiple change rows...it just skips it altogether.  I have attached design and report view images to help.

Any help is appreciated...I am at my wit's end with this one.


DesignView.JPG
ReportView.JPG
hfma33Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Try it this way

{@Quarter} = '2nd Qtr'
AND
(
    NextIsNull({Meeting.MeetingId})
     OR
    Next({Meeting.MeetingId})  = {Meeting.MeetingId}
)

mlmcc
0
mlmccCommented:
You could also use OnLastRecord instead of the NextIsNull part

mlmcc
0
hfma33Author Commented:
The first way didn't work at all.  It added up all the fees multiple times for as many records as the meetingID had in the meeting_changes table. :(

The second way gives me an error that says "too many arguments have been given to this function."
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

mlmccCommented:
{@Quarter} = '2nd Qtr'
AND
(
    OnLastRecord
     OR
    Next({Meeting.MeetingId})  = {Meeting.MeetingId}
)


mlmcc
0
James0628Commented:
As far as the error goes, I suspect that you replaced NextIsNull ({Meeting.MeetingId}) with     OnLastRecord ({Meeting.MeetingId}).  That would give you that error because OnLastRecord is used by itself, with no argument.  However, if the first version didn't work for you, the version with OnLastRecord presumably won't work either.

 I think the basic problem is that you're trying to look for changes in the value in a group field (MeetingID) that's contained in other groups.  For example, if the last record in one ContractStatusID group had the same MeetingID as the first record in the next ContractStatusID group, that last record would not be included in the running total when using your original <> test, because the next record (in the next ContractStatusID group) has the same MeetingID.

 I would try adding the other group fields to your test.  Something like:

{@Quarter} = '2nd Qtr' and
 (
  OnLastRecord or
 {Meeting.MeetingId} <> Next ({Meeting.MeetingId}) or
 {LookUp_ContractStatus.ContractStatusID} <> 
 Next ({LookUp_ContractStatus.ContractStatusID}) or
 {@Quarter} <> Next {@Quarter})
)

 James
0
hfma33Author Commented:
James-
That works!  My only question is how do I accommodate the change of contractstatusID's?  The original non-working formula is

{@Quarter}='2nd Qtr'
and {LookUp_ContractStatus.ContractStatusID} in [3,6]
and next ({Meeting.MeetingID})<>{Meeting.MeetingID}

What you gave me is below, but there is no accounting for certain statuses, so I'm stumped.


{@Quarter} = '2nd Qtr' and
(
  OnLastRecord or
 {Meeting.MeetingId} <> Next ({Meeting.MeetingId}) or
 {LookUp_ContractStatus.ContractStatusID} <>
 Next ({LookUp_ContractStatus.ContractStatusID}) or
 {@Quarter} <> Next ({@Quarter})
)
0
James0628Commented:
I think you just need to add the additional test.

{@Quarter} = '2nd Qtr' and
{LookUp_ContractStatus.ContractStatusID} in [3,6] and
(
  OnLastRecord or
 {Meeting.MeetingId} <> Next ({Meeting.MeetingId}) or
 {LookUp_ContractStatus.ContractStatusID} <>
 Next ({LookUp_ContractStatus.ContractStatusID}) or
 {@Quarter} <> Next ({@Quarter})
)


 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hfma33Author Commented:
Thank you!
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.