Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Pass Parameter to SQL or eliminate SQL altogether Post Reply Post New Topic
Author Message
carstowal
Groupie
Groupie


Joined: 31 Jul 2008
Online Status: Offline
Posts: 80
Quote carstowal Replybullet Topic: Pass Parameter to SQL or eliminate SQL altogether
    Posted: 19 May 2009 at 7:52am
Parameter: {?CurrInvYear}   Example value = 2008

SQL Command:
SELECT     PART_ID, UNIT_PRICE, PURC_ORDER_ID, ORDER_QTY, LAST_RECEIVED_DATE, PURCHASE_UM
FROM         dbo.PURC_ORDER_LINE po
WHERE     (LAST_RECEIVED_DATE =
                          (SELECT     MAX(po1.LAST_RECEIVED_DATE)
                            FROM          PURC_ORDER_LINE AS po1
                            WHERE      po1.PART_ID = po.PART_ID AND LAST_RECEIVED_DATE < '01/01/ 2008'))


Can I pass the Parameter to the last line of the SQL Command instead of hard coding 01/01/2008

Like:
AND LAST_RECEIVED_DATE < '01/01/ ‘ & {?CurrInvYear}   ))


OR is there a way to return the max LAST_RECEIVED_DATE < '01/01/ ‘ & {?CurrInvYear}  without the use of a SQL Command?
This report takes 80mins to run because of the SQL command

I can't select for this because one field is the last date prior to {?CurrInvYear}  or 2008 and another is the last date prior to {?CurrInvYear} -1  or 2007

IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 19 May 2009 at 10:01pm
try Year(LAST_RECEIVED_DATE ) = {?CurrInvYear} 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 20 May 2009 at 7:02am
does it take that long using SQL server?  if so, nothing is going to help. 
 
I would suggest using a stored proc, at least you can time how long it takes to execute and the parameter that you enter can be passed to the stored proc.  I could be wrong, but this is how crystal was designed to work...I don't think that they meant for the report to get all of its data from a command.
 
last, depending on what else is happening, the report may be getting an abundance of information and is taking a long time to format the report...the actual data may only be a short time, but formatting, grouping, subreports (especially subreports) may be taking longer.
 
last...ok really this time...
SELECT   MAX(po1.LAST_RECEIVED_DATE)LastRecieved,  PART_ID, UNIT_PRICE, PURC_ORDER_ID, ORDER_QTY, LAST_RECEIVED_DATE, PURCHASE_UM
FROM         dbo.PURC_ORDER_LINE po
WHERE  LAST_RECEIVED_DATE < '01/01/ 2008'
GROUP BY Part_id, last_received_date, unit_price, purc_order, order_qty, purchase_um
 
 
OR
SELECT     PART_ID, UNIT_PRICE, PURC_ORDER_ID, ORDER_QTY, LAST_RECEIVED_DATE, PURCHASE_UM
FROM         dbo.PURC_ORDER_LINE po
 JOIN (SELECT     MAX(po1.LAST_RECEIVED_DATE) AS MaxDate, PART_ID 
                            FROM          PURC_ORDER_LINE AS po1
                            WHERE      LAST_RECEIVED_DATE < '01/01/ 2008')
                           GROUP BY Part_ID) po1 ON po1.part_id=po.part_id and po.LAST_RECEIVED_DATE = po1.MaxDate
 
Hope one of these ideas helps you out.
                         
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.