Print Page | Close Window

CR crashes with 3 tables and no results with 2

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=6857
Printed Date: 28 Apr 2024 at 1:41am


Topic: CR crashes with 3 tables and no results with 2
Posted By: katie1200
Subject: CR crashes with 3 tables and no results with 2
Date Posted: 25 Jun 2009 at 5:01am
hi all,
 
i am having a problem (one of many lol) i will try my best to explain in detail.
 
i have to make a sales report (CRXI) with individual product sales totals. now the problem arises when sales reps discount a product to €0.00 so it can be given as free stock. 
this is throwing my sales totals out saying they have sold more than they have. i am adding it by quantity because the cash figure is not what is required.
 
database example:
 
quantity      item          rep      customer        price
 
      3           milk           bob          1111            23
      6           wine          gill           2221            45 
      3           milk          paul          3333              5
      1           pen          paul          3333              0
 
i have used the select expert and tried to return values
if(price is not equal to 0) 
i have also tried setting it between values and greater than but i get then same result.
 
any products that had a sale set to zero are not showing up in the grouping on the side. They are grouped by rep then by product description then by item code. When i take out that statement the product reappears in the grouping but the sales total is wrong.
 
i thought maybe there was a problem with the field so i triedadding another table with a cost field that would also be set to zero and when i try to do anything it just crashes crystal.
i have looked at how i link the tables and they are all linked by the same key which is product code and this is a primary key in two of the tables and just a field in another!
 
please help!!
 
sorry for the long post i thought it best to be descriptive, thanks again for any help given.
 
Katie.
 
 
 
 


Replies:
Posted By: AdamField
Date Posted: 25 Jun 2009 at 6:11am

Hey Katie,

Don't realy get the problem as in what you are count and what is not beying counted (maybe some later reply from somebody else is more usefull)
For now i can only sudject to change the "zero" value's to value + 0.00001 for example
this is a very small impact in your totals and witch a 0.xx max value behind the 0. it will not be visual unless you have 500+ sales of that article.
 
hope this helps a bit.
 
Greetz
 
Adam

Posted By: katie1200
Date Posted: 25 Jun 2009 at 6:20am
hi adam,
 
i am counting the quantity amount but with every transaction be it a normal sale for cash or a zero sale for free stock there is always a quantity so i need to only count the actual sales as oppesed to evry transaction.
i cant change any figures in the data base there are hundreds of sales every day and the prices are set so i have no access to them.
 
thanks for input

Posted By: lockwelle
Date Posted: 25 Jun 2009 at 6:25am

Why are you selecting based on price?  You want entries where the price is 0 don't you?  The report must be comparing to some other field to be able to say that you sold more than you have.

Wouldn't this be an issue of looking at quantities and not at price?

Posted By: AdamField
Date Posted: 25 Jun 2009 at 6:29am
Hey Katie,
 
so if i understand correct you have somthing like
 
article   /    amount on invoice /   cost / customer /  rep
 
and you want to count the "amount on invoice" for every article where the cost is <> from 0 ?
 
Adam

Posted By: katie1200
Date Posted: 25 Jun 2009 at 6:39am
yes when "amount on invoice" in my problem is called quantity e.g. a crate of beer. the report would total how many crates of beer were sold not the cash price of how much beer was sold.
 

Posted By: AdamField
Date Posted: 25 Jun 2009 at 6:46am
Hey Katie,
 
sorry but i'm not completly with you
 
could you make a small example with like above some numbers and articles and what you get now and what you would like to get ?
so i exatly see what totals you get now and what you would like to get.
 
My english isn't that great that's why i'm not 100% what you now get and where you want to get to.
 
greets
 
Adam

Posted By: katie1200
Date Posted: 25 Jun 2009 at 7:24am
before i use price <> 0
 
i get this
 
 
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
 
after i use price is <> 0
 
i get
 
      3           milk           bob          1111            23
      6           wine          gill           2221            45 
      3           milk          paul          4534              5
 
the pen sale just disappears!
 
thank you for trying to help especially if you dont have great english.

Posted By: AdamField
Date Posted: 25 Jun 2009 at 7:47am
Hey Katie,
 
No problem @ all
 
the price <> 0 are you doing this on the detail lvl ?
normaly as far as i know this can't be happening if you do the
price <> 0 on the sectie formula / record
 
      3           milk           bob          1111            23
      6           wine          gill           2221            45 
      3           milk          paul          4534              5
      1           pen          paul          3333              6
 
This is what you should get if you do price <> 0 on the record lvl
if you do this on the group lvl there is somthing else wrong
 
Greets
 
Adam

Posted By: DBlank
Date Posted: 25 Jun 2009 at 8:05am
I'll throw my two cents in.
I'll hazard a guess that when you use the select statement of price <>0 it is either making a left join into an inner join or dropping extra values from an extra link somewhere along the line. I doubt it is coincidenta that your item and customer are the same for oth items that were dropped.
 
That being said you should be able to easily get your numbers using Running Totals that conditionally count or sum your fields where table.price <>0 without worrying about dropping records via the select statement.

Posted By: katie1200
Date 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....

Posted By: katie1200
Date 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

Posted By: DBlank
Date 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.

Posted By: katie1200
Date 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.
 
 

Posted By: lockwelle
Date 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.


Print Page | Close Window