Solved

Crystal Report help

Posted on 2014-02-20
6
606 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now