Skip to Content
avatar image
Former Member

SQL Command Parameter - Multiple Value

Using XIR2

I am using a SQL command that I want to have a couple parameters in the where clause. My issue is making those parameter having multiple value turned on. I get an error pretty much saying not allowed. Side note, the parameters are dynamic.

Is there a workaround?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Feb 24, 2010 at 06:00 PM

    Multi-valued parameters are treated as arrays by Crystal, so they don't pass well to an SQL Command. They are not allowed. How I've worked around this in the past is to use subreports: The main report asks for the parameters, and concatenates the multi-valued parameters into a string, using something like:

    "|" + Join({?MultiParm1, "|") + "|"
    

    Pass the concatenation to the subreport, then in the subreport's SQL Command's where clause, use something like (MS SQL):

    where charindex('|' + db_field + '|', {?MultiParmConcat}) > 0
    

    BTW, you need all of those vertical bars, or you may end up matching "12" with "120".

    HTH,

    Carl

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 24, 2010 at 06:25 PM

    Can explain the second part more in-depth? I understand the part about supplying the formula to the sub-report, but get lost after that? Are you saying to take that formula from the main report and put in the where clause of the sub-report command?

    Edited by: Brian Potwora on Feb 24, 2010 7:25 PM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 21, 2010 at 06:59 PM

    FYI - I have a proof of concept report in my box.net account through linkedin that does this, but doesn't require parsing out the delimited values in the SQL. Here's the link:

    https://www.box.net/shared/663ziq8unr

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      No worries, Jason - that's what they're for.

      As to the SQL passed from the command-driven subreport to your database server, it'll be exactly what's in the command. In my example, the code being passed to the server is:

      SELECT

      *

      FROM

      SALES.CUSTOMER

      WHERE

      TerritoryID in ({?Territory})

      The actual values being passed in would be:

      WHERE

      TerritoryID in ('1','2','3','4')

      It's gratifying to know my example works with with both dynamic params and static params. I'd like to see if somebody could get it to work with a Business View LoV as a proof of concept (or just have everybody upgrade to 2008 - life would be much simpler and not just for this particular issue).

  • avatar image
    Former Member
    Jul 29, 2010 at 12:37 AM

    Hello,

    How I can link the parameter in the main report and the subreport ?

    Crystal ask to me twice to introduce the value...

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Keeping with the examples provided in this thread... If you right-click the sub-report and choose Change Subreport Links you'll see where the formula @Territory from the outer report is linked to parameter ?Territory of the sub-report.

      Because ?Territory is being fed it's value programatically, it is not being displayed to the end user.

      Jason