Avatar of Taras
Taras
Flag for Canada asked on

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

In my CR 2008 report I have main report as container for two sub reports that are created- based on the same sql command.
I am passing two parameters in each sub report.
 First parameter is ?Run_Date and I am using formula @CustomerNum to pass second parameter ?ListOfCustNum.
In each sub report I created sub report parameter named:
 ?Run_Date and ?CustomerNum – Name is the same as formula in main report.
 In sub report  parameter ?CustomerNum is number type and I did not checked Allow multiple value.
My formula in main report :
@CustomerNum
Numbervar Counter;
Stringvar display;
For counter := 1 to count({?ListOfCustNum}) do (
Display:= display + ToText({?ListOfCustNum}[counter], 0 , ””) +  ”,  “);
Left(display,len(display)-2);

I made links from main to subreport ?Run_Date and @CustomerNum.
I am getting proper date but not passing list of customer numbers in.
In sql command I have in Selection statement something as : Select ….where i.CustomerNum in ({?CustomerNum})
Crystal Reports

Avatar of undefined
Last Comment
Taras

8/22/2022 - Mon
James0628

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
ios man

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
Mike McCracken

Try this formula for @CustomerNum

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

Open in new window


mlmcc
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
James0628

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
Taras

ASKER
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.
Taras

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

you added one ) more:
WhilePrintingRecords;
 Join({?ListOfCustNum}, ',')
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James0628

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
Taras

ASKER
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 ???
ASKER CERTIFIED SOLUTION
James0628

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Taras

ASKER
James, thank you for help and directing me to proper solution.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Taras

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

ASKER
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.
James0628

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, ', ')

Open 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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Taras

ASKER
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.