Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Report help

Posted on 2014-02-20
6
Medium Priority
?
633 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 101

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 750 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 101

Assisted Solution

by:mlmcc
mlmcc earned 750 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…

722 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