September 8, 2023 at 12:18 pm
Hello, I have an SSRS report that uses a stored procedure. The stored proc is configured as a dataset, and one of the parameters is named @ProductNumbers. It is mapped to a report parameter of the same name, and if you clicked on expressions, it will indicate =Parameters!ProductNumbers.Value
The report parameter @ProductNumbers is fed from a dataset query for its available values, configured as text, and allows multiple values. So, it’s a dropdown on the report. It appears that this parameter is submitting a comma separated list of product numbers to the stored proc parameter, as it should.
This all works great, but now I need to add an additional report parameter to capture a handful of other product numbers to submit to the stored procedure that are not included in the @ProductNumbers report parameter dropdown. So, the user would just paste in a handful of comma separated product numbers in this additional text box parameter.
I was thinking about going to the stored proc dataset properties, parameters section, expression for @ProductNumbers and doing something like this:
=Parameters!ProductNumbers.Value & Parameters!NewTextBox.Value
Would this work? If so, what would be the correct syntax? Note that I cannot change the stored proc in this case. Help/suggestions appreciated.
Thanks!
September 8, 2023 at 2:10 pm
I have a feeling that would fail, but why don't you give it a shot?
But I think the correct syntax for concat is a "+" in SSRS.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 8, 2023 at 3:34 pm
I'm inclined to think that is going to fail as well. Each multi-value parameter is going to be stored as an array, and I don't believe you can simply concatenate arrays. However, I think you can probably do this by converting both arrays to delimited strings, combining the strings, and then converting back to an array before sending it to the stored procedure:
=Split(Join(Parameters!ProductNumbers.Value,";") & Join(Parameters!NewTextBox.Value,";"),";"
Maybe try that? 🙂
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
September 8, 2023 at 3:34 pm
I'm inclined to think that is going to fail as well. Each multi-value parameter is going to be stored as an array, and I don't believe you can simply concatenate arrays. However, I think you can probably do this by converting both arrays to delimited strings, combining the strings, and then converting back to an array before sending it to the stored procedure:
=Split(Join(Parameters!ProductNumbers.Value,";") & Join(Parameters!NewTextBox.Value,";"),";")
Maybe try that? 🙂
"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me
September 13, 2023 at 2:05 pm
Thank you both for your replies and examples to try. I see what you mean now and I didn't get it to work, but found a workaround. Instead of using 2 report parameters , I just went back to the query that feeds one of the parameters, and then performed additional set operations (unions) to 'stack' the other product numbers I needed on top of the existing query output. This seems to work fine and only one parameter drop down.
example:
Select * From (
Select product numbers from some table as ProductNumber
UNION
Select '123456p' as ProductNumber
UNION
Select '456789p' as ProductNumber
) X Order by ProductNumber
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply