I have passed a list of parameters to a multivalue parameter in a SQL Report.
I can click the drop down and see the list of parameters that were passed.
In my select statement for the report, here is the condition I am using in the where clause.
AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@.RUs))
Nothing is returned in the result set if more than one value is passed to the multivalue parameter.
Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.
For example, in the multivalue dropdown list, there are the following values:
211
212
If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.
what is the value type for your MV paramater? Is it the same as the sql value type?
|||>>Shouldn't the above conidition have the effect that no matter how many account numbers there are
You might think so, but no <g>. You have to build the SQL, there's no magic here that allows the SQL to automatically understand a collection of values -- which is what the multiselect parameter is.
I'll assume that your values for the dropdown list are string-type, because that is the hardest case usually and because you're using SUBSTRING() on your data column...
You need to construct your SQL something like this:
="SELECT " & blah blah blah & "WHERE " & blah blah &
" AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN ('" &
JOIN(Parameters!RUs.Value,"','") & "') "
... the use of red in the expression above is just to make sure you can see the single quotes versus the double quotes. I don't want to add spaces between the characters because those spaces would destroy the syntax we're trying to build.
What I'm trying to show you is that you need to end up with a SQL expression that has the delimited strings in an IN clause -- IN ('211','212') -- and that, while annoying when expressed as above, it is possible to do it <s>. This is because the JOIN function will take the various values for each of the parameter values in the collection and concatenate them with a delimiter that, in this case, is three characters long: ',' .
We add the outside or "embracing" single quote delimiters for the first and last items in the collection manually.
If there is only one item in the dropdown selected, the JOIN doesn't add anything but the one value, and the result is (say) IN ('211'), perfectly valid.
If you find it annoying to look at the SQL this way (I do) you can do what I've advised others to do several times in similar situations: just write a query that looks like this:
=Code.GetSQL()
... and write a little embedded Function GetSQL() AS String in your report, that returns the query as a string. You'll write a more maintainable version of the parsing code if you don't have to do it all in-line as above. If you need to do any type conversions or iterate through the parameter collection for any other reason, it will be easier.
HTH,
>L<
|||Lisa:
Thank you so much for such an involved reply. I like that answer and feel that you are most likely correct. However, I'm having trouble getting that to work for me.
Is the JOIN function that you are using in your code the one that can be used by right clicking a field on the layout tab of the report and selecting expression?
I'm not sure how I can use that JOIN in the SQL select statement on the Data tab. It doesn't seem to me that the code you have shown can be used in a standard select statement. How would I do that? As far as I know, JOIN in "natural SQL" can be INNER JOIN, OUTER JOIN, etc. However, I've never seen just JOIN used as you have here.
Also you've used the Parameters!RUs.Value syntax here. Shouldn't that be @.RUs as it is the data portion of the report and not the layout of the report?
Mainiac007:
The RUs parameter in my stored procedure is varchar(1000).
The multivalue report parameter is of type String.
The ChartOfAccountsAccountNo is of type varchar(24).
|||the JOIN she is referring to is not a sql command but a vb function.
|||HI there,
I'm sorry the reply was involved -- unfortunately it has to be <s>. I've seen a bunch of people saying you can't do this, which is why I bothered to write out the whole thing <s>. And it seems I guessed right about your data types, so you did need the full information.
To answer your question(s), and I promise it really does work <s>.... Look really carefully at the expression I wrote:
I didn't write a SQL statement. I wrote an EXPRESSION which, when evaluated, is a SQL statement.
Notice the = at the beginning. Notice the double quotes -- that's VB expression quotes, not SQL delimiters -- around the parts of the expression.
Notice, also, that I said in my reply that you could do the same thing by using the expression =Code.GetSql(), where GetSql is an embedded function in the Report Code. Obviously the SQL engine wouldn't understand Code.GetSql() either, but that makes no difference <s>.
Either way, in-line expression or function, the *returned result* of the expression is a valid SQL statement, which has been dynamically built up from strings to meet your requirements. The string which has been built up is what is going to get sent to the SQL engine, not the expression that created it.
As for why I used Parameters!RUs.Value rather than @.RUs, I think that will be obvious when you grok what I just said <s>. I'm not talking SQL when I'm creating the string. I'm talking report-scoped object syntax, and I'm talking to the collection of parameter objects that represents your multi-select parameter. The JOIN function is VB, and I'm using it in a way that is entirely appropriate to the problem at hand. (Considering the number of times I have seen people say "you can't do that with string values", I flatter myself that it's a pretty cool idea, too <g>.)
Does this make more sense now?
>L<
|||I could not have said it better myself.|||>> I could not have said it better myself
gee thanks <rofl> No offense, Maniac007, but ...
you were one of the people who appeared to need help on this exact issue and hence one of the reasons I posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012961&SiteID=1) .
>L<
|||yup, that was LAST year.... since then 6 months of working with ssrs has taught me a lot.|||Sorry, Mainiac007, I didn't mean you didn't *know* this. I meant it was obviously hard to write out and explain -- otherwise, obviously, you or many other people who post here would have done it <s>.
I've avoided explaining it many times as well, this time I just decided to bite the bullet. Let's hope the explanation is sufficiently clear and that people link to this thread as necessary.
>L<
|||My wife would kill me for saying this....but,,,,
"Just like a women, to have the last word." 
Lisa,
Thank you so much for your help. I think I'm at least following you now, but I still have an error in my SELECT statement and I can't seem to figure out what it is.
The error I get when running the report is: Incorrect syntax near 'GL_TRX'.
|||what are you summing this as...
SUM(GL_TRX.GLTRXAMOUNT) as what?|||
There are a couple of things that I see...
1. As Mainiac007 has already told you, you probably need an alias on your calculated (SUM(GL_TRX.GLTRXAMOUNT)) field.
2. Remember what I told you about those extra spaces you can't put in? Well, you put them in <g>.
" AND (SUBSTRING(CHART_OF_ACCOUNTS.CHARTOFACCTSACCOUNTNO,9,3) IN (' " &
JOIN(Parameters!RUs.Value," ',' ") & " ') " ...
you need to remove the extra spaces you put in, in all the places I marked in red where you have a single and double quote next to each other. Do you see why? Let's say your parameter value is XYZ. You want the chart of accts substring to match 'XYZ', you don't want it to match SPACE(1) + 'XYZ' + SPACE(1). Or, you could leave the spaces in but then you would have to change your substring expression to delimit with a space on either side.
3. I don't think that, when you're using a dynamically constructed query like this, you should use explicit params the way you're doing it embedded in the strings. It may work in some instances, it doesn't work out too well for me. So, instead of
"GL_TRX.GLTRXDATE BETWEEN @.StartDate AND @.EndDate"
... I would write:
"GL_TRX.GLTRXDATE BETWEEN '" & CSTR(@.StartDate) & "' AND '" & CSTR(@.EndDate) & "' "
... or something like that. Notice I've put the single quotes in red again so you can see them next to the double quotes.
4. I have two suggestions for you that might help you debug and also make the code less messy to maintain:
The first, I've already suggested (put the sql creation into an embedded proc so you can break it up and read what you are doing, append into a StringBuilder or whatever and then return the full statement.ToString() when you're done.)
The second is to create a simple report with the same parameters and have a text box that has the same expression building syntax you're using here. It doesn't matter what the rest of the report looks like, you just want one big textbox so that you can read it. Here's why this is a good idea:
* -- if you have basic VB-syntax problems, you'll know because the report won't be able to parse what you did when you try to preview it
* -- if you don't have a VB-syntax problem, you'll see what you did in the body of the report. You should be able to copy and paste it somewhere else and run it as sql. If it's correct, it will run. If not, you'll be able to see why <g>.
* -- once you get it right, you can move that same expression-building code into the "real" report.
HTH,
>L<
|||I have just run into a similiar situation. Where I have to pass a parameter (multi value) to a stored procedure.
The sproc use a where filed in(@.Centers) command.
When I select 1 value it works fine, when I select all or more then one, then no data is returned.
It seems the value returned from the MV is one,two,three. etc...
where to accurately pass this it should be 'one',two','three'
a hint on how to get it done this way?
No comments:
Post a Comment