on 02-24-2010 5:51 PM
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?
Hello,
How I can link the parameter in the main report and the subreport ?
Crystal ask to me twice to introduce the value...
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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).
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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,
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
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
User | Count |
---|---|
85 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.