Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

Crystal Report help

I have to get the combined number of certain part numbers shipped in a given date range, Using CR 8.5 and Oracle 8i database. attached is the SQL, what i am getting is totals that are way higher than what it should be. I have had to create 4 separate reports also, because i could not figure out how to get data for 1 group of parts and then the other 3 groups in the same report,  what the sales Exec. wants is a report with all 4 groups of parts on it, but that is the lesser issue, main thing is getting accurate totals.
SQL-Fixtures-Total-Sales-Report.doc
0
wallis34
Asked:
wallis34
  • 2
  • 2
  • 2
2 Solutions
 
Kurt ReinhardtCommented:
In general, the SQL seems fine, but one thing that sticks out is this:

CUST_ORDER_LINE."PART_ID" LIKE '%00WLW%' OR
    CUST_ORDER_LINE."PART_ID" LIKE '%00WLS%' OR
    CUST_ORDER_LINE."PART_ID" LIKE '%00WLB%' OR
    CUST_ORDER_LINE."PART_ID" LIKE '%00W%' OR

Open in new window


%00W% already captures the first three arguments.  It will also capture anything else with 00W in it, like %00WCB% (as an example).  So, you could be returning additional values you don't need based on this redundancy.

Other than that, everything looks pretty straightforward.  If the issue isn't caused by redundant statements then I would assume there's an issue with your data.

Also, how was this SQL generated and how are you accessing it?  It's not a SQL Command, because that feature didn't become available until Crystal Reports 9.  It's not horrific standard Crystal formatting, because it looks too clean. Did you go into Database|Show SQL Query and modify the SQL?  Are there any record selection filters that don't exist in the WHERE clause of the SQL?  Does the report point to a SQL View as a data source or a .qry file created with the Crystal Reports SQL Editor?
0
 
mlmccCommented:
If you are looking for parts shipped in a certain date range, why are you filtering on the LAST SHIPPED DATE field?  Seems you would be filtering on the shipped date field.

Does the report show the details or are just showing summary data?
If it is just summary it might pay to put the details on to see if you are getting duplicates or data you don't want.

mlmcc
0
 
wallis34Author Commented:
so using the wildcards , would this query count the amounts shipped multiple times like it would look at all parts shipped that are like *00W*", and then add up all the parts again that are like"*00WLB*", and then like "*00WLS*", and "*00WLW*". it just occurred to me i should he looking for distinct records, would that not deal with this issue? if so what is the syntax to add that to the record selection formula?

to answer your questions:

Also, how was this SQL generated and how are you accessing it? (go into Database|Show SQL Query) It's not a SQL Command, because that feature didn't become available until Crystal Reports 9.  It's not horrific standard Crystal formatting, because it looks too clean. Did you go into Database|Show SQL Query and modify the SQL?  (No) Are there any record selection filters that don't exist in the WHERE clause of the SQL?  (I don't think so) Does the report point to a SQL View as a data source or a .qry file created with the Crystal Reports SQL Editor? (no i am just getting the info all from the sysadm.cust_order_line table )
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Kurt ReinhardtCommented:
With the wildcards, it shouldn't duplicate records that meet the same criteria.  What will happen, however, is it may include records you don't want.  Why use just 00W if you're going to be more granular with other 00W records?

no i am just getting the info all from the sysadm.cust_order_line tabl

Did you just copy the SQL out of Database|Show SQL Query and format it so it was easy for us to read? It's normally formatted much worse.  Is there a "restore", "revert" or "default" button visible in Database|Show SQL Query?
0
 
mlmccCommented:
The record selection wouldn't cause a record to be added twice but the report could be running slower because it does 3 tests that really aren't necessary.

I have seen instances with Crystal and long OR filters that have funny results.  I haven't encountered any recently but I do recall have issues with the long OR filters in earlier versions

mlmcc
0
 
wallis34Author Commented:
Thanks for the feedback, i found i was using the wrong field, so i will split the points, thanks again for your help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now