Hi,
I created a custom report in BIDS, but I am having trouble getting the report/SSRS to accept the parameters that get passed from the report window and then run automatically. For example, here is the report window with the parameters selected:
When I click OK, I get the following:
As you can see, “Contact Info” did not accept the “No” parameter and the giving categories I selected did not get passed to the report, as I have to click on the “giving cats” dropdown and reselect the categories I want. Both "giving years" and "list no" did actually accept the given parameter values, but I'm not sure why those did and not the others. Additionally, even after I reselect my parameters, I still have to click “View Report.”
I want the report to select the parameters I initially chose and then run by itself. Can anyone help me with this? I’ve been back and forth between Tessitura, BIDS, and report setup, and I can’t’ seem to figure out the issue. Any help would be greatly appreciated. Thanks!
David
Thanks, Sara and Galen. I'm not quite sure what I did, but it appears that the Contact Info parameter is now passing the value to SSRS automatically, but the report is still prompting me for the Giving Category values after I run the report. Similarly, I tried hiding the parameters in SSRS, and while it works for three out of the four parameters, when the report runs, it says that Giving Categories is missing a value. My guess is this particular parameter is causing the issue ss this is the only parameter that needs to accept multiple values. I've tried both your suggestions, and they don't seem to work for that parameter.
I think there's one more thing you need in your procedure. When Tessitura passes the multi-selected values as a string, each value is in quotation marks. What I always do is at the start of the procedure I include this statement for each parameter than needs to receive multiple values:
set @parameter= replace(@parameter,'"','')
This strips out the quotation marks so that later when you do the FT_SPLIT_LIST thing they won't cause a problem.
-Galen
Interesting. I have literally NEVER done this with my multi-select string parameters and never had an issue when using FT_SPLIT_LIST.
Interesting! I wonder if something changed since I came up with that solution, or if I was actually just wrong about it in the first place years ago and so I've been automatically putting unnecessary code in everything I've written since then...
No idea. I just know that I have never once done it. I started writing SQL for Tessitura in late 2012 on Version 10 if that helps. I think it was Version 10. Definitely not 9, but I suppose could have been 11.
Thanks, Galen. I'll try this and let you know if it works. One question, I don't have the FT_SPLIT_LIST function in my procedure anywhere. Is this something I need to have, or does Tessitura/SSRS run that function in the background whenever there is a multiselect parameter that needs to be split up into individual strings? Thanks!
You'll definitely want to use the FT_SPLIT_LIST function in your stored procedure for multi-select parameters.