Passing multiple values to single parameter field in stored procedure

[mendez]

I'm using CR8 with an MSSQL7 database. My users want to be able to enter 0, 1, or Many account_codes as parameters for the report. I can accomplish this with a View, but the report is then too slow, so I want to use a stored procedure. I've tried having a huge nVarchar field in the proc, hoping that I could strip the character string, but CR8 only allows me to enter 1 value for each proc parameter.
How do I get CR8 to allow me to assign multiple values to this parameter as it would allow with a View?

 

[Naith]

Mendez,

You can't alter a parameter to have multiple choices in a stored procedure.

But if you have already set up the stored procedure to expect a huge Varchar string which will be manipulated anyway, then you wouldn't use multiple parameters for this. From what you are saying, the stored procedure seems to be expecting a single string to populate the parameter, from which it can strip out values if there is a common separator. This must be true when you are executing the stored procedure without Crystal, mustn't it? Crystal will be able to mimic the prompts exactly as you are controlling them in your database client.

So, in your single value parameter, if you entered "360*380*390", stripping out the * in your stored procedure, you could replicate multiple values. If this is not how you are suggesting multiple values in your stored procedure, then it might be helpful knowing how you are attempting to do it.

Naith

 

[mendez]

Hi Naith.
Yes, I will be stripping the values from the parameter. Currently, I have set the proc to accept the values, then generate a text file containing the values that I intend to strip, hoping that I can then analyse the text file data and determine my delimiter. Unfortunately, when I point Crystal Reports at the proc, it defaults to allowing only one entry for each unique parameter that the proc expects. The usual functionality in CR that "would" allow me to set this parameter to allow multiple values is greyed out. If I could get CR to allow multiple values for a proc parameter, I think I would be OK. Unless there is another way?

 

[Naith]

When you execute your procedure in your database client, show me how you're supplying multiple values prior to the strip.

 

[mendez]

Stripping the values is not a problem, I just can't supply the multiple values. Crystal Reports will not allow multiple values to be entered-for/supplied-to any one parameter in a stored proc.

The only solution I can see at the moment is to allow the users to enter 1 long string for the parameter (eg, "AAAA112002, AAAB112002, AAAC112002, AAAD112002&quot. This works, however, I doubt the users will go for it because of how prone to errors it would be. I need to be able to enter a value, click ADD, and so on.

 

[Naith]

The long string is exactly how I imagined you would be entering the parameter in the stored procedure, and then stripping it.

Basically, the way I see it, there are two ways you can build your stored procedure:

(a) You have a parameter which is set to, I don't know, varchar2(500) which allows the operator to enter "X Y Z".
You then use a cursor to split the parameter at the spaces and handle X, Y, and Z as individual values.

This approach is exactly the same kind of thing as you mention above, but it wouldn't just be Crystal that the parameter would be entered like this - you would have to enter it like this in the stored procedure too, regardless of whether or not Crystal was in the frame.

(b) You build the stored procedure to have, again - I don't know, let's say 10 parameters. All 10 have a default value of null. You set as many of the 10 as you need to have values, the rest aren't evaluated. Again, this is treated the same as if you ran the stored procedure directly against the database client yourself, compared to how Crystal would use the prompts.

If your stored procedure uses another way (because I can't see any other way how you would otherwise handle multiple values in a single parameter in a stored procedure), that's why I asked you to post it, so that I could then pose what I think would be the best way for Crystal to mimic your stored procedure behaviour.

Naith

 

[Naith]

Alternatively, if you aren't using a technique other than those above, there's a fail safe algorithm I can show you which will "allow" multiple values in a stored procedure, if you know the number of account codes you have, and if the codes are static.

 

[mendez]

Thanks Naith but, as I've been trying to explain, the proc is fine. I have no problem with the proc. I can exec the proc from the Query Analyser window and get exactly what I expect. What I need though, is to be able to exec it from Crystal Reports.

I need to get Crystal Reports to send a value string as a single parameter to the stored procedure.

 

[synapsevampire]

I think that Naith has explained this perfectly. I disagree with the concept of using a cursor, since you can do the same with basic string manipulation, but this is a small dataset and it won't matter much, his theories are sound.

If the stored proc has a single parameter which is expecting a comma delimited value, then I fear that you'll have to prompt the users to enter the parameter as a comma delimited value or break up the single parm into many - I know, Crystal is horrible with parameters...

Now part of the reason that your View is slow might be that the SQL isn't being passed to the database for the parameters entered.

Make sure that it does pass the where criteria in the Show SQL Query.

If it isn't, share your record selection formula, someone here will straighten it out.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mendez]

synapsevampire

I'm sure it is not passing the where criteria. When using a View, I see the record counter in the report get over 2m before I am displayed the 10 or 20 pages of what I'm looking for.

This is my record selection formula:
IF {?Reference Code} <> &quot;&quot; AND {?Account Code} <> &quot;&quot; THEN
{mm_creative_services_inventory_vw.reference_code} = {?Reference Code}
AND {mm_creative_services_inventory_vw.account_code} = {?Account Code}
AND ({mm_creative_services_inventory_vw.apply_date} = {?From Date} OR {mm_creative_services_inventory_vw.apply_date} > {?From Date})
AND ({mm_creative_services_inventory_vw.apply_date} = {?To Date} OR {mm_creative_services_inventory_vw.apply_date} < {?To Date})
ELSE
IF {?Reference Code} <> &quot;&quot; AND {?Account Code} = &quot;&quot; THEN
{mm_creative_services_inventory_vw.reference_code} = {?Reference Code}
AND ({mm_creative_services_inventory_vw.apply_date} = {?From Date} OR {mm_creative_services_inventory_vw.apply_date} > {?From Date})
AND ({mm_creative_services_inventory_vw.apply_date} = {?To Date} OR {mm_creative_services_inventory_vw.apply_date} < {?To Date})
ELSE
IF {?Reference Code} = &quot;&quot; AND {?Account Code} <> &quot;&quot; THEN
{mm_creative_services_inventory_vw.account_code} = {?Account Code}
AND ({mm_creative_services_inventory_vw.apply_date} = {?From Date} OR {mm_creative_services_inventory_vw.apply_date} > {?From Date})
AND ({mm_creative_services_inventory_vw.apply_date} = {?To Date} OR {mm_creative_services_inventory_vw.apply_date} < {?To Date})
ELSE
({mm_creative_services_inventory_vw.apply_date} = {?From Date} OR {mm_creative_services_inventory_vw.apply_date} > {?From Date})
AND ({mm_creative_services_inventory_vw.apply_date} = {?To Date} OR {mm_creative_services_inventory_vw.apply_date} < {?To Date})

Cheers

 

[Naith]

Mendez,

It seems we're talking at cross purposes here, because I'm sure your stored procedure does indeed work fine, it's just that I wanted to verify if your procedure was using the long string method you illustrate in your 3rd post.

Your generated SQL is interesting in that, you're right, it won't pass to the database, so it could be sped up. I'll let the forums resident passchamp deal with that since he brought it up...

However, if you want to stick with using stored procedures, I'll give you a look at how I force Crystal to think it's getting multiple values to a stored procedure using an algorithm, which you can decide to lift if you want.

The following is only advisable if you have a list of account codes which is not greater than 116 on Sybase, or around 200 on Oracle.

(1) Create a reference table for all your account codes - which you probably have already. This table, however, should have an identifying sequence of unique powers of 2 which increment for each row.
e.g.
1 1stAccountCode
2 2ndAccountCode
4 3rdAccountCode
8 4thAccountCode
16 5thAccountCode
32 ...
64 ...
128 ...YouGetTheDrift...

NB: To maximise the number of rows you can get in, use the smallest precision and work up. i.e. .000000000000000001 for the first row in Sybase. Or the same plus 20 zeroes in Oracle. This is so you don't bust out of the maximum datatype size when you get to your later rows.

(2) Create a report which queries this table, allowing for multiple prompts on account code. (Set the value of the parameter to the ^2 numbers, and the description to the account codes. Set the parameters to display the description only.)
(3) When the list has been selected, a formula sums the total of the list, and passes this to the subreport in the ReportHeaderB. The subreport is actually the only report which is seen - and is your current report.
(4) The stored procedure parameters are prompted from the subreport in addition to a single stored procedure parameter which receives the total from the formula in the main report.
(5) The stored procedure applies an algorithm to break the total down into it's components.
e.g.
Choosing the 3rd, 4th, and 5th codes from the example above creates a total of 28. Supplying the procedure with the algorithm to subtract the maximum ^2 number less than the total (in this case, 16), and then the maximum ^2 number less from that total, and so on, and so forth reveals the ids of the account codes chosen to the procedure.

Each total can only have one possible breakdown of n^2 components, so the possibility of breaking down incorrectly is not an option.

(6) The procedure returns the data based on the multiple account codes in the initial report.

It sounds inwieldy, but it's quick to execute and return, and hey - it's been working for me every time I needed multiple prompts in a stored proc. However, you can save yourself a lot of work if you can supply the codes direct to the subreport by having a formula which concatenates the multiple values instead of a formula which sums the ^2 ids.

If you have a concatenating formula, you don't need the algorithm at all, the users need never see the concatenation, and you split the string up in the procedure just like you're doing now, anyway. The only thing is that using concatenation, the 254 character limit puts a cap on the amount of values you can pass. At 10 characters a code, that's still a healthy 25 account codes.

I just prefer to use the sums because I work with variable length strings, and it eliminates the 254 cap.

Naith

 

[synapsevampire]

I'll bet that your parameter is a date type, rather than a datetime. Sometimes Crystal won't pass the SQL through based on that alone.

You'll get MUCH faster performance by just creating formulas to change the parameters into datetimes and referencing those in the record selection.

Do it one step at a time, checking the Database->Show SQL Query, so that you assure that every section passes, and don't reference any formulas that use a variable, this breaks the pass through.

Obviously a Stored Procedure will be faster, but a 2 million row return is what's punishing you.

Here's an example of how I'd try to address this:

I'll assume that the codes pass fine, we'll fix the dates:

Formula FromDate
datetime(year({?From Date}),month({?From Date}),day({?From Date}),0,0,0)

Formula ToDate
datetime(year({?To Date}),month({?To Date}),day({?To Date}),23,59,59)


IF {?Reference Code} <> &quot;&quot; AND {?Account Code} <> &quot;&quot; THEN
(
{mm_creative_services_inventory_vw.reference_code} = {?Reference Code}
)
AND
(
{mm_creative_services_inventory_vw.account_code} = {?Account Code}
)
AND
(
({mm_creative_services_inventory_vw.apply_date} = {?From Date} OR {mm_creative_services_inventory_vw.apply_date} >= {@FromDate})
)
AND
(
({mm_creative_services_inventory_vw.apply_date} = {?To Date} OR {mm_creative_services_inventory_vw.apply_date} <= {@ToDate})
)

Note the seemingly pointless parens, but I have examples of how CR won't pass the SQL without having this separation in place...

It's very persnickety about passing through SQL, but once you get a feel for how CR creates pass through, you'll be fine.

Hope that this resolves for you.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com