Solved

Crystal Report help

Posted on 2014-02-20
6
624 Views
Last Modified: 2014-03-07
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
Comment
Question by:wallis34
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39874982
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39875130
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
 

Author Comment

by:wallis34
ID: 39875139
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 39875174
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39875182
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
 

Author Closing Comment

by:wallis34
ID: 39913408
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need to Understand Resolution to Oracle Error ORA-00600 2 53
Query - Duplicate dates with different activities counts 10 58
construct a query sql 11 43
oracle query 4 31
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question