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?
 
James0628Connect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
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
 
hfma33Author Commented:
Thank you!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.