How to pass multi-value parameters to DDS (data driven subscription)?
Using RS2005, how should multivalue parameters be stored in a database field so that a data driven subscription can properly read and use them? I have so far had no luck using syntax of: 1)parm1, parm2 2) parm1,parm2 Do single qutoes need to explicitly wrap the values? Can you please provide an example and a SQL INSERT statement using parm1 and parm2 to demonstrate what to store in the database field? Thanks!!
June 29th, 2006 9:14pm

Update: Also tried wrapping parms with single-quotes as follows: 'Parm1, Parm2' 'Parm1,Parm2' 'Parm1', 'Parm2' Thesefailed as well. :-(
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2006 3:28pm

In order to get multiple parameters in data-driven subscriptions you must have multiple columns per row returnthe individualparameter values. You then map each column to the same parameter name. -- Robert
July 3rd, 2006 2:32am

Thank you for the reply - but how *exactly* does one map each column name to the same paramter name? When I try this in a DDS, I do not see any way to map multiple columns to a single parameter? Using Report Manager to setup the DDS, it appears to only allow one column per parameter. Thank you- Matt
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2006 8:28pm

Currently, you can only achieve this mapping when making calls directly through the RS SOAP API. It cannot be done through report manager or management studio UI. -- Robert
July 3rd, 2006 8:50pm

Is there an example of how to do this somewhere? We use the report manager to create our data driven subscriptions today ... and I havent tried one of our reports with multi-value
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2006 12:34am

Hi, This is a functionality thatwould bemore than welcomein ReportManager. ReportManager should detect the presence of a string containing comas whenthe parameteris a multi-value parameter and parse it to make it a valid list of value. kind of the sametrick one have to use when passing a multi-value parameter to a procedure. Using custom programming to map multiple columns to the same parameter is just not practicable. Imagine I havea total of300 multi-value parameters spreadaccross 100 reportseach of these containing between 10 and 10000 values. Of course only a few of these parameters share the same values. This improvement is high on my wish list, it would save me a lot of work. Thanks, Philippe
October 6th, 2006 2:28am

You can't put something like 301,302 in a column and expect that to be passed to the report. You'd have to use the soap api as someone else has suggested, however during the setup of the data driven subscription, you can use the default value for your multi valued parameters and this will allow you to have a data driven subscription for reports with multi value parameters - except you have to use the defaults....
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2006 6:24am

Actually you can do this by the way you write your query. If you write your query like this (for example) and then match the parms to the data fields from this query. You can match the parm with the value in the field metric_codes which has multiple values in it as below. Hope this makes sense.... it works. Select 'daily' as interval, rptperiod.STARTDATE as pm_start_date, rptperiod.ENDDATE as pm_end_date, cr.CLIENT_CODE as client_code, cr.GROUP_PATH as group_path, 'CpuPctUsed,MemPagesFree,MemPhyCapacity,MemPhySpaceFree,MemVirPctUsed,MemPhyPctUsed' as metrics_codes FROM CLIENT_VW cr join RPT_PERIOD_VW rptperiod on period = 'previous month' where cr.CLIENT_CODE = 'ABC Company' and ( cr.GROUP_PATH like '%\ABC Company\%' ) group by cr.CLIENT_CODE, cr.GROUP_PATH, rptperiod.STARTDATE, rptperiod.ENDDATE, rt.res_type
November 3rd, 2006 1:45am

What you can do is create 2 hidden fields, one called IsScheduled - set the default to false. The second one is MultiList- again hidden. In your data driven subscription, set the IsScheduled to true, put in your comma list in the MultiList parameter and then in your stored procedure check to see if IsScheduled is true and handle the SQL after that.
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2006 10:32pm

Apparently passing in values like {A, B, C} only works when you configure the parameter values not to come from a dataset so that SSRS doesnt check for ValidValues.
February 14th, 2008 8:24pm

Robert, Can you post an example? Thanks, Stephen
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2009 4:46am

Hi, We have the similar case. We want to have a DDS for a multi value parameter. We tried one work around: for ex: Movies is a multi parameter value we want to pass in the report. Step 1: Create a new Dummy Hidden Report Param : DUMMYPARAM (String Datatype , Allow Blank) Set Available Value and Default Values as : Non Queried - which will be blank Step 2: Create a new Dummy DataSet : DUMMYDATASET as follows: SELECT MovieSet = variable FROM dbo.fn_ParseComma(@DUMMYPARAM) Note: the function "dbo.fn_ParseComma" is just returning the comma separated values into rows. Step 3: In Movies report parameter Set the default values as (From Query) And select the above created dataset i.e. DUMMYDATASET, and value field as MovieSet Deployed the report, did the DDS as a normal way and map the report parameter DUMMYPARAM with the database value (column) and for And For Movies param set the value as default. When we click on Finish it gives as error saying : this report requires a default or user-defined value for the report parameter 'Movies'. To run or subscribe to this report, you must provide a parameter value. (rsReportParameterValueNotSet) This workaround is working for another report deployed on the server, but it is failing for this report, we appreciate your urgent help on this. Thanks, Shailesh Gajare
April 27th, 2010 9:46am

Hi, I got the solution for this. There was one mistake. In the report parameter, The Movies Multi Value Param had a dependency on the Director Parameter and for Director Parameter we were having the Default Value as Null. As we were expecting to set the value for the director in DDS we kept this value as Null and that created problem. We changed the value from Null to Non Queried: And set the Value (Shailesh Gajare). The report worked. In the above post I mentioned that one of our reports was working. In that report for the dataset of multi value parameter, we were not having any variable, so there was no dependency on any other parameter. But in our case we had a dependency so we need to set the default value. You can override this default value in your DDS to select it from the database. I will be happy to help the guys, if they still have any issues. Thanks and Regards, Shailesh Gajare
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2010 8:08am

I have heard several people mention that you can use the SOAP API to map multiple columns to one parameter, but I have not seen any examples so I decided to give it a try. So far I have not been able to get it to work. I can use the SOAP API to create a data driven subscription that only maps one column to a parameter, but whenever I try to map multiple columns to one parameter I get a 'requested functionality is currently not supported exception'. Is this truly not supported, or am I just doing something wrong? Here is my C# code that is mapping multiple columns to one parameter: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.Services.Protocols; namespace DDTestWeb { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { ReportingServices.ReportingService2010 rs = new ReportingServices.ReportingService2010(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; string report = "/Test/DDTestReport"; string description = "Test data driven subscription"; ReportingServices.ExtensionSettings settings = new ReportingServices.ExtensionSettings(); settings.Extension = "Report Server Email"; ReportingServices.ParameterValueOrFieldReference[] extensionParams = new ReportingServices.ParameterValueOrFieldReference[8]; ReportingServices.ParameterValue to = new ReportingServices.ParameterValue(); to.Name = "TO"; to.Value = "email@email.com"; extensionParams[0] = to; ReportingServices.ParameterValue replyTo = new ReportingServices.ParameterValue(); replyTo.Name = "ReplyTo"; replyTo.Value = "email@email.com"; extensionParams[1] = replyTo; ReportingServices.ParameterValue includeReport = new ReportingServices.ParameterValue(); includeReport.Name = "IncludeReport"; includeReport.Value = "True"; extensionParams[2] = includeReport; ReportingServices.ParameterValue renderFormat = new ReportingServices.ParameterValue(); renderFormat.Name = "RenderFormat"; renderFormat.Value = "Excel"; extensionParams[3] = renderFormat; ReportingServices.ParameterValue priority = new ReportingServices.ParameterValue(); priority.Name = "Priority"; priority.Value = "NORMAL"; extensionParams[4] = priority; ReportingServices.ParameterValue subject = new ReportingServices.ParameterValue(); subject.Name = "Subject"; subject.Value = "Your sales report"; extensionParams[5] = subject; ReportingServices.ParameterValue comment = new ReportingServices.ParameterValue(); comment.Name = "Comment"; comment.Value = "Here is the report."; extensionParams[6] = comment; ReportingServices.ParameterValue includeLink = new ReportingServices.ParameterValue(); includeLink.Name = "IncludeLink"; includeLink.Value = "False"; extensionParams[7] = includeLink; settings.ParameterValues = extensionParams; ReportingServices.DataSource delivery = new ReportingServices.DataSource(); ReportingServices.DataSourceDefinition dataSourceDefinition = new ReportingServices.DataSourceDefinition(); dataSourceDefinition.ConnectString = "Data Source=DBServer;Initial Catalog=DB"; dataSourceDefinition.CredentialRetrieval = ReportingServices.CredentialRetrievalEnum.Store; dataSourceDefinition.Enabled = true; dataSourceDefinition.EnabledSpecified = true; dataSourceDefinition.Extension = "SQL"; dataSourceDefinition.ImpersonateUserSpecified = false; dataSourceDefinition.UserName = "username"; dataSourceDefinition.Password = "password"; delivery.Item = dataSourceDefinition; ReportingServices.Field[] fieldsList = new ReportingServices.Field[1]; fieldsList[0] = new ReportingServices.Field(); fieldsList[0].Name = "Practice1"; fieldsList[0].Alias = "Practice"; ReportingServices.DataSetDefinition dataSetDefinition = new ReportingServices.DataSetDefinition(); dataSetDefinition.AccentSensitivity = ReportingServices.SensitivityEnum.False; dataSetDefinition.CaseSensitivity = ReportingServices.SensitivityEnum.False; dataSetDefinition.KanatypeSensitivity = ReportingServices.SensitivityEnum.False; dataSetDefinition.WidthSensitivity = ReportingServices.SensitivityEnum.False; dataSetDefinition.Fields = fieldsList; ReportingServices.QueryDefinition queryDefinition = new ReportingServices.QueryDefinition(); queryDefinition.CommandText = "SELECT '0006' AS Practice1, '0172' AS Practice2"; queryDefinition.CommandType = "Text"; queryDefinition.Timeout = 45; queryDefinition.TimeoutSpecified = true; dataSetDefinition.Query = queryDefinition; ReportingServices.DataSetDefinition results = new ReportingServices.DataSetDefinition(); bool changed; string[] paramNames; try { results = rs.PrepareQuery(delivery, dataSetDefinition, out changed, out paramNames); } catch (SoapException se) { TextBox1.Text = se.Detail.InnerText.ToString(); } ReportingServices.DataRetrievalPlan dataRetrieval = new ReportingServices.DataRetrievalPlan(); dataRetrieval.DataSet = results; dataRetrieval.Item = dataSourceDefinition; string eventType = "TimedSubscription"; string matchData = "<ScheduleDefinition><StartDateTime>2010-05-24T21:55:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Monday>True</Monday><Tuesday>True</Tuesday><Wednesday>True</Wednesday><Thursday>True</Thursday><Friday>True</Friday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>"; ReportingServices.ParameterValueOrFieldReference[] parameters = new ReportingServices.ParameterValueOrFieldReference[2]; ReportingServices.ParameterFieldReference practice1 = new ReportingServices.ParameterFieldReference(); practice1.ParameterName = "Practice"; practice1.FieldAlias = "Practice1"; parameters[0] = practice1; ReportingServices.ParameterFieldReference practice2 = new ReportingServices.ParameterFieldReference(); practice2.ParameterName = "Practice"; practice2.FieldAlias = "Practice2"; parameters[1] = practice2; try { string subscriptionId = rs.CreateDataDrivenSubscription(report, settings, dataRetrieval, description, eventType, matchData, parameters); } catch (SoapException se) { TextBox1.Text = se.Detail.InnerText.ToString(); } } } }
May 25th, 2010 5:13am

Shailesh Gajare has hit nail on the head for me... You can pass in multiple parameters from your data driven subscriptions source dbo.table as First parameter column Second paramater column Parm1, Parm2, Param3 Parm11, Parm22 As long as you have default and available parameters in report set to non queried.. Thank you new to SQL server reporting
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2010 4:21pm

I have skimmed through this an have ran into this problem before but not with RS specifically, but with other ASP.NET apps I have built. Maybe this work around will help. In my database I created a table function I call udf_Split List. It looks like this: (Can't remember where I got this from, but it's not my own.) CREATE FUNCTION [dbo].[udf_SplitList] ( @List varchar(max), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn, @List) > 0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1))) Set @List = Substring(@List,Charindex(@SplitOn, @List) + len(@SplitOn), len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END Then, in my query I used the following parameter: QueriedField IN(select value from dbo.udf_SplitList(@Parameter,',') (I used commas to separate the list, but you can use whatever you like (;,|,*, etc.) For you @Parameter would be your DDS list parameter. Hope this helps. You can enter your list like Item1,Item2,Item3 etc.
September 29th, 2010 5:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics