locked
How to pass multi-value parameters to DDS (data driven subscription)? RRS feed

  • Question

  • 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!!

     

     

    Thursday, June 29, 2006 6:14 PM

Answers

  • In order to get multiple parameters in data-driven subscriptions you must have multiple columns per row return the individual parameter values.  You then map each column to the same parameter name. 

    -- Robert

    Sunday, July 2, 2006 11:32 PM
  • 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

    Monday, July 3, 2006 5:50 PM

All replies

  • Update:

    Also tried wrapping parms with single-quotes as follows:

    'Parm1, Parm2'

    'Parm1,Parm2'

    'Parm1', 'Parm2'

    These failed as well.   :-(

     

    Friday, June 30, 2006 12:28 PM
  • In order to get multiple parameters in data-driven subscriptions you must have multiple columns per row return the individual parameter values.  You then map each column to the same parameter name. 

    -- Robert

    Sunday, July 2, 2006 11:32 PM
  • 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

     

     

    Monday, July 3, 2006 5:28 PM
  • 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

    Monday, July 3, 2006 5:50 PM
  • 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
    Tuesday, August 22, 2006 9:34 PM
  • Hi,

    This is a functionality that would be more than welcome in ReportManager.

    ReportManager should detect the presence of a string containing comas when the parameter is a multi-value parameter and parse it to make it a valid list of value. kind of the same trick 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 have a total of 300 multi-value parameters spread accross 100 reports each 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

    Thursday, October 5, 2006 11:28 PM
  • 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....
    Thursday, November 2, 2006 3:24 AM
  •   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

    Thursday, November 2, 2006 10:45 PM
  • 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.
    Tuesday, November 21, 2006 7:32 PM
  • 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.

    Thursday, February 14, 2008 5:24 PM
  • Robert,

    Can you post an example?

    Thanks,
    Stephen
    Thursday, April 2, 2009 1:46 AM
  • 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

     

    Tuesday, April 27, 2010 6:46 AM
  • 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

    Wednesday, April 28, 2010 5:08 AM
  • 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();
    }
    }
    }
    }

     

     

     

    Tuesday, May 25, 2010 2:13 AM
  • 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
    • Proposed as answer by Kt555 Friday, September 10, 2010 1:23 PM
    Friday, September 10, 2010 1:21 PM
  • 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.

    Wednesday, September 29, 2010 2:23 PM
  • I found a really lame work-around for this issue if you only have ONE set of values to send in to the report.  I only needed to send in one list, so this worked for me:

    Before setting up your subscription, go to Properties--> Parameters and add your list of values as the default, separating each with a carriage return.  The subscription can then be set to use that default value.

    Wednesday, December 14, 2011 9:37 PM
  • This works if you have default values but not if you are pulling from a table.  I'm having trouble getting this to work pulling from a column in my table.  The subscribers go to an asp form and pick a report and selects the parameters that then feed into a table that I am using for subscriptions but the subscription is not reading the values in.

    Any ideas or solution?  I have tried several things and nothing is working.  I am using 2008 R2.

    thanks,

    Mike


    Mike
    Monday, December 19, 2011 3:15 PM
  •  

    I got this working based on what Shailesh Gajare wrote, I didn't use the exact same methodology.  Here is what I did, it is in SQL 2005.  First the problem: I had a table feeding a data driven subscription and I wanted to feed a multi select parameter a comma seperated list from that table (basically how you would expect it to work).  what I did was add 2 hidden parameters to the report.
    They should go at the top of the parameter list because @TestCenterID is dependent on these 2 hidden params.

    @Subscription - Hidden, Boolean, Default = Non-queried (false)

    @DummyParam - Hidden, String, Default = Non-queried ('0')

    The parameter that is the multi select is

    @TestCenterID - Multi value, Integer - Available values from query, Default values from query.

    Query for that parameter looks something like this

    If @Subscription = 'False'
    Begin
     SELECT
      TestCenterID
      ,CenterName
     FROM
      Unity.dbo.TestCenter WITH (NOLOCK)
     ORDER BY
      CenterName
    End
    Else
    Begin
     Select
       TestCenterID
       ,CenterName
     FROM
      Unity.dbo.TestCenter WITH (NOLOCK)
        where
      TestCenterID in (SELECT Data as TestCenterID FROM dbo.SplitSSRSMultiValueParam(@DummyParam,','))
    End

    The function SplitSSRSMultiValueParam takes a comma seperated list and returns it as a row by row value.

    Here is that function

    IF OBJECT_ID (N'[dbo].[SplitSSRSMultiValueParam]') IS NOT NULL
        DROP FUNCTION [dbo].[SplitSSRSMultiValueParam]
    GO

    CREATE FUNCTION [dbo].[SplitSSRSMultiValueParam]
    (
     @RowData varchar(max),
     @SplitOn NVARCHAR(5)

    RETURNS @RtnValue table
    (
     Data varchar(max)
    )
    AS 
    BEGIN
     DECLARE @Cnt INT
     SET @Cnt = 1

     WHILE (CHARINDEX(@SplitOn,@RowData)>0)
     BEGIN
      INSERT INTO @RtnValue (data)
      SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1)))

      SET @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+1,DATALENGTH(@RowData))
      SET @Cnt = @Cnt + 1
     End
     
     INSERT INTO @RtnValue (data)
     SELECT Data = @RowData

     RETURN
    END

    Basically your multiselect parameter is using the dummydata parameter as the data source if its a subscription. 
    So when you go to set up the data driven subscription, set Subscription to 'True' and your comma seperated list should
    feed into the DummyParam Value.  The @TestCenterID which is your actual multi select list should be set to use the default.

    1 Other thing to note I found that I had to set defaults to all the parameters to get the data driven subscription to intially work
    but after that I actually turned off one of the parameters and set it to null and used data from my table to populate it in the data
    driven subscription and it still worked.  Just note to initially get it to work though I had to set all the defaults.


    • Edited by SimonH01 Wednesday, March 7, 2012 1:53 PM
    • Proposed as answer by SimonH01 Wednesday, March 7, 2012 1:55 PM
    Wednesday, March 7, 2012 1:52 PM
  • You can do this with one additional string parameter. Just set the default to 0 or something to signify to the sql that it should look at the multi-pick parameter:

    where (
            (
            objectname.columnname in(dbo.fn_split(@multipickparam))
            and @stringparam = '0'
            )
          or objectname.columnname in(dbo.fn_split(@stringparam))
          )

    Tuesday, January 7, 2014 11:20 PM
  • You can do this with one additional string parameter. Just set the default to 0 or something to signify to the sql that it should look at the multi-pick parameter:

    where (
            (
            objectname.columnname in(dbo.fn_split(@multipickparam))
            and @stringparam = '0'
            )
          or objectname.columnname in(dbo.fn_split(@stringparam))
          )

    Correct, the issue at hand is the CHECKING...  RS checks for a list and unless its a single value or a list of VALID values is wont work.

    its using the query to see if its a list in the underlying selection data set... if its not in there or its not a list of those it says invalid parameter.  You trying to add commas or any other delimiter wont work.  Simply create another dummy hidden param that runs off a string to pass the data(your single field representing a list) to your stored proc, parse it inside there.



    • Edited by Mike5th Wednesday, January 4, 2017 5:15 PM
    Wednesday, January 4, 2017 5:12 PM