[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Better way for Date comparison to Database to Bring Back CurrentDate-1 Crystal Reports

Still working with old version of Crystal 7.
Using Select Expert  record selection - {soinvdate.invoice_date]=CurrentDate-1
to report invoices from the previous day. It works put the database contains many records and the impact on the database is significant.

 I need to find a better way to push the date comparison to the database so that it only brings back the one day’s worth of data using crystal. This report is emailed to many.

 How could this be accomplished?
0
trilian
Asked:
trilian
  • 5
  • 4
  • 4
  • +2
2 Solutions
 
peter57rCommented:
I think I'm a bit surprised if Crystal (even V7) does not send that selection rule to the database.

Have you checked the sql being created (database menu) ?
0
 
vastoCommented:
If there is a way for crystal to convert CurrentDate to the database function ( for example GetDate() for SQLServer) it will send the record selection formula as a where clause. Otherwise it will download all the records and filter them locally, which will cause report to be slow.
You have to choices :
1. Move the data retrieval to the database by creating a view or stored procedure and filter the data there. Then you will be able to set a where clause using the database function for current date and the data returned to crystal will be filtered on the database side.
2. Add a parameter date and change your record selection formula to {soinvdate.invoice_date]=@Date. Then Crystal will include this in a where clause and send it to the database and filtering will be done again on the database side.

What is your database type ?
0
 
peter57rCommented:
vasto:
I would have expected Crystal to convert Currentdate-1 to an actual date rather than look for an equivalent Sql server (or whatever) function.
0
Industry Leaders: 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!

 
trilianAuthor Commented:
I did check the sql and the record selection was not listed... not sure why. Old version?

I tried option #2 from Vasto but I still don't see it listed in the sql where clause.

I am not up to speed on trying out option #1.  I know the basics any addtional help would be appreciated on how to set this up.
0
 
vastoCommented:
peter57 - yes this makes sence. However I am not sure Crystal will be able to calculate an expression like CurrentDate -1

trilian - what is the database type ?
what happened when you ran the report with a date parameter ?
0
 
vastoCommented:
BTW can you try  to change the record selction formula to
{soinvdate.invoice_date] < CurrentDate

Then there will be no expression and as peter57 mentioned CurrentDate will be replaced with the actual date value and filter will be applied on the database side.
0
 
mlmccCommented:
CurrentDate - 1 should be calculated before the records are read so it shoul get passed to the database.

http://www.kenhamady.com/news1205.shtml
Scroll down to the section  Using formula fields and calculations in the selection formula

WHat else is int he selection formula?

Are the date fields date or datetime?

Is the selection of records running slowly?

mlmcc
0
 
trilianAuthor Commented:
It should work. I agree.

{SALES_ORDER_INVOICE_LINES.SALES_PRICE} > 0 and
{SALES_INVOICE_LINE_QTYS_COST.UNIT_OF_MEASURE} in ["EA", "ROLL"] and
not ({@Part} in ["3PBFC","INTHANDLING",  "HANDLING", "MBLFEE"]) and
{CUSTOMER_ADDRESSES.SALESPERSON_CODE} = {?SalesCode} and
{SALES_ORDER_INVOICES.INVOICE_DATE} ={@Date}     With the change

Date fields are Date

Selection does run slow.
0
 
vastoCommented:
Crystal converts IN to OR whenever this is possible. However I don't know when this was introduced. Try to use OR instead of IN to check if this is the reason:

{SALES_ORDER_INVOICE_LINES.SALES_PRICE} > 0 and
(
  {SALES_INVOICE_LINE_QTYS_COST.UNIT_OF_MEASURE} = "EA"
  OR {SALES_INVOICE_LINE_QTYS_COST.UNIT_OF_MEASURE} = "ROLL"
)
and not
(
  {@Part} = "3PBFC"
  OR {@Part} = "INTHANDLING"
  OR {@Part} = "HANDLING"
  OR {@Part} = "MBLFEE"]
) and
{CUSTOMER_ADDRESSES.SALESPERSON_CODE} = {?SalesCode} and
{SALES_ORDER_INVOICES.INVOICE_DATE} ={@Date}
0
 
mlmccCommented:
What is the @Date formula?

Can you display the SQL the report uses?
Check DATABASE --> SQL

mlmcc
0
 
trilianAuthor Commented:
@Date CURRENTDATE-1

I don't find the date getting passed to the Where clause in the SQL

SELECT
    SALES_ORDER_INVOICES."INVOICE_NUMBER", SALES_ORDER_INVOICES."INVOICE_DATE", SALES_ORDER_INVOICES."CUSTOMER_NUMBER", SALES_ORDER_INVOICES."DELIVERY_ADDRESS_CODE",
    SALES_ORDER_INVOICE_LINES."PART_CODE", SALES_ORDER_INVOICE_LINES."SALES_PRICE", SALES_ORDER_INVOICE_LINES."UNIT_COST", SALES_ORDER_INVOICE_LINES."LINE_DISCOUNT",
    CUSTOMER_ADDRESSES."CUSTOMER_NAME", CUSTOMER_ADDRESSES."SHIP_METHOD", CUSTOMER_ADDRESSES."SALESPERSON_CODE", CUSTOMER_ADDRESSES."SHIPPING_CHARGES",
    SALES_INVOICE_LINE_QTYS."UNIT_OF_MEASURE", SALES_INVOICE_LINE_QTYS."INVOICE_QUANTITY",
    SALESPERSONS."CODE_DESCRIPTION", SALESPERSONS."SALESPERSON_CODE",
    PRODUCT_MASTER."PART_DESC_1", PRODUCT_MASTER."PRODUCT_DISCOUNT_GROUP",
    CUSTOMERS."SYSTEM_DATE", CUSTOMERS."HARDY_CDATE",
    SALES_INVOICE_LINE_QTYS_COST."UNIT_OF_MEASURE", SALES_INVOICE_LINE_QTYS_COST."INVOICE_QUANTITY"
FROM
    "FPROD"."SALES_ORDER_INVOICES" SALES_ORDER_INVOICES,
    "FPROD"."SALES_ORDER_INVOICE_LINES" SALES_ORDER_INVOICE_LINES,
    "FPROD"."CUSTOMER_ADDRESSES" CUSTOMER_ADDRESSES,
    "FPROD"."SALES_INVOICE_LINE_QTYS" SALES_INVOICE_LINE_QTYS,
    "FPROD"."SALESPERSONS" SALESPERSONS,
    "FPROD"."PRODUCT_MASTER" PRODUCT_MASTER,
    "FPROD"."CUSTOMERS" CUSTOMERS,
    "FPROD"."SALES_INVOICE_LINE_QTYS" SALES_INVOICE_LINE_QTYS_COST
WHERE
    SALES_ORDER_INVOICES."COMPANY_CODE" = SALES_ORDER_INVOICE_LINES."COMPANY_CODE" AND
    SALES_ORDER_INVOICES."DIVISION" = SALES_ORDER_INVOICE_LINES."DIVISION" AND
    SALES_ORDER_INVOICES."INVOICE_NUMBER" = SALES_ORDER_INVOICE_LINES."INVOICE_NUMBER" AND
    SALES_ORDER_INVOICES."COMPANY_CODE" = CUSTOMER_ADDRESSES."COMPANY_CODE" AND
    SALES_ORDER_INVOICES."DIVISION" = CUSTOMER_ADDRESSES."DIVISION" AND
    SALES_ORDER_INVOICES."CUSTOMER_NUMBER" = CUSTOMER_ADDRESSES."CUSTOMER_NUMBER" AND
    SALES_ORDER_INVOICES."DELIVERY_ADDRESS_CODE" = CUSTOMER_ADDRESSES."ADDRESS_CODE" AND
    SALES_ORDER_INVOICE_LINES."COMPANY_CODE" = SALES_INVOICE_LINE_QTYS."COMPANY_CODE" AND
    SALES_ORDER_INVOICE_LINES."DIVISION" = SALES_INVOICE_LINE_QTYS."DIVISION" AND
    SALES_ORDER_INVOICE_LINES."INVOICE_NUMBER" = SALES_INVOICE_LINE_QTYS."INVOICE_NUMBER" AND
    SALES_ORDER_INVOICE_LINES."INVOICE_LINE_NUMBER" = SALES_INVOICE_LINE_QTYS."INVOICE_LINE_NUMBER" AND
    SALES_ORDER_INVOICE_LINES."UNIT_PRICE" = SALES_INVOICE_LINE_QTYS."UNIT_OF_MEASURE" AND
    CUSTOMER_ADDRESSES."COMPANY_CODE" = SALESPERSONS."COMPANY_CODE" (+) AND
    CUSTOMER_ADDRESSES."SALESPERSON_CODE" = SALESPERSONS."SALESPERSON_CODE" (+) AND
    SALES_ORDER_INVOICE_LINES."COMPANY_CODE" = PRODUCT_MASTER."COMPANY_CODE" (+) AND
    SALES_ORDER_INVOICE_LINES."PART_CODE" = PRODUCT_MASTER."PART_CODE" (+) AND
    CUSTOMER_ADDRESSES."COMPANY_CODE" = CUSTOMERS."COMPANY_CODE" AND
    CUSTOMER_ADDRESSES."DIVISION" = CUSTOMERS."DIVISION" AND
    CUSTOMER_ADDRESSES."CUSTOMER_NUMBER" = CUSTOMERS."CUSTOMER_NUMBER" AND
    SALES_ORDER_INVOICE_LINES."COMPANY_CODE" = SALES_INVOICE_LINE_QTYS_COST."COMPANY_CODE" AND
    SALES_ORDER_INVOICE_LINES."DIVISION" = SALES_INVOICE_LINE_QTYS_COST."DIVISION" AND
    SALES_ORDER_INVOICE_LINES."INVOICE_NUMBER" = SALES_INVOICE_LINE_QTYS_COST."INVOICE_NUMBER" AND
    SALES_ORDER_INVOICE_LINES."INVOICE_LINE_NUMBER" = SALES_INVOICE_LINE_QTYS_COST."INVOICE_LINE_NUMBER" AND
    SALES_ORDER_INVOICE_LINES."SALES_PRICE" > 0. AND
    (SALES_INVOICE_LINE_QTYS_COST."UNIT_OF_MEASURE" = 'ROLL' OR
    SALES_INVOICE_LINE_QTYS_COST."UNIT_OF_MEASURE" = 'EA') AND
    CUSTOMER_ADDRESSES."SALESPERSON_CODE" = 'CS2'
0
 
mlmccCommented:
Did you select tables as the data source?

mlmcc
0
 
mlmccCommented:
Try moving the date filter so it is earlier in the selection.  I seem to remember that early versions of Crystal stopped passing the filter on the first selection that couldn't be passed.

Try this one

{SALES_ORDER_INVOICE_LINES.SALES_PRICE} > 0 and
{SALES_INVOICE_LINE_QTYS_COST.UNIT_OF_MEASURE} in ["EA", "ROLL"]  and
{SALES_ORDER_INVOICES.INVOICE_DATE} ={@Date}  and
{CUSTOMER_ADDRESSES.SALESPERSON_CODE} = {?SalesCode}   and
not ({@Part} in ["3PBFC","INTHANDLING",  "HANDLING", "MBLFEE"])

mlmcc
0
 
James0628Commented:
If @Date is just CurrentDate - 1, try replacing {@Date} in the selection formula with (CurrentDate - 1).  Maybe that version of CR is not passing the calculated date because it's the result of a formula.  It's just a shot in the dark, but it's easy enough to try.

 James
0
 
trilianAuthor Commented:
To James --that is what I  started with and it didn't work

To mlmcc- I had the date in a different sequence at one time, but used your suggestion and it still does not appear in the sql

How would I write {SALES_ORDER_INVOICES.INVOICE_DATE} ={@Date}  in sql or
{SALES_ORDER_INVOICES.INVOICE_DATE} = CURRENTDATE-1   ?
0
 
mlmccCommented:
IN SQL it would be

SALES_ORDER_INVOICES.INVOICE_DATE = GetDate() - 1

mlmcc
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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