Print Page | Close Window

If OnLastRecord?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22629
Printed Date: 24 Apr 2024 at 7:40pm


Topic: If OnLastRecord?
Posted By: techsupport
Subject: If OnLastRecord?
Date Posted: 15 Jun 2018 at 12:29pm
Hello,
There's this on Suppress Freight and InvTotal (so not to repeat when there are multiples lines on an invoice) 
if OnLastRecord = False then

Next({AR_InvoiceHistoryDetail.DetailSeqNo}) <> '000000' Else False


How do I modify it to also handle if the 1st record doesn't have 000000 but 000001 instead?

So it's ItemCodes and sometimes the same item repeats, hence using the DetailSeqNo (Line item no).

However it appears on some invoices the 1st line item starts with 000000 and some starts with 000001.

Why the original OnLastRecord doesn't always work.

The reason for the suppress is that there are header records and detail records so the header record repeats for every detail record. Just want to show it once on the 1st detail record of the header record.

Hope that made sense?

Example:
Invoice = 1
DetailSeqNo = 000000
Itemcode = X
DetailSeqNo = 000001
ItemCode = X
DetailSeqNo = 000002
ItemCode = Y

Invoice = 2
DetailSeqNo = 000001
ItemCode = Y
DetailSeqNo = 000002
ItemCode = Y
DetailSeqNo = 000003
ItemCode = Z

Invoice = 3
DetailSeqNo = 000000
ItemCode = X
DetailSeqNo = 000001
ItemCode = Y
DetailSeqNo = 000002
ItemCode = Z

WHY it did that, do not know but that's what's in the system (tables) and dealing with that.



And to properly total on Report Footer?

Please help.

Thank you

https://www.dropbox.com/s/o5rvhmj711zi0ca/Example.jpg?dl=0

https://www.dropbox.com/s/i3qg1v89jodgs83/Example-02.jpg?dl=0

The highlighted are the freight and invoice total per invoice and should only show once per invoice.

-------------
____________________
Thank you very much!


Replies:
Posted By: hilfy
Date Posted: 22 Jun 2018 at 4:46am
If I understand you correctly, you want to suppress the header record for all of the details except the first one. Is that correct?

Instead of putting all of your data in the details section and suppressing the one that contains the header data, I would group by invoice and put the invoice header information in the group header and just the item details in the details section. This way you don't need to use any suppression formula.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: techsupport
Date Posted: 22 Jun 2018 at 7:29am
Yes thank you

How to total at the end? Grand Total / Report Total when also hiding duplicates?

-------------
____________________
Thank you very much!

Posted By: hilfy
Date Posted: 22 Jun 2018 at 7:57am
If you're talking about duplicate invoice data, I would use a Running Total for this with the following settings:

Field to summarize: The field you want a sum of.
Type of summary: sum
Evaluate: On change of group - the invoice group.
Reset: Never

This will get you a grand total of invoice amounts if the total amount is in the invoice header.

Invoice count: Use the DistinctCount summary to count the invoice numbers - no need for a running total.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: techsupport
Date Posted: 22 Jun 2018 at 8:29am
Ok thank you

It's on both

Header total - total invoiceamt, freightamt, discountamt <-- this looks to be correct
Detail total - item total <-- this is off
Maybe due to duplicate line records/

-------------
____________________
Thank you very much!

Posted By: hilfy
Date Posted: 22 Jun 2018 at 9:47am
Use the running total for the header info and regular summaries for the detail total.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: techsupport
Date Posted: 22 Jun 2018 at 10:13am
Yes, that is what I did.
When I export the report out and total the details, there's a discrepancy.

Exported and totaled: 4,852          36,137.27
Sum Detail On Report: 4,854          36,162.52


-------------
____________________
Thank you very much!

Posted By: hilfy
Date Posted: 22 Jun 2018 at 10:42am
Go to the Database menu and make sure that "Select distinct records" is turned on. If it's already on or if turning it on doesn't help, try using running totals.

Set Evaluate to On change of field DetailSeqNo.

Make sure that your data is sorted by DetailSeqNo inside the Invoice group for this to work correctly.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: techsupport
Date Posted: 22 Jun 2018 at 12:55pm
The Select Distinct Records did not make a difference.
I've tried the 2nd part and tried it again and that results in a very large difference for the totals than the above

-------------
____________________
Thank you very much!


Print Page | Close Window