Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: CR crashes with 3 tables and no results with 2 Post Reply Post New Topic
<< Prev Page  of 2
Author Message
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 25 Jun 2009 at 8:06am
i am using select expert, here's my code
 
{TRANS.PERIODCODE} >= 200901.00 and
{TRANS.DATE} in {?start date} to {?end date} and
{TRANS.TRTYPE} = {?invoice (i) or credit (c) report} and
{TRANS.COSTPRICE} <> 0.00
 
i have it grouped by:
 
sales person,
          product description,
                      product number,
                                 product number.
 
i use product number twice to get the summary for the quantity total.
 
but when i use the code above some of the products do not appear in the grouping on the left side of crystal.
basically any products that have a 0 costprice value are gone!
 
if i use
 
{TRANS.PERIODCODE} >= 200901.00 and
{TRANS.DATE} in {?start date} to {?end date} and
{TRANS.TRTYPE} = {?invoice (i) or credit (c) report}
 
all the products appear
 
im at this for two days this is insane it cant be that hard....


Edited by katie1200 - 25 Jun 2009 at 8:07am
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 25 Jun 2009 at 8:19am
hi DBlank and Lockwelle
 
i have only seen your posts now
 
lockwelle
 
what i want to do is forget any values where price = 0
 
quantity      price       customer      sales rep
     3                4               r                    john
     2                3               s                   paul
     4                5               t                    mark
      1                0               t                   mark
 
i want quantity total of sales what i am getting is either the quantity total for:
 
 
quantity      price       customer      sales rep
     3                4               r                    john
     2                3               s                   paul
     4                5               t                    mark
      1                0               t                   mark
 
total would be 10
 
or
 
quantity      price       customer      sales rep
     3                4               r                    john
     2                3               s                   paul
 
total would be 5
 
and i need
 
quantity      price       customer      sales rep
     3                4               r                    john
     2                3               s                   paul
     4                5               t                    mark
 
i need total to be 9
 
 
 
 
DBlank i am not sure about inner or left joins as im only a noob
 
but i am going to look into running totals for this i tried that originally and had different problems.
 
thanks again for all the help guys this is a great site a credit to the helpfull members


Edited by katie1200 - 25 Jun 2009 at 8:20am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jun 2009 at 8:19am

IN your sample:

quantity      item          rep      customer        price
 
      3           milk           bob          1111            23
      6           wine          gill           2221            45 
      3           milk          paul          4534              5
      1           pen          paul          3333              0
      1           pen          paul          3333              6
is the PRICE column the actual trans.costprice field or is it something like table.salesprice?
I am guesiing that there is a field that is updated to show current cost (costprice) and a differnt field that shows the price it was sold at. If you are filtering on the costprice <>0 then all sales of those items would be excluded (as well as including items that were once discounted to 0 but are not currently). You would need to be filtering on the actual customers price field <>0 to avoid the problem you have described.
Hoep this helps.


Edited by DBlank - 25 Jun 2009 at 8:22am
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 25 Jun 2009 at 8:27am
i will look into that to DBlank
im just finished work now my head is melted with this, 2days like!
 
price is the actual price the shops get the product at so it will either be the set price or 0 for  a particular product. it is the price field i am filtering on.
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 25 Jun 2009 at 2:34pm
Sorry, I misunderstood the issue.  Do you have access to the database and can you write a query?  This might enlighten you as to where things are going wrong....You're probably right, it is really simple, just as DBlank says there is probably some other field that is causing the issue.
IP IP Logged
<< Prev Page  of 2
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.