Crystal Report pass multi valued parameter in sub report based on sql command

I'm confused.  As I understand it, you have the @CustomerNum formula in the main report producing a string, and it's linked to the ?CustomerNum parameter in the subreport, which is a number.  IOW, you're linking a string to a numeric parameter.  That should not be possible.

 Is ?CustomerNum actually a string parameter?

 If so, I'm not sure why the Command isn't working, but I haven't really used them (most of my reports used stored procedures).

 First of all, while your @CustomerNum formula looks OK, have you tried simply putting that formula on the main report, to make sure that the list of numbers is correct?  More to the point, you could put ?CustomerNum on the subreport, to make sure that it is getting the list correctly.

 If the list is correct, you could try checking what is actually getting passed to the Command.  Add a new column to your Select with the parameter value.  Maybe something like:

'{?CustomerNum}' AS PARAM_VALUE

 Then display that on the report and see if you get your list of numbers.

 That's completely untested, but it seems like it would work.

 James

Hi after reading your blog I have finally found some knowledge which
I was looking for so long for that reason Thanks.

192.168.1.1 facetime app facetime for android

Try this formula for @CustomerNum

WhilePrintingRecords;
 Join({?ListOfCustNum}), ',')

Select allOpen in new window

mlmcc

mlmcc,

 I was going to suggest Join as well (The OP's @CustomerNum formula looks fine.  Join would just be a lot simpler).  But they used ToText to build the list, implying that ?ListOfCustNum is a numeric parameter.  If so, Join won't work.  However, if ?ListOfCustNum is actually a string parameter, then they could try Join.  The end result should be the same, so I wouldn't expect it to change anything.  But it wouldn't hurt to try it, and the @CustomerNum formula would be simpler, if nothing else.

 James

mlmcc

WhilePrintingRecords;
 Join({?ListOfCustNum}), ',')

I got error A string array is required here.

I assume that Join works only on strings not numeric multi valued par.

mlmcc
in your
WhilePrintingRecords;
 Join({?ListOfCustNum}), ',')

you added one ) more:
WhilePrintingRecords;
 Join({?ListOfCustNum}, ',')

I assume that Join works only on strings not numeric multi valued par.

Basically.  Technically, Join is used on string arrays.  For example,

Join ([ "ab", "cd", "e" ], "-")

 gives you

"ab-cd-e"

 But since CR treats multi-value parameters like arrays, you can also use Join on one of those, as long as it's a string parameter.

 James

So this error string is required ask this value to  be changed to string???
Then pass this string variable  to  command as string.
 What I will do when I get this string variable passed to my command how to get numbers from it ???

James, thank you for help and directing me to proper solution.

I would like to submit my solution how to do that?

Hi James.

I got this work out finally.

To answer your question, is the parameter numeric parameter? Yes it is.
Field I use as parameter Customer Number in table is decimal with precision 2.
Those 2 precisions numbers are “.00” for each number e.g.  20,500.00 - 20,605.00 - 20,530.00 ……my parameter is definitely numeric.

Passing number like this in string will complicate more issue as now in sub report command you have something as:(( Select….Where my field in ?CustomerNum}  and in  parameter {?CustmerNum} you have string ‘25,300.00,45,005.00,45,620.00…’

Why my client setup this field Customer Number as decimal with two precision which have value of .00 I am not sure( possible reason is that he made sub group of Customer and gave it value as (25,300.55 – 25,300.60 –  45,005.10 45,005.20 and on).

So I was dealing with customer number parameter that is numeric and have format as I described.
So to solve this I had to :

First pass this number in string without decimal point as this is the only way I can pass parameters to command in CR2008.

Second in command I need to convert Customer Number to integer then to Varchar
-      CAST(CAST(mytable.[CustomerNumber] as Int) as VARCHAR(5) ))

I changed my formula to this:

WhilePrintingRecords;

Global StringVar Array DISP;

Local NumberVar i;

Local NumberVar x := UBound({?ListOfCustNum});

Redim DISP(x);

For i := 1 to x Do

DISP (i) := ToText({?ListOfCustNum}(i),'#');

Join (DISP, ', ')

Then I connected my sub report parameter with this formula and later on in Select statement have something as Select…..myfield in ({?CustomerNum})

Just to add around “i” in  Disp  goes square bracket but it will not allow me to post my answer on site with it.

I'm glad to hear that you got it working.  This may all be moot at this point, but, FWIW ...

To answer your question, is the parameter numeric parameter? Yes it is.

If you're talking about the subreport parameter, then I'm missing something.  In my experience, it is simply not possible to link a string (your @CustomerNum formula) to a numeric parameter.  CR won't give you that option when you create the subreport link (which makes sense, because the string might not be a number, and then you'd just get some kind of error).  I'm sorry to keep harping on this, but I'm confused.  :-)

Just to add around “i” in  Disp  goes square bracket but it will not allow me to post my answer on site with it.

Yeah, the site uses the square brackets for formatting commands.  An i in square brackets is for italics.  FYI, you can get around that by adding a right square bracket in front of the i -- []i]  (I did that by adding two right brackets, so one gets removed by the site and the second one is left alone).  Another option is to use the "code" formatting command.  Put [code] and [/code] around the formula, and CR won't look for formatting commands in those lines, and you get something like this:

WhilePrintingRecords;
Global StringVar Array DISP;
Local NumberVar i;
Local NumberVar x := UBound({?ListOfCustNum});
Redim DISP[x];
For i := 1 to x Do
DISP [i] := ToText({?ListOfCustNum}[i],'#');
Join (DISP, ', ')

Select allOpen in new window

 Also, regarding the formula above (from your last post):

 FWIW, the formula in your first post produces the exact same output, once you remove the extra space that I mentioned in an earlier post (",  " should be ", ").  So, your original formula should also work.

 James

This was my first work with command as data source in CR. I can see where using sql command gives a lot of  opportunities to take a heavy load from CR on user machine and put that hared work  on  database engine. On this way we get in report  already prepared and filtered records for  final manipulation and display.  In lot cases it could simplify and speed up report creation avoiding complex  calculation, creating sub reports,  grouping  and  formulas in report. Of course CR still stay an unmatched  end presenter and fine tool to polish your results.
Thank you and other a lot.