We help IT Professionals succeed at work.

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

Taras
Taras asked
on
187 Views
Last Modified: 2018-10-11
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})
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Commented:
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 McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this formula for @CustomerNum

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

Open in new window


mlmcc
CERTIFIED EXPERT

Commented:
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

Author

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.

Author

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

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

Commented:
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

Author

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 ???
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Author

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

Author

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.
CERTIFIED EXPERT

Commented:
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

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions