Skip to Content
author's profile photo Former Member
Former Member

Another multi value parameter question

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2010 at 05:26 PM

    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.

    ?????

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2010 at 05:56 AM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2010 at 01:20 PM

    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}

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 28, 2010 at 06:58 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.