Custom Report not accepting parameters from Tessitura Report window

Hi David - Without having seen the files, I'd recommend:

1. In SSRS parameter properties:
   1. Change your parameter visibility to hidden (this has solved problems more than once for me)
   2. Make sure your multi-select parameters are set to Data Type = Text
   3. I've also resolved issues by checking the "Allow null value" box on multi-select parameters even when they're required in Report Setup
   4. There's no need to check the "Allow multiple values" checkbox on the parameter in SSRS if you're passing it as a string which is the only way I've found that works consistently.
2. In Tessitura Report Setup:
   1. Make sure the "MS" box is checked for the multi-select parameters
   2. Make sure the parameter name exactly matches the parameter name in the stored procedure/query (including capitalization)

If it helps, I've included an example of part of one stored procedure I use where the first parameter (season_str) is a single select and the second parameter (pkg_str) is multi-select:

----

--SEASONS PARAMETER TABLE
Create Table #season (
season_id int null
,season_desc varchar(50) null
)

Insert #season (season_id, season_desc)
SELECT DISTINCT
s.id
,s.description
FROM [dbo].TR_SEASON AS s
JOIN [dbo].FT_SPLIT_LIST(@season_str, ',') x on s.id = x.element

--PKG PARAMETER TABLE
Create Table #pkgs (
pkg_no int null
,pkg_desc varchar(50) null
,season int null
)

Insert #pkgs (pkg_no, pkg_desc, season)
SELECT DISTINCT
p.pkg_no
,p.description
,p.season
FROM [dbo].T_PKG AS p
JOIN [dbo].FT_SPLIT_LIST(@pkg_str, ',') x on p.pkg_no = x.element
JOIN #season AS s ON p.season = s.season_id
WHERE p.season IN (s.season_id)

---

If you'll you send me your stored procedure/query, SSRS file, and an export of the report set-up, I'm happy to look at this for you in greater detail.

Cheers,
Sara Nemeth
snemeth@cballet.org

I second everything that Sara said. Additionally, I would guess that the reason your Contact Info value didn't get passed correctly is that the value that was passed by the Tessitura parameter window didn't match the values you had set up for the dropdown in the SSRS parameter setup. As a general rule, if you're handling the parameters in Tessitura, you're much better off setting the parameter in SSRS up to just be a string or an int or whatever data type is appropriate rather than setting it up as a dropdown. Leave the parameter visible while you test, so that you can easily confirm that the correct value was passed into it, and then hide it once you're satisfied that it's working right.

-Galen

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!

Update: I tried setting the giving categories parameters using the statement that Galen suggested and it didn't seem to have an effect. The other thing I also tried testing was putting a text box in the report that would simply return out the value of the "Giving Categories" parameter, so I could see what Tessitura was actually trying to pass to SSRS. I selected all four giving categories in the report, but the text box was only showing one of the options i selected. So it seems to me that somewhere, the multiple values I select either aren't being rolled up into one string and passed to SSRS, or Tessitura is only accepting and passing on the first option selected and ignoring the rest, and I'm not exactly sure how to fix this. 

Again, suggestions are needed and welcome. Thanks all!

David 

You'll definitely want to use the FT_SPLIT_LIST function in your stored procedure for multi-select parameters.

The trick that solved the problem was to set the Available Values for the multi-select parameter to None in the parameter properties window in SSRS.

Thank you for this tip, Galen! I've been working all afternoon on a custom report, and it turns out I had superfluous double quotes gumming up a cascading multi-param. Like John, I had never seen this happen, but so's I can save future hours troubleshooting going forward, this code is gonna be SOP.