Passing Paremeters - Best Practice

[ideafixer]

Currently I have a SQL 2000 database with a view set up. I have a Crystal 8 report hooked to that view. My View contains a large amount of data ( greater than 10000 recs) and I want to pull specific Job Ids out of the data.

Right now I am using the selection expert to get these job IDs. It appears the report reads every record in the table then determines if the job ID is in the table. This processing takes some time.

What is the best way to query for just a specific job ID. I want the user to have the ability to enter different Job IDs on the fly when the report is run.

 

[synapsevampire]

Check that the SQL is being passed to the database by selecting: Database->View SQL Query.

If you don't see the list of jod ID's referenced, than you'll need to redesign the formula for selecting job ID's.

It would be best to post what you have in the selection expert:

Report->Edit Selection Formula->Record

Make sure that you don't reference any formulas which have a variable in them, and I would suggest that you build it manually rather than use the expert.

It should be something like:

{table.JobID} = {?JobIDParm}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ideafixer]

That's what I did. It looks as if the report reads all of the records to determine what records match the selection criteria. I was thinking passing a parameter to a stored procedure or something may be faster.

 

[synapsevampire]

A stored procedure will be faster.

Post the SQL currently being generated, and what you have in the selection expert.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ideafixer]

SQL in my view:

SELECT CAST(SUBSTRING(dbo.JobMessageLog.Message, CHARINDEX('-', dbo.JobMessageLog.Message) + 2, 254) AS Varchar(254)) AS Message,
dbo.JobMessageLog.JobMessageID, dbo.JobMessageLog.JobID, dbo.JobMessageLog.MasterID, dbo.JobMessageLog.Severity,
dbo.JobMessageLog.CreateDate, dbo.vw_AnnuityDataPRE.AnnuityMasterID, dbo.vw_AnnuityDataPRE.ClientCode,
dbo.vw_AnnuityDataPRE.CountryCode, dbo.vw_AnnuityDataPRE.CaseTypeCode, dbo.vw_AnnuityDataPRE.RelationTypeCode,
dbo.vw_AnnuityDataPRE.FilingTypeCode, dbo.vw_AnnuityDataPRE.StatusCode, dbo.vw_AnnuityDataPRE.FilingNumber,
dbo.vw_AnnuityDataPRE.DocketNumber
FROM dbo.JobMessageLog LEFT OUTER JOIN
dbo.vw_AnnuityDataPRE ON dbo.JobMessageLog.MasterID = dbo.vw_AnnuityDataPRE.AnnuityMasterID

------------------------------------------------------------
What I have in selection expert:

{_VW_REPORT_Import_Errors.JobID} = {?GetJobID}

 

[synapsevampire]

Note that there is no Where clause being generated, so as you mentioned, all rows in the views are being processed by Crystal. Had you entered something in to the parameter when you were prompted?

Try adding the column _VW_REPORT_Import_Errors.JobID to the columns being Selected. It's odd that your select doesn't show the column that your Where references...I would think that it would be in both places.

Also, make sure they're both the same data types.

If all else fails:

Try creating a report with just the _VW_REPORT_Import_Errors View (I assume that it's a View), select just the job_id column in the select and use the same record selection criteria. It should pass it to the database in the form of a Where clause.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jbuono]

ideafixer,

I have attached a stored procedure that you can use as a template for an earlier project I worked on... the view is processed on the server where the data resides. And only a subset of data is sent back. In this example the data input was a specific date called date_str and used as a parameter sent from the report to the sql server.

if
exists
(
select * from SysObjects
where ID = Object_ID('spFullCaseNotPick')
and ObjectProperty(ID, 'IsProcedure') = 1
)

begin
drop procedure spFullCaseNotPick
end
go

create procedure spFullCaseNotPick

@Date_Str DateTime
with Encryption
as
set NoCount on

declare
@Date_From DateTime

if @Date_Str is null
set @Date_Str = GetDate()
set @Date_Str = Floor(Convert(Float,@Date_Str))
set @Date_From = @Date_Str + Convert(DateTime, '12:00:00')


select distinct
m.control_number,
m.tracking_number,
m.status,
m.reserved_for,
d.item_number,
hj.date_stamp,
hj.exp_status

from
t_hu_master m,
t_hu_detail d,
t_order o,
EXP_WGHT_HJ hj

where
o.target_ship_date = @Date_From and
m.load_id = o.order_number and
m.hu_id = d.hu_id and
m.tracking_number *= hj.tracking_num and
m.tote_id is NOT null and
m.status not in ('C', 'S') and
m.repack_flag = 'N' and
m.actual_qty <> d.planned_qty

order by
m.control_number

SuccessProc:
return 0 /* success */

ErrorProc:
return 1 /* failure */

go

grant execute on spFullCaseNotPick to Public
go


if questions fee free to e-mail me.
Jim