SQL Command Parameter - Multiple Value

[Former Member]

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?

[Former Member]

Hello,

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

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

Thanks

[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

[Former Member]

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

[Former Member]

Kurt,

Great example, thanks for posting it.

Jason

[charliez]

Great example....Thank for posting, Kurt.

Does anyone figured out this with dynamic multi values? We use Business Views to populate the parameter values. The method described here only works with static values.

Any ideas?

[Former Member]

Conceptually, it should work the same. You'd choose values in the main report, concatenate them into a string via formula and then pass the formula into the command of the subreport. I'm fairly certain this would work with DCPs based on SQL Commands, but I'm less sure about BV-based parameters.

That being said, there's a major flaw in multi-value DCPs and I generally recommend people not use them.

[charliez]

No they don't work with BV based parameters......

I am still trying to figure out how the values look like in the final SQL. I was just wondering if we can modify the SQL itself to accept those values.

[Former Member]

Kurt is correct. It works exactly the same way weather you are using a dynamic lov or static.

Just out of curiosity, I decided to test it myself using Kurt's example...

[CRXIR2 Example Report - Multiple value parameters with SQL Commands.rpt|https://docs.google.com/leaf?id=0B_0KY03Gs2knYzNlZGVhYmMtYmZlMC00ZTdiLWIyYzctOTJiOTcxMGQxZWZm&sort=name&layout=list&num=50]

There's no way to post a working sample based on a BV but the same principal should apply.

Jason

PS... Kurt...I'm not trying to steal your stuff, it just provided an good foundation that was easy to modify.

[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).

[Former Member]

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

[Former Member]

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

[Former Member]

Carl,

How are you getting the results of a formula to pass into a SQL Command?

I've used an approach similar to the one described here: [http://customerfx.com/pages/reporting/2009/12/08/using-multi-value-parameters-to-select-records-for-your-crystal-reports.aspx]

The problem is that this method forces you to use the Select Expert to select records as opposed to the WHERE clause of the SQL statement.

Thanks,

Jason

[Former Member]

The main report has the multi-valued parameter {?MultiParm1}. The formula (call it {@ParmConcat}) to do the Join() is in the main report. This formula changes the array to a scalar string.

The subreport has the SQL Command, with the string parameter {?MultiParmConcat} and part of the WHERE clause as suggested above. Use subreport parameter linking to pass {@ParmConcat} in the main report to the {?MultiParmConcat} parameter in the subreport.

I know this works, because I've used it several times.

HTH,

Carl

[Former Member]

Wasn't doubting you at all. Just trying to wrap my head around it. That's all.

[Former Member]

Hi Carl,

It worked when multiple value is a static parameter. The problem I have now is a dynamic multiple value. I have this in my main report and link the parameter to subreport as what you did. However, when I upload this report to CMC, this dynamic parameter keep asking for database login even though I set database log on to "use same database logon as when report is run" I also try to check on Parameters tab and click on this dynamic multiple paramter, it asks for dabase login.

Have you ever tried your method using dynamic multiple value and run it in Inforview?

Thanks,

[charliez]

Does anyone know how the multi-valued parameters look like before passing them to the underlying SQL command? I mean how the SQL looks like before sending it to the database fetch?

Thanks in advance.....

[Former Member]

Hi Jason,

I am using Crystal Reports 2008 on an Oracle DB, BOBJ Enterprise XI 3.1, w/o using a universe.

I tried the example at

http://customerfx.com/pages/reporting/2009/12/08/using-multi-value-parameters-to-select-records-for-...

and I am getting a syntax error that {USERINFO.USERNAME} This field name is unknown.

How do I make BOBJ available to my Crystal Report? I really need this information for facility based report filter. I already have a cross-reference table built with BOBJ Username and Facility Id.

My Data Source is Oracle DB on a separate server than the BOBJ server.

Thanks,

Shari