Stored Procedure Newbie Questions- Passing Parameters

[fredp1]

Hi All,

CR8.5, SQL2k

I've been using CR for about 2 years using tables and views. But I've never used a SP as a data source.

I have a performance issue with a report that needs a parameter passed to a subquery. Subreports are too slow, I don't believe its possible to use a view because of the parameter, so that leaves a SP.
the query is someting like

Select itemno, qty from tblItem t1 left outer join (select ItemNo, qty from tblTransaction t2 where trandate >=PARAMATERDate1 and trandate <=PARAMETERDate2) t3 on t1.itemno = t3.itemno

anyway..

1) How do you avoid for the SP promting for values. Ideally I want the std CR prompts to pass the values to the SP. How is this possible?

2) If a parameter has multi values, how do you pass it to a parameter in the SP. (I'm dreading that a csv string will need to be passed to the sp(assuming question 1 is possible), and then the sp will need to unpack the string. Does anyone have any example on how to do it?

How do you pass a multi value paramter in CR to the SP?
here is a test sp that I am learning on ...
Create sp_Test @minItem varchar(10), @minItem Varchar(10) as
select ItemNo from ItemMaster
where ItemNo>=@minItem and ItemNo<=@maxItem

Any other tips on SP's?
ta
Fred

 

[bdreed35]

Basically, you create the SP with input parameters. When you point Crystal to it as a datasource, Crystal will pick up the parameters and create parameters inside Crystal.

I don't think you will have the option to use a multi-value parameter since the parameters are created by crystal once you point to the SP datasource. I am not sure what to tell you as a work around since I don't have one off the top of my head.

How are you running the reports? If you are using something like CE, you can build the SP to handle the default values before you run the SQL statement.

Also, make sure that you are returning a dataset as the result of your SP, it won't work otherwise.

I hope that helps some even though it won't totally eleviate your problem.

~Brian

 

[lyanch]

You are right about the only way to send a multivalue parameter to a stored proc is to send it as a long string and unpack it. It isn't pretty and I have avoided using it. Most of the time you can get away with wildcards, or "you can pick up to X" having a parameter for each choice (nulls allowed of course). Brian is right about the parameters.. you can add your own description etc. If the parameters are in a subreport, you can link them to parameter values or fields in the main report, just like any other link.

Lisa

 

[fredp1]

Hi Brian/Lisa,
(
Brian)
I am running CE 8.5 so I am curious on how you picked up the defaults. Any samples you can point to would be great!
can you expand on it.

(Lisa)
I was afraid that you would agree that you would have to pack/unpack the values manualy.
As you said, If you add the SP in a SUBRPORT you can pass the CR paramaters to the SP paramaters in the Subreport.
This way I can then send a packed string to the SP.

I got put off in 8.5 beacuse I couldn't 'see' how to link to the paramater in the subreport. (It works fine in 9)
After talking to techsupport, The scroll box arrows are very small and you can easily miss it as I did, but you can link a field/prompt of the main report to the paramater in the subreport.

Also this KB goes through the steps.

http://support.businessobjects.com/library/kbase/articles/c2006697.asp

Thanks for confirming what may need to be done.
Fred
Sorry for the time delay, but i'm in Sydney so I don't see the responses until your next day.

Cheers