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})
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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
0
ios manCommented:
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
0
mlmccCommented:
Try this formula for @CustomerNum

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

Open in new window


mlmcc
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

James0628Commented:
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
0
TarasAuthor Commented:
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.
0
TarasAuthor Commented:
mlmcc
in your
WhilePrintingRecords;
 Join({?ListOfCustNum}), ',')

you added one ) more:
WhilePrintingRecords;
 Join({?ListOfCustNum}, ',')
0
James0628Commented:
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
0
TarasAuthor Commented:
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 ???
0
James0628Commented:
I think the error was just because Join is used on string arrays, and your parameter is numeric, so you can't use Join on that parameter.  But don't get too distracted by Join.  Even if it worked, it probably wouldn't change anything.  The end result should be basically the same as the string that you get from the @CustomerNum formula in your first post.  You don't need to use Join.  That was just a different way to combine the values into a single string.

 Have you put the @CustomerNum formula on the report to make sure that the string looks OK?

 Also, going back to something in my first post that you didn't answer:

 You said that you had linked your @CustomerNum formula to the ?CustomerNum parameter in the subreport, but the formula produces a string, and the parameter is supposedly a number.  You should not be able to link a string to a numeric parameter.

 Is ?CustomerNum actually a string parameter?

 If ?CustomerNum really is a numeric parameter, then you have lost me somewhere.


 Assuming that the ?CustomerNum parameter is actually a string, I do have a couple of things that you might want to change in your @CustomerNum formula.  I don't know if either of these will fix anything, but you could try them.

 Try adding WhilePrintingRecords; to the beginning of the @CustomerNum formula.  I'm wondering if it might be possible that the formula is not being evaluated at the proper time, so the subreport doesn't get the values.  I doubt it, but it won't hurt to try adding WhilePrintingRecords and see what happens.

 The formula in your first post has 2 spaces after the comma, instead of 1 (",  " instead of ", ").  That may just be a typo in your post, but if the formula in your report is like that, then Left(display,len(display)-2) will just remove the 2 spaces at the end and leave that last comma (and you'll have 2 spaces in between the values).  For example, you would get "1,  2,  3," instead of "1, 2, 3".  I don't know if that would cause a problem in the query, but I wanted to mention it.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
James, thank you for help and directing me to proper solution.
0
TarasAuthor Commented:
I would like to submit my solution how to do that?
0
TarasAuthor Commented:
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.
0
James0628Commented:
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
0
TarasAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.