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
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)
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christian Mauri Sent: Thursday, April 02, 2009 2:10 PM To: Ryan Creps Subject: [Tessitura Technical Forum] SSRS Help
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
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
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
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christian Mauri Sent: Thursday, April 02, 2009 2:30 PM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] RE: SSRS Help
From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com> Sent: 4/2/2009 4:16:32 PM
Ryan, That did the trick. Thanks again for your help.
Christian
Not a problem!
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christian Mauri Sent: Thursday, April 02, 2009 3:10 PM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] RE: RE: SSRS Help
From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com> Sent: 4/2/2009 4:41:29 PM
Ryan,
1 more question. In the SSRS report I am not seeing the parameter values displayed for the multi select member level strings. The other non multi paramters are showing up fine. I have the table setup properly as you demonstrated in your webinar:
=Fields!formatted_value.Value
any ideas?
Christian,
This has been found to be a bug in v8.0.0.3. This will be fixed in v9.0, and I will email you offline with a fix.
-Ryan
I have a similar problem described above but my multi-select passes a string of numbers rather than a string of varchars and applying any combination of the above changes does not lead me to success.
I want to add a multi-select to allow users to pick more than one fund_no (parameter is called fund_no_str).
SSRS - fund_no_str is set up as a string parameter, single-select, hidden, allow NULLs and blanks.
GSRSETUP - Fund parameter is set up as string, multiselect.
When two funds are selected Tessitura passes the string as '110,111'. Reporting Services reports "
Selecting one fund only works fine.
Can anyone help?
David Joyce (Sydney Opera House)
Hi David,
Good day.
we have the same problems too.
at last, I have to give up passing parameters from Tessitura, and create datasets for parameters from SQL reporting service.
so even without dropdown lists from Tessitura, you still can have them from SQL reporting service.
have fun
Ben
Guys,
I figured out the proper syntax to pass fund no as a string. It took me awhile but it can be done. I'll be in touch very soon with the correct syntax. Passing the multi selct in SSRS is different than Infomaker, so the SQL syntax of your charindex command is different.
Thanks,
Christian @ The Tech
Looking forward to seeing that syntax Christian - thanks in advance for posting.
David
Hi Guys,
good day.
I can make the sql reporting service working, but I cannot pass the multi-selection from tessitura to SQL reporting service.
here are pics and stored procedure.
David, good to see you posting on the forum!
As far as getting a multi-select parameter in Tessitura to pass to SSRS, everything looks ok from first glance, but obviously something is not quite right. Do you have the Available Values section in SSRS Report Parameters window set to reference a dropdown? This is probably the culprit, as you are able to pass a single fund_no value, but not multiple fund_no values. What is happening is that SSRS is validating the value passed from fund_no_str to the set of funds in the available values dataset. A single fund_no value works fine as the relation is simply equality (there is a fund_no of 10 that equals the @fund_no_str of 10, e.g.). However, once you get to a multi-select string (in Tessitura) and pass it to SSRS, SSRS is treating it as a single value and trying to validate it to the list of available funds. To SSRS, it’s a single value, so a fund_no_str of ‘1,32,4,5’ does not equal any of the fund_no (data) values in the dataset set in the Available Values (defined in the SSRS Report Parameter setup).
Try changing the Available Values to “Non-Queried”, leaving the field blank and see if that solves the issue.
Side note, the bug referenced previously here (that I say “fixed in 9.0”) was for the formatted_value column in the AP_GET_PARAMETER_VALUES resultset. It was not correctly returning a descriptive value when the datavalue was a multi-select string.
-Ryan Creps
Tessitura Network
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Joyce Sent: Thursday, January 21, 2010 2:56 PM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] RE: RE: RE: SSRS Help
· The 'fund_no_str' parameter is missing a value ".
From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com> Sent: 4/3/2009 2:05:12 PM
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. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Normal 0 false false false MicrosoftInternetExplorer4 Hey guys,
So I've attached a word doc with some screen shots. Here is how your SQL syntax should look like:
Normal 0 false false false MicrosoftInternetExplorer4
INSERT INTO #fund
SELECT fund_no
FROM t_fund
WHERE charindex(',' + convert(varchar,fund_no) + ',' , ',' + @fund_str + ',') > 0
My example shown on the word doc is not using fund_no, rather a custom table I developed, but the intent is the same. I needed to pass a multi value integer select from Tessitura to SSRS.I noticed you had multi select checked in the SSRS parameters and in my example I do not have that checked. Seems logical to check it, but mine is working without it being checked :-)
Let me know if I can be of more help,
That's bang on Ryan, thanks!
We are working towards an environment where we will allow users to run SSRS-based reports through Tessitura and in some instances run those same reports directly through our intranet (embedding the Report display and parameter objects inside our intranet page template).
Does the above illustrate that it's not possible to allow a multi-select dropdown for use in Tessitura as well as allow a populated multi-select dropdown in the Report Server window also were we to want to display a dropdown in the intranet view also?
Regards
hope this is the right way of 'joining' the SSRS HELP area .....
My first venture with SSRS - needed to modify SP TP_ORDER_ACK ot retrieve row and seat number info.
After learning that I couldn't change the data-source for the acknowledgment in InfoMaker - I went to SSRS
created a modified SP ( TP_ORDER_ACK_MOT - which returns row and seat ..) and tried it as a source in SSRS.
If I run the script below in SSMS it work fine:
execute dbo.TP_ORDER_ACK_MOT @order_start_dt ={ts '1900-01-01 00:00:00.000'} , @order_end_dt ={ts '1900-01-01 00:00:00.000'} , @season_str ='' , @perf_start_dt ={ts '1900-01-01 00:00:00.000'} , @perf_end_dt ={ts '1900-01-01 00:00:00.000'} , @mos_str ='' , @reprint_ind ='' , @habo_flag =0 , @exception_flag =0 , @list_no =0 , @mailing_dt ={ts '1900-01-01 00:00:00.000'} , @mailing_type ='' , @signer =0 , @label_ind ='' , @include_general_public ='' , @eaddress_type =0 , @eaddress_purpose ='' , @eaddress_market_ind_no ='' , @report_type ='O' , @p_order_no =725813 , @ob_no =0 , @balance_type =4
If I run it in SSRS Query builder it also run fine - after telling me the " EXECUTE is not supported ..... "
but if I try to execute it as a stored procedure ( SSRS ) it hangs on the date parameters ...
@order_start_dt ={ts '1900-01-01 00:00:00.000'}
I'm thinking it may run ok if I can just get past the syntax ......