Multi line items--dont want to see them
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7379
Printed Date: 05 May 2024 at 5:35pm
Topic: Multi line items--dont want to see them
Posted By: lahorichick
Subject: Multi line items--dont want to see them
Date 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 mailto:%7b@Dif - {@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 |
Replies:
Posted By: DBlank
Date 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?
|
Posted By: lahorichick
Date 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:
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?
|
Posted By: DBlank
Date 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?
|
Posted By: lahorichick
Date 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?
|
Posted By: DBlank
Date 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( mailto:%7b@supress%7d%29=%7b@supress - {@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 mailto:%7b@Suppress - {@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
|
Posted By: lahorichick
Date 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 mailto:%7b@Dif - {@Dif Qty charge}
else Sum ({INVITEMS.QUANTITY}, {INVITEMS.DESCRIPTION}) Dif Qty charge
numberVar TotalchargeQty := mailto:%7b@Qty - {@Qty charge}-( mailto:%7b@Qty - {@Qty mailto:Wash%7d+%7b@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?
|
Posted By: lahorichick
Date 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)
|
Posted By: DBlank
Date 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? |
Posted By: lahorichick
Date 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...
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 12:48pm
|
did you test to make sure each of the parts were getting you the right amounts by placing each in the footer? {#charge}=316
{#wash}=170
{#shampoo}=118
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 12:49pm
|
sure did... |
Posted By: DBlank
Date Posted: 14 Aug 2009 at 12:51pm
|
Hmm.Try and add a Report FooterB and place the formula there. Does that work?
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 12:52pm
| nope |
Posted By: DBlank
Date Posted: 14 Aug 2009 at 1:03pm
|
Weird. I just tested that and the parenthesis are messing it up but they are not needed so try it without:
{#charge} - {#wash } + {#shampoo}
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 1:06pm
|
{#charge}-{#wash}+{#shampoo}
still get 316...ouff...NOPE
i tried...
{#charge}-{#wash}
= 316
i tired ...
{#charge}-{#shampoo}
= 316
strange??
its it because they are RTs?
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 1:13pm
|
Really weird. I use them in formulas without issue.
1. Are you typing them in our using the fields from the Field Explorer in the Formula workshop. They should be in there starting with a Sigma.
2. You have to use these in the level that they are caclulated at so if you are resettting the value at Group Level 1 it ahs to be used at in a formula that is going to be displayed in GF1 or if you reset to Never the formual has to bein the Report Footer display.
Any issues here?
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 1:20pm
|
1. sigma--good on this..
2. i have it resetting on group level 1...for all of them
what i noticed is..
since the formula is in the group1 footer
if i go to group1 footer for
a.) charge = 316
b.) wash = (163)---its showing up negative
c.) shampoo = 118
b.) is showing up negative no matter if or ifnot the '()' are there?--i have a feeling thats why it might be just coming up to 316? maybe?
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 1:38pm
|
Negative value seems to be messing it up my sandbox as well but I do not know why. Never had to use negative values like this. Can you invert that into a positive and then add all 3 together instead?
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 1:42pm
|
ok so its:
positivie now..
but all the values are the same..
its as though its indifferent...it only picks up the indivual #--doenst bother doing the math associated with it
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 1:52pm
|
Just to clarify to amke sure because this is stumping me. 1. You have 3 Running Total fields: (#charge, #wash and #shampoo)
2. All 3 are designed to Reset on Group1
3. all are placed on the Group Footer1 and display teh correct value for each.
4. You created another formula called Charge total as {#charge}-{#wash }+{#shampoo} and placed it on Group Footer1 and it only show the value from #wash.
Is all of this correct?
|
Posted By: lahorichick
Date Posted: 14 Aug 2009 at 1:56pm
|
ah almost.
1-3 perfect
4...that formula is in the Group Footer1--and when the group desciption for 'charge' comes it returns the charge value 316
when group description for 'wash' comes is returns 163
and last..when group description 'shampoo' comes it returns value 118---for all other grooup descriptions it returns a 0
so that formula in short is returning the value for the Group footer1---just like the 3 RTwere in the respective desp groups
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 1:59pm
|
Dohhh.
But you need #4 (your final summary) to be in the report Footer correct.
|
Posted By: DBlank
Date Posted: 14 Aug 2009 at 2:08pm
|
Assuming that is correct you need to change all of your RTs to Reset to NEVER. What is happening is it is working but it is subtracting or adding 0 because that is the value in the last group (whilereadingrecords) for both Wash and Shampoo.
If you want to display the values inthe Group Fotoers you can create other RTs for that (reset on Group1) but in order for you to use the total # of records then you need to all have them reset as Never.
Make sense?
|
Posted By: lahorichick
Date Posted: 17 Aug 2009 at 5:06am
|
ah...i would like the total for charge--in the group footer for charge..only..
so that formula:
charge - (wash + shampoo)= charge
i need for that to appear in the group footer for charge...so that i know that the total qty is for it
|
Posted By: lahorichick
Date Posted: 17 Aug 2009 at 5:57am
| is there no way to pass on the running total amounts for wash and shampoo and charge --so that they just dont turn into negatives..? (i hope that made sense) |
Posted By: lahorichick
Date Posted: 17 Aug 2009 at 6:19am
|
ok so I did what you said with changing the reset to never..it worked...
i'm going to make sure that the other #s didnt change in any way.
thank yOU |
Posted By: DBlank
Date Posted: 17 Aug 2009 at 7:10am
Glad you got it worked out 
|
Posted By: lahorichick
Date Posted: 17 Aug 2009 at 7:11am
| ya this took a while...thank you again for your help |