SSRS Help

A Multi-select parameter in SSRS is really an array datatype.  A multi-select parameter in Tessitura is really just a string.  Try changing the parameter in SSRS to a string, uncheck Multi-Select and you should be good to go. 

You can’t pass a SSRS multi-select parameter in SSRS defined as ‘multi-select’ to a stored procedure, but you can use the parameter in a where clause of a SQL query like this:

Select column1, column2

From SomeTable

Where somecolumn in (@MyMultiSelectParameter)

Hello,

Trying to create my first report using MS SQL Reporting Services in place of Infomaker.

My data source is a stored procedure with 1 of the parameters being membership level. The parameter is a multiple value string. My stored proc has the following line to deal with it:

create table #tlevels(memb_level char(3) null)

    Insert    into #tlevels

    Select    memb_level
    From    t_memb_level
    WHERE     charindex(',' + memb_level + ',' , ',' + @level_str + ',') > 0

I need help because for some unknown reason, SSRS is not liking the way that Tessitura is sending the string value of the member levels.

For example, from the report parameter selection, the following is output: "VIP","IVI","BA+","CB+"

If I change the parameter values manually to:

VIP,IVI,BA+,CB+

then the SSRS report works.

Any ideas on what I need to do to get it to work?

Christian @ The Tech Museum in San Jose CA

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.238 / Virus Database: 270.11.33/2031 - Release Date: 04/02/09 06:09:00

Tessitura always adds double-quotes as a text qualifier in a multiselect “string”.

Two things you can do to remove the quotations, either do a replace in the stored procedure:

Replace(@myparameter, ‘”’, ‘’)  -- minus the slanted quotes

Or you can add the quotes to the charindex()

create table #tlevels(memb_level char(3) null)

    Insert    into #tlevels

    Select    memb_level
    From    t_memb_level
    WHERE     charindex(',”' + memb_level + '”,' , ', ”' + @level_str + '”,') > 0

Ryan,

Thanks for your reply. I was just watching your webinar on SSRS via the new site. Thanks for that too.

I have tried changing the parameter to non multi select as well and still doesn't work. If I keep the parameter unhidden, I can see what Tessitura sends over and it is "VIP","IVI","BA+","CB+"

If I remove the quotation marks, the SSRS report works as intended.

Christian

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.238 / Virus Database: 270.11.33/2031 - Release Date: 04/02/09 06:09:00