I get an error (Incorrect syntax near ','.) when I choose more than one value
for any of the multi-valued report parameters in the below query. All
parameters, except scheduled_date and tracking_number, are defined as
multi-value. It may be a problem with strings appending. Any suggestions?
SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
D.tracking_number, B.alternate_id, D.name,
CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
status, D.sent_date, D.scheduled_date, D.owner_user_id
FROM o_dpl_deployment AS D INNER JOIN
o_bas_brand AS B ON D.environment_id = B.environment_id
INNER JOIN
o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
AND B.environment_id = DT.environment_id
WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
AND ((D.tracking_number LIKE @.tracking_number) OR
(@.tracking_number = ''))
AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
AND ((D.scheduled_date >= @.scheduled_date AND
D.scheduled_date < DATEADD(month,1,@.scheduled_date))
OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
>= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
>= GETDATE() - 1 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
>= GETDATE() - 3 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
>= GETDATE() - 7 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
>= GETDATE() - 14 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
>= GETDATE() - 30 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
>= GETDATE() - 60 AND D.scheduled_date < GETDATE())
OR (@.scheduled_date = convert(datetime,'9599-12-31')))
AND ((DT.name IN (@.deployment_type_name)) OR
(@.deployment_type_name = '-1'))
AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
AND ((D.owner_user_id IN (@.owner_user_id)) OR
(@.owner_user_id = '-1'))
AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
AND ((D.status IN (@.status)) OR (@.status = '-1'))
AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
UNION
SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
ORDER BY D.scheduled_date DESCYou may want to search in the programming discussion group too. I had a
similar problem.
"Stephanie" wrote:
> I get an error (Incorrect syntax near ','.) when I choose more than one value
> for any of the multi-valued report parameters in the below query. All
> parameters, except scheduled_date and tracking_number, are defined as
> multi-value. It may be a problem with strings appending. Any suggestions?
> SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
> D.tracking_number, B.alternate_id, D.name,
> CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
> status, D.sent_date, D.scheduled_date, D.owner_user_id
> FROM o_dpl_deployment AS D INNER JOIN
> o_bas_brand AS B ON D.environment_id = B.environment_id
> INNER JOIN
> o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
> AND B.environment_id = DT.environment_id
> WHERE ((D.name IN (@.name)) OR (@.name = '-1'))
> AND ((D.tracking_number LIKE @.tracking_number) OR
> (@.tracking_number = ''))
> AND ((D.promo_code IN (@.promo_code)) or (@.promo_code = '-1'))
> AND ((D.scheduled_date >= @.scheduled_date AND
> D.scheduled_date < DATEADD(month,1,@.scheduled_date))
> OR (@.scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
> >= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
> OR (@.scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
> >= GETDATE() - 1 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
> >= GETDATE() - 3 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
> >= GETDATE() - 7 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
> >= GETDATE() - 14 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
> >= GETDATE() - 30 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
> >= GETDATE() - 60 AND D.scheduled_date < GETDATE())
> OR (@.scheduled_date = convert(datetime,'9599-12-31')))
> AND ((DT.name IN (@.deployment_type_name)) OR
> (@.deployment_type_name = '-1'))
> AND ((B.name IN (@.brand_name)) OR (@.brand_name = '-1'))
> AND ((D.owner_user_id IN (@.owner_user_id)) OR
> (@.owner_user_id = '-1'))
> AND ((D.created_by IN (@.created_by)) OR (@.created_by = '-1'))
> AND ((D.status IN (@.status)) OR (@.status = '-1'))
> AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
> UNION
> SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
> ORDER BY D.scheduled_date DESC
>
Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts
Friday, March 30, 2012
multi-value parameter in Master report passing to single param sub-report in a list.
Here's tricky one.
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
Essentially, I want to create a for each loop.
Any ideas?
I have the same problem where I need to run a subreport for each value in a multivalued paramter. Does anyone have a solution?
|||
Here is one way to accomplish this if your parameter values come from a query:
In your main report, add a list to the body bound to the same data set as the multi-value parameter.
On the list, add a Filter with the following properties:
Expression: The same as used to populate the parameter value.
Operator: In
Value: Use the parameter value (i.e. =Fields!MyMultiValueParam.Value) Because the parameter is a multi-value parameter, this will evaluate to an array.
sqlMultiValue Parameter Help
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?
MultiValue Parameter Help
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?
MultiValue Parameter Help
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?
MultiValue Parameter Help
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?
MultiValue Parameter Help
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?
Multivalue Optional Parameters : Report Builder
Hi All,
Is there any way to make Multivalue parameter ( list parameter) in Report builder as optional ?
I was able to achieve optional functionalitiy for parameter is single value but not able to with respect to multivalue parameters
An early response is highly appreciated
Thanks
Do you really mean report Builder? the ad hoc tool... if so how did you make a single value optional? I was not aware that this could be done.|||How did you make parameter as multivalue in report builder? Quick response will be appreciated|||Pls go thru this topic
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=982530&SiteID=1
|||I couldnt get your question. Does that mean how we can allow the user to select multiple values from a Filter if so , First you need to set the valueinstance property for the field you want to use a multivalue parameter to list /dropdownlist in the Report Model.
Then after deploying the model. drag the field into filter dialg box and right click and select " IN a list option "
hope this helps
Multivalue Optional Parameters : Report Builder
Hi All,
Is there any way to make Multivalue parameter ( list parameter) in Report builder as optional ?
I was able to achieve optional functionalitiy for parameter is single value but not able to with respect to multivalue parameters
An early response is highly appreciated
Thanks
Do you really mean report Builder? the ad hoc tool... if so how did you make a single value optional? I was not aware that this could be done.|||How did you make parameter as multivalue in report builder? Quick response will be appreciated|||Pls go thru this topic
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=982530&SiteID=1
|||I couldnt get your question. Does that mean how we can allow the user to select multiple values from a Filter if so , First you need to set the valueinstance property for the field you want to use a multivalue parameter to list /dropdownlist in the Report Model.
Then after deploying the model. drag the field into filter dialg box and right click and select " IN a list option "
hope this helps
multi-value dropdownlist as a .net control?
Does anyone know if I can utilize the same multi-value dropdownlist control that is available in Report Manager and the ReportViewer parameters section, in my own ASPX pages? This is the control that has checkboxes in a dropdown list.
If not, can anyone point me to a third party?
Thanks,
Brian
Brian,know exactly what you are talking about as I was searching for an item like this myself. I have not found any similar control yet but managed to seperate the client side code that is driving the control in the ReportViewer. Did not have time to write a custom control that renders out this client side code but this is definitely doable. Let me know if you want to have the commented client side code that drives the dropdown (it does not work in Firefox though but should be good enough as an example).
Cheers,
Niels
multi-value dropdownlist as a .net control?
Does anyone know if I can utilize the same multi-value dropdownlist control that is available in Report Manager and the ReportViewer parameters section, in my own ASPX pages? This is the control that has checkboxes in a dropdown list.
If not, can anyone point me to a third party?
Thanks,
Brian
Brian,know exactly what you are talking about as I was searching for an item like this myself. I have not found any similar control yet but managed to seperate the client side code that is driving the control in the ReportViewer. Did not have time to write a custom control that renders out this client side code but this is definitely doable. Let me know if you want to have the commented client side code that drives the dropdown (it does not work in Firefox though but should be good enough as an example).
Cheers,
Niels
Monday, March 26, 2012
Multi-Select Parameters in SSRS 2005 - HELP
Hi all â'
Having a problem with SQL 2005 Reporting Services and Multi-Select
parameters. Itâ's seems as though Multi-Select and Allow Nulls are mutually
exclusive. This is a problem as we have some reports that are very complex â'
using very complex stored procedures that allow for this scenario. Now â' put
you hand over your ears for a second â' this works fine in Cryatsl â' the sproc
is set up to accept a null or a list.
WHERE (@.parm is NULL or element = @.parm)
There must be a way around this in SSRS
ThanksTry doing this...
where (@.parm is null or element in (@.parm))
Cheers|||My bad - I mistated the problem - the problem is not in the sproc - it is in
SRSS Designer. It will not let you set both Multi-Select and Allow Nulls on a
parameter - they are mutually exclusive. If you try to set both - the
designer gives an error and will not save your changes. It will on the other
hand - let you set one or the other individually.
Ths sproc workd just fine as we have sent it a list and a NULL value and it
works both ways as intended.
Thanks...
"sullins602" wrote:
> Try doing this...
> where (@.parm is null or element in (@.parm))
> Cheers
>|||Did you try returning a value of null to the dataset that populates the
list for the parameter?
select
null as 'val',
' - All - ' as 'label'
union
select
realval as 'val',
reallabel as 'lable'
from
mytable|||I just ran into the same problem today with a multi-value and null
parameter. I also thought to add null to the dataset, but when I added
the null to the parameter dataset it did not show up in the report
drop-down box.
Could it be possible this is being filtered out?
One way that does work is to use a actual value instead of a null
(assuming int).
SELECT -1 AS Level1Value, ' - None -' AS Level1Label
UNION ALL
<rest of query>
But I would rather have the option to not require a value to be
selected.
Any other ideas on making this work?|||I could try that - the only drawback that I see would be that All would then
be part of the mustiselect list - then what would happen if they select all
plus some of the other values - would they not get a sting like
'NULL','Val1','Val2'....
"sullins602" wrote:
> Did you try returning a value of null to the dataset that populates the
> list for the parameter?
> select
> null as 'val',
> ' - All - ' as 'label'
> union
> select
> realval as 'val',
> reallabel as 'lable'
> from
> mytable
>
Having a problem with SQL 2005 Reporting Services and Multi-Select
parameters. Itâ's seems as though Multi-Select and Allow Nulls are mutually
exclusive. This is a problem as we have some reports that are very complex â'
using very complex stored procedures that allow for this scenario. Now â' put
you hand over your ears for a second â' this works fine in Cryatsl â' the sproc
is set up to accept a null or a list.
WHERE (@.parm is NULL or element = @.parm)
There must be a way around this in SSRS
ThanksTry doing this...
where (@.parm is null or element in (@.parm))
Cheers|||My bad - I mistated the problem - the problem is not in the sproc - it is in
SRSS Designer. It will not let you set both Multi-Select and Allow Nulls on a
parameter - they are mutually exclusive. If you try to set both - the
designer gives an error and will not save your changes. It will on the other
hand - let you set one or the other individually.
Ths sproc workd just fine as we have sent it a list and a NULL value and it
works both ways as intended.
Thanks...
"sullins602" wrote:
> Try doing this...
> where (@.parm is null or element in (@.parm))
> Cheers
>|||Did you try returning a value of null to the dataset that populates the
list for the parameter?
select
null as 'val',
' - All - ' as 'label'
union
select
realval as 'val',
reallabel as 'lable'
from
mytable|||I just ran into the same problem today with a multi-value and null
parameter. I also thought to add null to the dataset, but when I added
the null to the parameter dataset it did not show up in the report
drop-down box.
Could it be possible this is being filtered out?
One way that does work is to use a actual value instead of a null
(assuming int).
SELECT -1 AS Level1Value, ' - None -' AS Level1Label
UNION ALL
<rest of query>
But I would rather have the option to not require a value to be
selected.
Any other ideas on making this work?|||I could try that - the only drawback that I see would be that All would then
be part of the mustiselect list - then what would happen if they select all
plus some of the other values - would they not get a sting like
'NULL','Val1','Val2'....
"sullins602" wrote:
> Did you try returning a value of null to the dataset that populates the
> list for the parameter?
> select
> null as 'val',
> ' - All - ' as 'label'
> union
> select
> realval as 'val',
> reallabel as 'lable'
> from
> mytable
>
Multi-Select Parameters
How does setting the Default type to Component work?
? - With all due respect -
Can you be more specific - I have not seen this before?
Best Regards,
sqlMulti-Select Parameters
I think I am really missing something here. I am trying to set up a
multi parameter dataset. I have read through all the postings and am
looking at the Wrox book SQL Server 2005 Reporting Services. It
states the following query will work
Select ProductSubcategoryId, [Name]
>From Production.ProductSubCategory
Where ProductSubCategoryID IN( @.ProductSubCategories )
so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
a nvarchar(150)
I have turned the parameter on the RS side as multiselect and am
passing 2 values through to this SP
I have tried testing this just through sql management studio to no
avail.
What am I missing?What you are missing is a SQL Server thing. It is not a RS issue. You cannot
pass a string and use it in an in clause in a stored procedure. If you have
the below statement in the dataset (i.e. not calling a SP it will work).Here
is a previous post of mine on the subject:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bysnyder@.hotmail.com> wrote in message
news:1166195712.306140.100760@.j72g2000cwa.googlegroups.com...
>I think I am really missing something here. I am trying to set up a
> multi parameter dataset. I have read through all the postings and am
> looking at the Wrox book SQL Server 2005 Reporting Services. It
> states the following query will work
>
> Select ProductSubcategoryId, [Name]
>>From Production.ProductSubCategory
> Where ProductSubCategoryID IN( @.ProductSubCategories )
> so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
> a nvarchar(150)
> I have turned the parameter on the RS side as multiselect and am
> passing 2 values through to this SP
> I have tried testing this just through sql management studio to no
> avail.
> What am I missing?
>
multi parameter dataset. I have read through all the postings and am
looking at the Wrox book SQL Server 2005 Reporting Services. It
states the following query will work
Select ProductSubcategoryId, [Name]
>From Production.ProductSubCategory
Where ProductSubCategoryID IN( @.ProductSubCategories )
so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
a nvarchar(150)
I have turned the parameter on the RS side as multiselect and am
passing 2 values through to this SP
I have tried testing this just through sql management studio to no
avail.
What am I missing?What you are missing is a SQL Server thing. It is not a RS issue. You cannot
pass a string and use it in an in clause in a stored procedure. If you have
the below statement in the dataset (i.e. not calling a SP it will work).Here
is a previous post of mine on the subject:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bysnyder@.hotmail.com> wrote in message
news:1166195712.306140.100760@.j72g2000cwa.googlegroups.com...
>I think I am really missing something here. I am trying to set up a
> multi parameter dataset. I have read through all the postings and am
> looking at the Wrox book SQL Server 2005 Reporting Services. It
> states the following query will work
>
> Select ProductSubcategoryId, [Name]
>>From Production.ProductSubCategory
> Where ProductSubCategoryID IN( @.ProductSubCategories )
> so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
> a nvarchar(150)
> I have turned the parameter on the RS side as multiselect and am
> passing 2 values through to this SP
> I have tried testing this just through sql management studio to no
> avail.
> What am I missing?
>
Multi-select parameter in SQL Server Rptg Svcs 2005
I am using a beta version of reporting services 2005.
I have created my report parameters as multi-select. However, I'm having
trouble having the query parameters accept the list that the report
parameters passes. (below) I tried replacing the = with in and between and
neither work. It doesn't like the comma between the multiple values. I
can't imagine that they've allowed multi-select report parameters without a
way to pass them cleanly to the query parameters.
Any help would be greatly appreciated. Thank you in advance for any help.
Machelle
Select * from candidate_info
where (recruiting_year in @.p_Year) and
(candidate_type_display in @.p_candidate_type) and
(degree_level in @.p_degree) and
(owner_site_code in @.p_site_owner) and
(offer_job_grade in @.p_grade)Try putting parenthese around the parameter like you normally would if you
were providing a hardcoded list ie
Select * from candidate_info
> where (recruiting_year in (@.p_Year)) and
> (candidate_type_display in (@.p_candidate_type)) and
> (degree_level in (@.p_degree)) and
> (owner_site_code in (@.p_site_owner)) and
> (offer_job_grade in (@.p_grade))
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
>I am using a beta version of reporting services 2005.
> I have created my report parameters as multi-select. However, I'm having
> trouble having the query parameters accept the list that the report
> parameters passes. (below) I tried replacing the = with in and between and
> neither work. It doesn't like the comma between the multiple values. I
> can't imagine that they've allowed multi-select report parameters without
> a
> way to pass them cleanly to the query parameters.
> Any help would be greatly appreciated. Thank you in advance for any help.
> Machelle
> Select * from candidate_info
> where (recruiting_year in @.p_Year) and
> (candidate_type_display in @.p_candidate_type) and
> (degree_level in @.p_degree) and
> (owner_site_code in @.p_site_owner) and
> (offer_job_grade in @.p_grade)
>|||I love it when it's a simple answer - thanks Wayne I really appreciate your
taking the time to answer my question.
"Wayne Snyder" wrote:
> Try putting parenthese around the parameter like you normally would if you
> were providing a hardcoded list ie
> Select * from candidate_info
> > where (recruiting_year in (@.p_Year)) and
> > (candidate_type_display in (@.p_candidate_type)) and
> > (degree_level in (@.p_degree)) and
> > (owner_site_code in (@.p_site_owner)) and
> > (offer_job_grade in (@.p_grade))
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
> >I am using a beta version of reporting services 2005.
> >
> > I have created my report parameters as multi-select. However, I'm having
> > trouble having the query parameters accept the list that the report
> > parameters passes. (below) I tried replacing the = with in and between and
> > neither work. It doesn't like the comma between the multiple values. I
> > can't imagine that they've allowed multi-select report parameters without
> > a
> > way to pass them cleanly to the query parameters.
> >
> > Any help would be greatly appreciated. Thank you in advance for any help.
> >
> > Machelle
> >
> > Select * from candidate_info
> > where (recruiting_year in @.p_Year) and
> > (candidate_type_display in @.p_candidate_type) and
> > (degree_level in @.p_degree) and
> > (owner_site_code in @.p_site_owner) and
> > (offer_job_grade in @.p_grade)
> >
>
>
I have created my report parameters as multi-select. However, I'm having
trouble having the query parameters accept the list that the report
parameters passes. (below) I tried replacing the = with in and between and
neither work. It doesn't like the comma between the multiple values. I
can't imagine that they've allowed multi-select report parameters without a
way to pass them cleanly to the query parameters.
Any help would be greatly appreciated. Thank you in advance for any help.
Machelle
Select * from candidate_info
where (recruiting_year in @.p_Year) and
(candidate_type_display in @.p_candidate_type) and
(degree_level in @.p_degree) and
(owner_site_code in @.p_site_owner) and
(offer_job_grade in @.p_grade)Try putting parenthese around the parameter like you normally would if you
were providing a hardcoded list ie
Select * from candidate_info
> where (recruiting_year in (@.p_Year)) and
> (candidate_type_display in (@.p_candidate_type)) and
> (degree_level in (@.p_degree)) and
> (owner_site_code in (@.p_site_owner)) and
> (offer_job_grade in (@.p_grade))
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
>I am using a beta version of reporting services 2005.
> I have created my report parameters as multi-select. However, I'm having
> trouble having the query parameters accept the list that the report
> parameters passes. (below) I tried replacing the = with in and between and
> neither work. It doesn't like the comma between the multiple values. I
> can't imagine that they've allowed multi-select report parameters without
> a
> way to pass them cleanly to the query parameters.
> Any help would be greatly appreciated. Thank you in advance for any help.
> Machelle
> Select * from candidate_info
> where (recruiting_year in @.p_Year) and
> (candidate_type_display in @.p_candidate_type) and
> (degree_level in @.p_degree) and
> (owner_site_code in @.p_site_owner) and
> (offer_job_grade in @.p_grade)
>|||I love it when it's a simple answer - thanks Wayne I really appreciate your
taking the time to answer my question.
"Wayne Snyder" wrote:
> Try putting parenthese around the parameter like you normally would if you
> were providing a hardcoded list ie
> Select * from candidate_info
> > where (recruiting_year in (@.p_Year)) and
> > (candidate_type_display in (@.p_candidate_type)) and
> > (degree_level in (@.p_degree)) and
> > (owner_site_code in (@.p_site_owner)) and
> > (offer_job_grade in (@.p_grade))
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
> >I am using a beta version of reporting services 2005.
> >
> > I have created my report parameters as multi-select. However, I'm having
> > trouble having the query parameters accept the list that the report
> > parameters passes. (below) I tried replacing the = with in and between and
> > neither work. It doesn't like the comma between the multiple values. I
> > can't imagine that they've allowed multi-select report parameters without
> > a
> > way to pass them cleanly to the query parameters.
> >
> > Any help would be greatly appreciated. Thank you in advance for any help.
> >
> > Machelle
> >
> > Select * from candidate_info
> > where (recruiting_year in @.p_Year) and
> > (candidate_type_display in @.p_candidate_type) and
> > (degree_level in @.p_degree) and
> > (owner_site_code in @.p_site_owner) and
> > (offer_job_grade in @.p_grade)
> >
>
>
Friday, March 23, 2012
Multiple/optional parameters
Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.
Thanks.
Hello John,
|||Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.
1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.
2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.
3.) In the stored Procedure the logic would be like this:
If @.Parm = fisicalyear
Begin
End
Else
Begin
End
Hope this helps..
I'm running all my queries against the cube but I guess the logic would be similar.
I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?
|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||Yes , it is possible. I personally did not do it but this link might be helpful.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087731&SiteID=1
John, I have this saved in my Code-Sample database and this was answered in the same forum.
This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.
Here is how to do this:
1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title
2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title
3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName
4.) Go to the Layout tab and open the \Report\Report Parameters menu
Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.
Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title
Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName
Click OK to leave the Report Parameters menu.
5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.
If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.
|||I'm not quite doing the same thing.
I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.
Wednesday, March 21, 2012
Multiple Values to select all values or deselect all values
I have 3 multi select report parameters on my report. Let's say a
user selects only a few of the values in each of the report parameter
drop downs but know they want to select all of the values with a click
of option button.
Then what I would like to do is have an option button that will allow
the user to select all of the option values or deselect all of the
values for all pararmeters by just selecting True or False from the
option button. Is there a way of doing this in Reporting Services?
Thanks for any help proved!On Jul 20, 11:01 am, trevorfuller1...@.gmail.com wrote:
> I have 3 multi select report parameters on my report. Let's say a
> user selects only a few of the values in each of the report parameter
> drop downs but know they want to select all of the values with a click
> of option button.
> Then what I would like to do is have an option button that will allow
> the user to select all of the option values or deselect all of the
> values for all pararmeters by just selecting True or False from the
> option button. Is there a way of doing this in Reporting Services?
> Thanks for any help proved!
There is not something built in to SSRS already; however, you could
create the functionality. You could have a parameter that if its value
is selected as True, you could send an 'All' type value back to the
stored procedure/query that is sourcing the report and then the stored
procedure could determine what 'All' means and select everything. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql
user selects only a few of the values in each of the report parameter
drop downs but know they want to select all of the values with a click
of option button.
Then what I would like to do is have an option button that will allow
the user to select all of the option values or deselect all of the
values for all pararmeters by just selecting True or False from the
option button. Is there a way of doing this in Reporting Services?
Thanks for any help proved!On Jul 20, 11:01 am, trevorfuller1...@.gmail.com wrote:
> I have 3 multi select report parameters on my report. Let's say a
> user selects only a few of the values in each of the report parameter
> drop downs but know they want to select all of the values with a click
> of option button.
> Then what I would like to do is have an option button that will allow
> the user to select all of the option values or deselect all of the
> values for all pararmeters by just selecting True or False from the
> option button. Is there a way of doing this in Reporting Services?
> Thanks for any help proved!
There is not something built in to SSRS already; however, you could
create the functionality. You could have a parameter that if its value
is selected as True, you could send an 'All' type value back to the
stored procedure/query that is sourcing the report and then the stored
procedure could determine what 'All' means and select everything. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql
Multiple Values Returned from Stored Proc
I need to return 2 values from a stroed proc. Can you have more than one
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:
> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com
output parameters in a stored proc? If not, would bringing back a dataset b
e
better than two round trips to the database to get the 2 values I need?
--
Robert HillRobert wrote:
> I need to return 2 values from a stroed proc. Can you have more than
> one output parameters in a stored proc? If not, would bringing back
> a dataset be better than two round trips to the database to get the 2
> values I need?
Output variables are generally faster to return than generating a 1 row
result set. You can have more than one output parameter in a procedure.
I would tell you to determine if, in fact, the results should be
returned as a resultset or as output parameters. If you think that
additional values may need to be returned in the future or if you think
there might be a time when more than one row needs to be returned, it's
better to use a resultset so you don't have to mess with the interface
of the procedure.
David Gugick
Imceda Software
www.imceda.com|||I am using the Microsoft Application Block for DataAccess and I cannot find
a
suitable procedure to call to bring back two output parameters. If this is
correct, I will need to extend the Application Block to include a procedure
to do what it is I need. Is this correct?
"David Gugick" wrote:
> Robert wrote:
> Output variables are generally faster to return than generating a 1 row
> result set. You can have more than one output parameter in a procedure.
> I would tell you to determine if, in fact, the results should be
> returned as a resultset or as output parameters. If you think that
> additional values may need to be returned in the future or if you think
> there might be a time when more than one row needs to be returned, it's
> better to use a resultset so you don't have to mess with the interface
> of the procedure.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Robert wrote:
> I am using the Microsoft Application Block for DataAccess and I
> cannot find a suitable procedure to call to bring back two output
> parameters. If this is correct, I will need to extend the
> Application Block to include a procedure to do what it is I need. Is
> this correct?
>
I plead ignorance. I have never used the Microsoft Application Block for
DataAccess. I don't know how the object model looks. I assume it's a
high-level view of ADO.Net. Where do you define parameters for the
stored procedures. Make sure you're using the latest release, now called
the "Enterprise Library Patterns and Practices Library"
http://msdn.microsoft.com/library/d...li
b.asp
David Gugick
Imceda Software
www.imceda.com
Multiple value parameters in SQL Server 2000 Reporting Services.
Hi all,
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
Thank you,
Hi,
Unfortunatelly, there is no official way to push multipy values fo parmeters in SSRS 2000, however, you can hardcode a list with multiply values behind eg: Sales Users as text and "Jim; Bob " ...etc as values and reuse them in the procedure or construct your t-sql string based on parameter with sp_executesql. There is a security risk with the last option.
Regards,
Janos
|||Thanks a lot Janos. I'll see what I can do. Maybe I can convince the client to go to SSRS 2005?Let's hope.
Thanks anyway,
Monday, March 19, 2012
multiple unnamed paramaters
Please give me an idea as to how I should be mapping my query parameters, if
i have more than one - to report parameters when using ODBC data
source(which supports only unnamed paramaters?)Related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fe557b51-0171-46a7-9af0-6940b9e04dd2.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"NU" <vidhuvaradan@.rediffmail.com> wrote in message
news:eoGk%23lYbEHA.2812@.tk2msftngp13.phx.gbl...
> Please give me an idea as to how I should be mapping my query parameters,
if
> i have more than one - to report parameters when using ODBC data
> source(which supports only unnamed paramaters?)
>|||Thanks, that helped.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:O23MKFabEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Related previous post:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fe557b51-0171-46a7-9af0-6940b9e04dd2.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "NU" <vidhuvaradan@.rediffmail.com> wrote in message
> news:eoGk%23lYbEHA.2812@.tk2msftngp13.phx.gbl...
> > Please give me an idea as to how I should be mapping my query
parameters,
> if
> > i have more than one - to report parameters when using ODBC data
> > source(which supports only unnamed paramaters?)
> >
> >
>
i have more than one - to report parameters when using ODBC data
source(which supports only unnamed paramaters?)Related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fe557b51-0171-46a7-9af0-6940b9e04dd2.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"NU" <vidhuvaradan@.rediffmail.com> wrote in message
news:eoGk%23lYbEHA.2812@.tk2msftngp13.phx.gbl...
> Please give me an idea as to how I should be mapping my query parameters,
if
> i have more than one - to report parameters when using ODBC data
> source(which supports only unnamed paramaters?)
>|||Thanks, that helped.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:O23MKFabEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Related previous post:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=fe557b51-0171-46a7-9af0-6940b9e04dd2.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "NU" <vidhuvaradan@.rediffmail.com> wrote in message
> news:eoGk%23lYbEHA.2812@.tk2msftngp13.phx.gbl...
> > Please give me an idea as to how I should be mapping my query
parameters,
> if
> > i have more than one - to report parameters when using ODBC data
> > source(which supports only unnamed paramaters?)
> >
> >
>
Subscribe to:
Posts (Atom)