Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Multi line items--dont want to see them Post Reply Post New Topic
Page  of 3 Next >>
Author Message
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Topic: Multi line items--dont want to see them
    Posted: 14 Aug 2009 at 8:43am
My report is dealing with Parts and Services
 
I have it grouped (GROUPa)by the Description of part/service (so that there should be only one type of part/service per WO/INVOICE- QTY CAN INCREASE)
 
Fields displayed:
Description of Part/Service
Invoice # (where that appears in the register)
Quantity- (appears in the register)
 
ISSUE:
 
There are instances where a part/service has been entered twice in error:
 
WO#(not displayed as field) - INVOICE #    - Part/Service - Qty
 
ABCDE - 12345 - Haircut - 1
ABCDE - 56789 - Haircut- 1  <-----thats the error
FGHIF - 83839 - Haircut 2
 
I donot want that error to be displayed as an item- nor do i want it to appear in the Total Sum for Qty.
 
I'm not running a SUM of Qty because I have a formula(FORMULA1) that has to SUM it :
 
if {INVITEMS.DESCRIPTION} like "*Recharge" then {@Dif Qty Recharge}

else Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}) --
 
how do i NOT have that double entry line item display on the report and how to i ensure that it will NOT add up in FORMULA1?
 
 
PLZ HELP


Edited by lahorichick - 14 Aug 2009 at 8:44am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 8:53am

Suppressing an item does not exclude it fom a SUM or Count however gettting the condition to suppress can often lead to a way to that.

You need a condtion (or set of conditions that) that always define an include or exclude.
From your sample data the only thing I see is that ...
If the WO# and the part/service and quantity fields are the same this is error...
Is this ALWAYS an error and if not how can you tell? Or is there a better way with a field not shown to make this determination?


Edited by DBlank - 14 Aug 2009 at 8:54am
IP IP Logged
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Posted: 14 Aug 2009 at 9:00am
If this part/service ABCD appears twice on a single WO, then YES everytime that is an error--you can only increase the qty for it.
 
I was running :
Formula Suppress:
 
IF previous ({WRKORDER.WRKORDNBR}) = {WRKORDER.WRKORDNBR} then '1'
else '0'
I can't set this as a group...I figure cuz my group is ItemDescription?
 
Can I include something in my total formula to not add anything that is suppressed?  or anything if the previous WO# is the same?
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 9:07am

Couple of ways to deal with this but first I need to know:

1. Do you have to have this sorted in any particular way
2. Is there grouping in the report already (if so what)
3. If grouping are you using drill down at all?
IP IP Logged
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Posted: 14 Aug 2009 at 9:12am
I have it GROUPed by service/part description--why because at the end of the service/part description group i total up the Qty for that group, then work in how much it cost me to do this particular Group.
 
Can the grouping be changed..yes...but i would still need it to return total for that specific service/part
 
Drill down?....Yes/no---because lets say i want to know how much i spent on shampoo during this qtr..i can just drill down to shampoo--its easier for auditing purposes..
 
why what did you have in mind?
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 9:30am

Grouping by product will still work for this idea.

The issue is that the even for your supress you have to make the the dupe rows follow in a sequential sort. If you break things up it all falls apart. The only issue is
 
Here is the easiest way (IMO) to handle the problem.
Go ahead and group on product.
Create a formula field as "Suppress":
totext({WRKORDER.WRKORDNBR},0,"") + "-" + {INVITEMS.DESCRIPTION}
Sort on this.
Now you can conditionally suppress the detail row as:
not onfirstrecord and previous({@supress})={@supress}
 
For your Sums/AMounts you can use Running Totals. However you cannot use a SUM of a SUM. So you need to create formulas per row that you can use for your overall SUM or quantity or whatever you want. Don't worry about it duplicating on the next row, just make it handle the correct amounts for that row.
For all teh Running Totals You will have it Evaluate for "On Change of Field" as {@Suppress}.
For group totals Reset to On change of group (Product Group).
For Report Totals reset as NEVER.
Will this work for you?
Now in the
 
IP IP Logged
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Posted: 14 Aug 2009 at 11:02am

The first part worked--with the 'suppress'

 
the sum part..i'm confused...If I add a running total of
 
Invitems.Quantity
evaluate on change of field = 'suppress'
 
and drop that running total on each line item---it just adds it quantity with the previous--
ex- by the end of item description shampoo it shows 391 (thats the amount I want)
 
I changed the Report Totals reset to on change of group Invitem.Description
 
Now the issue with the running total is:
 
there is one service called 'charge' where i cant take the quantity total as given by the running total--or even adding up the quantity myself.  Its:
 
x = Qty charge - (Qty wash + Qty of Shampoo)
 
what i have in place for that in my org file:
 
Invoice Items Qty Sum:
if {INVITEMS.DESCRIPTION} like "*charge" then {@Dif Qty charge}

else Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION})
 
Dif Qty charge
numberVar TotalchargeQty := {@Qty charge}-({@Qty Wash}+{@Qty Shampoo});
TotalchargeQty
 
Qty Charge
if {INVITEMS.DESCRIPTION} like "*charge" then (numberVar charge := ((Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}))*1));
charge
 
Qty Wash
if {INVITEMS.DESCRIPTION} like "*Wash" then (numberVar Wash:= ((Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}))*1));
Wash
 
Qty Shampoo
if {INVITEMS.DESCRIPTION} like "*Shampoo" then (numberVar shampoo := ((Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}))*1));
shampoo
 
 
I had the Invoice Items Qty Sum running the total qty for a single service/part.
 
The Items Qty that arises with the running total is correct...how do i reflect..that one stupid item
 
And how do I pass the given Running total so that its one whole number and not different for each line item?
IP IP Logged
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Posted: 14 Aug 2009 at 11:11am
OHH>>>ok so i got where to put the running total that it gives me the total for that invitems.description group
 
so only that Charge --total is left to address..any more advice (VERY APPRECIATED)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Aug 2009 at 11:37am
Hmmm,
Tying to figure out what you have going on hereand having trouble.
However maybe you can figure this out if I explain a bit...
You can create a RT to replace any of your Formulas.
For example
Qty Charge
if {INVITEMS.DESCRIPTION} like "*charge" then (numberVar charge := ((Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}))*1));
Can make a formula for "Charge" as:
if {INVITEMS.DESCRIPTION} like "*charge" then {INVITEMS.QUANTITY} else 0
Now you can use that formula field in your RT to get a SUM of "@Charge". If you Reset the RT at Group1 then this value is per Group (and as you found out must be placed in the Group Footer to display the total - same as a variable formula final display). If you need it for all records, Reset is NEVER and placed in the Report Footer. You can also exclude your dupes the same way as in your other RT using the Evaluate for on change of the "Suppress".
This final Value can be used in a formula giving you a new total using different RTs
Like...{#RT charge}-({#RTQty Wash}+{#RT Shampoo})
This has to be placed in the Report footer to work.
Does this help?


Edited by DBlank - 14 Aug 2009 at 11:40am
IP IP Logged
lahorichick
Groupie
Groupie


Joined: 13 Aug 2009
Online Status: Offline
Posts: 54
Quote lahorichick Replybullet Posted: 14 Aug 2009 at 12:24pm

Everything except for the Charge total is working

I entered in the formula :

{#charge}-({#wash }+{#shampoo})

and its still returning the total qty of 316....

when it should be
316- (170+118)
 
?? what am i doing wrong?
its in the footer...
 
IP IP Logged
Page  of 3 Next >>
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.020 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.