Solved

Crystal Report help

Posted on 2014-02-20
6
612 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports 2008 6 20
Oracle Next Available Number 2 30
Visual Studio .Net 2012 and Crystal Reports 9 44
Crystal report highlighting 5 28
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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

932 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

10 Experts available now in Live!

Get 1:1 Help Now