Solved

Command Parameter

Posted on 2006-07-11
6
232 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
6 Comments
 
LVL 5

Expert Comment

by:dkDeveloper
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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 34

Assisted Solution

by:James0628
James0628 earned 250 total points
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now