Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: If OnLastRecord? Post Reply Post New Topic
Author Message
techsupport
Newbie
Newbie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 34
Quote techsupport Replybullet Topic: If OnLastRecord?
    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.

Edited by techsupport - 15 Jun 2018 at 6:02pm
____________________
Thank you very much!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3639
Quote hilfy Replybullet 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
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
techsupport
Newbie
Newbie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 34
Quote techsupport Replybullet 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!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3639
Quote hilfy Replybullet 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
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
techsupport
Newbie
Newbie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 34
Quote techsupport Replybullet 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/

Edited by techsupport - 22 Jun 2018 at 9:58am
____________________
Thank you very much!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3639
Quote hilfy Replybullet 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
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
techsupport
Newbie
Newbie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 34
Quote techsupport Replybullet 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!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3639
Quote hilfy Replybullet 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
9-Time SAP BusinessObjects Partner of the Year.
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
techsupport
Newbie
Newbie


Joined: 03 Jun 2014
Location: United States
Online Status: Offline
Posts: 34
Quote techsupport Replybullet 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!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.