Another multi value parameter question

[Former Member]

I have spent the last six hours researching and testing this and I am getting nowhere. I am using CR 2008 and SQL Server 2005. I have a main report that has a six level cascading prompt. The values for all of the prompts are number values. The lowest level prompt is a multi value prompt. I need to pass the value(s) from this prompt to a subreport so I have created this formula field which I have seen in many of the posts:

WhilePrintingRecords;

Shared numberVar counter;

//increments the loop so that all parameter entries can be displayed

Shared stringVar display;

//creates a string "running total" so that all entries can be displayed

for counter := 1 to Count({?Hierarchy - DOUNum}) do

(

display := display + ToText({?Hierarchy - DOUNum}[counter],0,";");

);

display;

I display this on my main form and I run across my first problem. The first DOUNum is 19903 and the second is 19904. What I see in the report though is 19,90319,904. I need for it to be 19903,19904. Before i knew the formula was doing this I passed this formula to my subreport for record selection there. That subreport has the DOUNum field in it and that field is a numeric field, so when I try to put this in my record selection formula:

{spCSSReportNCounts1;1.DOUNum} in [{?Pm-@DOUNum}]

I get this error: A number range is required here.

How in the world can I get around these two problems?

TIA

[Former Member · Accepted solution]

When I send a single DOUNum, either |19903| or |19904|, I get the desired results, but when I send multiple DOUNum's together |19903|19904| then my subreport returns nothing.

?????

[Former Member · Accepted solution]

Dang! I got the parameters to InStr backwards. It should be

InStr({?Pm-@DOUNum}, "|" + ToText({spCSSReportNCounts1;1.DOUNum},0,"") + "|") > 0

HTH,

Carl

[Former Member]

Thanks Carl, that did the trick. Where in the world do you guys come up with these formulas? I went to all three Crystal Reports 2008 training classes about three months ago and even bought the Complete Guide book and nothing even came close to giving me this answer unless it's written in small subscript somewhere.

I am going to open a new thread as soon as I can type it up that relates to passing other parameters to the same subreport. I have been working on this report since around Christmas and I am supposed to be done with it by Monday but it is looking bad. I still have a few more stumbling blocks to overcome.

Thanks again for your help with this problem.

[Former Member]

Glad to help!

This trick came from 35+ years of programming... Not all of the languages that I have used over the years supported arrays.

This trick is also useful to pass multiple-valued parameters to an SQL Command, as Crystal doesn't support that. (Use a main report to get the user's multiple selections and concatenate them as above, then pass the string to a subreport that passes the concatenation to the SQL Command.)

Carl

[Former Member]

Okay, I have the formatting issue resolved. Here is my new formula:

WhilePrintingRecords;

Shared numberVar counter;

//increments the loop so that all parameter entries can be displayed

Shared stringVar display;

//creates a string "running total" so that all entries can be displayed

for counter := 1 to Count({?Hierarchy - DOUNum}) do

(

display := display & "," & ToText({?Hierarchy - DOUNum}[counter],0,"");

);

Mid(display,2);

This gives me 19903,19904 to pass to the subreport in my subreport link parameter - ?Pm-@DOUNum

When I tried adding the = sign:

{spCSSReportNCounts1;1.DOUNum} = {?Pm-@DOUNum}

I get the error: A number is required here.

And when I try this:

{spCSSReportNCounts1;1.DOUNum} = [{?Pm-@DOUNum}]

I get the error: A number range is required here.

Which is the same error I get when i switch out the = for IN:

{spCSSReportNCounts1;1.DOUNum} In {?Pm-@DOUNum}

[Former Member]

Change your concatenation formula to:

WhilePrintingRecords;
Shared numberVar counter;
//increments the loop so that all parameter entries can be displayed
Shared stringVar display = "|";
//creates a string "running total" so that all entries can be displayed
for counter := 1 to Count({?Hierarchy - DOUNum}) do
(
display := display & "|" & ToText({?Hierarchy - DOUNum}[counter],0,"");
);
display := display + "|"

Pass it as a string to the subreport, and then in the subreport use

InStr("|" + ToText({spCSSReportNCounts1;1.DOUNum},0,"") + "|", {?Pm-@DOUNum}) > 0

By using the vertical bars ("|") you will guarantee to match numbers exactly (ie., InStr() will not find the value "2" in "222").

HTH,

Carl

[Former Member]

The first problem is due to the concatenation of the values taht you are doing in the formula meaning the display variable is just concatenating the values. you need to modify the formula to take cre of it.

For the second error, try using = operator instead of "In" .

Hope this helps!