Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Command Parameter

Posted on 2006-07-11
6
Medium Priority
?
246 Views
Last Modified: 2008-02-01
Using CR XI Developer edition, I have created a report that uses the below command.  The problem that I am experiencing is with the {?item} parameter.  When the user selects 1 item the report runs fine and returns data, when a user selects multiple items the report comes back blank.  Does anyone know if multiple values can be returned inside a command object parameter?  Can anyone see an error in the code?  Any assistance would be greatly appreciated....

select a.soptype,a.sopnumbe,a.orignumb,a.docid,a.docdate,a.pymtrmid,a.prclevel,a.locncode,a.custnmbr,a.custname,a.cstponbr,a.city,a.state,
a.zipcode,a.slprsnid,b.itemnmbr,b.itemdesc,b.quantity,b.unitprce,b.xtndprce from sop30200 a inner join sop30300 b on a.soptype = b.soptype and a.sopnumbe = b.sopnumbe
where orignumb in(select sopnumbe from sop30200 where docdate >= {?Date1} and docdate <{?Date2} and soptype = 2
and voidstts <> 1 and orignumb = '')
and docdate >= {?Date1} and docdate < {?Date2} and a.soptype = 3 and b.itemnmbr in( '{?item}') and a.locncode ='{?Location}'
0
Comment
Question by:mwalsh2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 5

Expert Comment

by:dkDeveloper
ID: 17081867
Hi mwalsh2000,

I'm pretty sure that when Crystal returns multiple selections from a parameter it does so in an array (unfortunately even for commands).  You might want to consider testing this by creating a command or stored proc that would just return the value passed in by {?item}.  I'd be suprised if it was a comma seperated list.

Let us know what you find out -- if it is an array I think we can still work around it using a couple of different methods depending upon your development freedom.

Cheers!
dK
0
 

Author Comment

by:mwalsh2000
ID: 17082543
Hi dK, yes it is an array, I guess I can loop through the array using the whileprintingrecords ?
0
 
LVL 28

Accepted Solution

by:
bdreed35 earned 1000 total points
ID: 17082675
The command parameters can only handle a single discrete value as input.
They work the same as if you created a parameter in a stored procedure.
An alternative would be for them to eter a comma delimited list as the parameter input.
You could then write your criteria like this:

select a.soptype,a.sopnumbe,a.orignumb,a.docid,a.docdate,a.pymtrmid,a.prclevel,a.locncode,a.custnmbr,a.custname,a.cstponbr,a.city,a.state,
a.zipcode,a.slprsnid,b.itemnmbr,b.itemdesc,b.quantity,b.unitprce,b.xtndprce from sop30200 a inner join sop30300 b on a.soptype = b.soptype and a.sopnumbe = b.sopnumbe
where orignumb in(select sopnumbe from sop30200 where docdate >= {?Date1} and docdate <{?Date2} and soptype = 2
and voidstts <> 1 and orignumb = '')
and docdate >= {?Date1} and docdate < {?Date2} and a.soptype = 3 and b.itemnmbr in( '{?item}') and a.locncode in ({?Location})
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 17096557
As you said mwalsh2000, parameters that are set in CR to allow multiple values are stored in CR as an array.  As such, they can't be passed to an SQL stored procedure (because SQL doesn't have an array data type).  You can test them in CR using a simple record selection like:

{field} = {?param}

 CR knows how to interpret the parameter, so you can just use "=" (instead of trying to use a loop to check each value in the array).  You could presumably also use "in" (as in "{field} in {?param}"), but "=" seems to work fine.
 The drawback is that since you can't pass that parameter to the stored procedure, it has to output the rows with every value for that field and then CR selects the rows to include on the report.  So there will be that much more information being sent from the SQL server, etc.  Whether or not that's a problem will depend on how much "extra" data you're dealing with, etc.

 If you enter multiple values in a single parameter, that can be passed to a stored procedure, but I don't believe you can use a test like "b.itemnmbr in ({?item})" to test for those multiple values.  It would see the parameter as one value (eg. "1,2,3,4") instead of separate values (eg. "1", "2", "3" and "4").  At least, that's how it seemed to work when I tried it.  I'd be happy to hear that I was wrong.
 You might be able to do something like "b.itemnmbr in ({?item})" using dynamic SQL, so the parameter name was replaced by the value and then the value was interpreted to get a list of values, although it seems like you would have a problem if the values were strings and the user didn't enter quotes (eg. they entered "a,b,c" instead of "'a','b','c'").

 Also, in your example you had "b.itemnmbr in( '{?item}') and a.locncode ='{?Location}' ".  With the single-quotes around the parameter names, the procedure would, I think, be looking at the literal parameter names (eg. "{?Location}"), not the contents of the parameters.  So you probably want to take the quotes out (eg. "a.locncode = {?Location}").

 To use multiple values in a parameter, you can parse the parameter and store the values in a table variable (or temp table), then use that in your test.  The following code assumes that @item is a parameter containing a list of values that can be up to 10 characters long each.  If your item numbers are different, change the declarations of @tmpitem and single_item as appropriate.  If you want the separate fields to be numeric, you can change the declarations and use CAST/CONVERT when setting @tmpitem.


DECLARE
@tmpitem varchar(10),
@sep int

DECLARE @itemlist TABLE
(
single_item varchar(10)
)

-- Remove any spaces in @item (assuming you don't want any spaces
-- in your values).
SET @item = Replace (@item, ' ', '')

SET NOCOUNT ON

WHILE Len (@item) > 0
BEGIN
  SET @sep = CharIndex (',', @item)
  IF @sep = 0 OR @sep IS NULL
  BEGIN
--  If no comma was found, use all of @item as the value and then set
-- @item to null, which will end the loop.
    SET @tmpitem = @item
    SET @item = NULL
  END
  ELSE
  BEGIN
--  Otherwise, use everything before the first comma as the value and
-- remove everything through the first comma from @item.
    SET @tmpitem = Left (@item, @sep - 1)
    SET @item = Substring (@item, @sep + 1, Len (@item))
  END

  INSERT INTO @itemlist VALUES (@tmpitem)

END


 Put that in the beginning of your procedure.  It builds a table variable named @itemlist that contains the values found in @item.  Then you would use something like the following in your WHERE:
 b.itemnmbr in (select single_item from @itemlist)

 Note that the code above changes the @item parameter.  If you want to leave the parameter intact, just declare a variable of the appropriate length, set it to @item and use your variable in that code instead of @item.

 James
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question