• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Command Parameter

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
mwalsh2000
Asked:
mwalsh2000
2 Solutions
 
dkDeveloperCommented:
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
 
mwalsh2000Author Commented:
Hi dK, yes it is an array, I guess I can loop through the array using the whileprintingrecords ?
0
 
bdreed35Commented:
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
 
James0628Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now