.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Setting multi-value parameters using ReportExecutionService

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Does anyone know how to set multi-value parameters using ReportExecutionService in C#?   If I have   rsExec = new ReportExecutionService2005.ReportExecutionService(); . . . ReportExecutionService2005.ParameterValue[] parameters = new ReportExecutionService2005.ParameterValue[2];   parameters[0] = new ReportExecutionService2005.ParameterValue(); parameters[0].Label = "Report_Begin_Date"; parameters[0].Name = "Report_Begin_Date"; parameters[0].Value = "4/15/2007";   parameters[1] = new ReportExecutionService2005.ParameterValue(); parameters[1].Label = "SalesID"; parameters[1].Name = "SalesID"; parameters[1].Value = ???  //Here, this parameter should accept multiple values like 200, 201, 202, etc.   rsExec.SetExecutionParameters(parameters, "en-us");   How would I set the SalesID to take multiple values?

View Complete Post

More Related Resource Links

Report parameters bomb out using wildcards with multi value parameters

SQL Server 2008 Latest release I have a report that I have setup (using BIS Visual Studio 2008) for user required parameters (multi valued possible). I have tried adding the like value in the report parameter values in Visual Studio and it just does not like it, example -  LIKE ('A%'). If I enter a valid value without wildcards it works fine Example employee id # A001234. Anyone have any ideas as to a solution?   SELECT        DATAAREAID, TRANSDATE, JOURNALID, PRODID, EMPLID, HOURS, HOURPRICE FROM            Production_Direct_Labor_Hours WHERE        (DATAAREAID = @DATAAREAID) AND (HOURS > 0) AND (PRODID = @PRODID) AND (EMPLID IN (@EMPLID)) AND (TRANSDATE BETWEEN @STARTDATE AND                          @ENDDATE)

Multi Value Parameters in SSRS 2005

Hi Guys, I am having a parameter 'Employee' and it is multi valued. This parameter gets values from Dataset 'Emp_Data'. It has the follwing query: Select Employee_Code, Employee_Name from Employee_Table. I tried to do this but didnt get What I exactly want. What I have done is : IN the Report Prameters tab, went to Default Values section and selected From Query option and given the dataset name as 'Emp_Data' and Value Field as Employee_Code. Now When I view it in the Preview tab. I am getting as this in the Parameter Employee text box: Employee - James, Ross, Steve, Mark, Kevin........ For some reason, the client did not like this and they wants to see default as ALL Employees (instead of all those names displayed in the text box). So Is there a way to make it as ALL Employees as default (when all employees are selected). Note : I tried in this method also : Select 'ALL' as Employee_Code, 'All Employees' as Employee_Name                                         Union                                  

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

Linked Reports - Multi-Select Parameters


I have searched for this but have not found any answers, or similar questions even. I have a report with several multi-select parameters. These parameters are populated by datasets that use stored procedures. When I go to "Properties" and "Parameters" of the linked reports, they do not populate the drop-down menu for the parameter, the parameter is simply blank and has an area for me to manually type in data. Is there any way to force these parameters to populate with a selectable list of options? Like would occur when running the report from the front-end interface, or setting up a subscription.


Pass Multi-Value parameters to Stored Procedure problem



I am trying to pass multi-value parameters to stored procedure to filter data, but seems it does not work.

Stored procedure:


ALTER PROCEDURE [dbo].[test]

@StartDate DateTime,
@EndDate DateTime,
@FirstName varchar(8000),
@LastName varchar(8000),
@Location varchar(8000),

SELECT [Item No_],[Sales Staff],[Location Code],[Date],
[Price],[Quantity],[Item Category Code],[Product Group Code]FROM [Trans Sales Entry]
cte2 as
SELECT [ID],[First Name],[Last Name] FROM Sta

JDBC - stored procedure won't allow setting parameters by name



When I try to call CallableStatement.setString( name, value ) it throws an exception. Is there something I have to do to either change the parameter names or set them up? I use the full "@Beginning_Date" as the name.

thanks - dave

Very funny video - What's your Metaphor?

Issue with multi valued parameters in SSRS using Oracle data source


Hi All,

I have a dataset which is getting data from oracle datasource and my Dataset query expression is

="select To_Char(Time_Stamp,'Month') as Month, Node_Name as Device,Connection,MSNAME,Monitor,Avg as Average,Max as Maximum FROM OracleDataVW where ((monitor='X' and msname in ('Y')) or (MONITOR = 'CPU Utilization' AND MSNAME = 'utilization')) and TO_Char(Time_Stamp,'Mon YYYY') in  ('"+ Join(Parameters!Parameter1.Value,",") +"')"

My parameter is multiValued parameter. The above query is working good if i select one value,however if select more than one value or all it is not giving me the data. I tried to put Ltrim and Rtrim in Join but it is giving me another syntax error.

Please help me with this

how to call multi-value parameters in SSRS from asp.net application?


hi all

i am trying to call an SSRS report (in VS 2005) from my asp.net (web application) implemented in VS 2008 (don't ask me why, i am kind of confused by this weird arrangement too). report has been deployed to report server and it contains a multi-value parameter.

when i call report from asp.net app, i set the value for this multi-value parameter as a string and all selected values are separated by comma. i.e. i have 5 options (a, b, c, d, e), and i selected first two, the string is then "a,b".

the multi-value parameter on the report side is defined as: Multi-value selected, Allow blank selected, Avaliable values are from query (a dataset that contains a, b, c, d, e) Value field and Label field are both mapped to dataset filed (there is only one field in the dataset). the default values are from query too (same dataset). value field is mapped also.

i have two problems:

1: it looks to me that all available values are selected on the report side, regardless what i really selected and passed over (when i turn on paramters).

2: when i turn off parameters, i need to display what values being selected as a string, the join statement, join(Parameters!TeamLead.Value), gives a list of all available values for the multi-value parameter, however, i only need selected values. h

Setting where parameters within Linq DataSource Control Markup


Hi everyone!

I've got a Linq datasource hitched up to a Formview control for clients to edit some data.  I'm having trouble setting the where parameters withing the control so it show the right set of data.

Here's the markup:

 <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="DataClassesDataContext"
            TableName="ClientComments" EnableUpdate="True">
                <asp:SessionParameter DbType="Guid" Name="userID" SessionField="userGUID" />
                <asp:Parameter DefaultValue="1" Name="Product.productCategoryID" Type="Int32" />
                <asp:Parameter DefaultValue="True" Name="Product.active" Type="Boolean" />

Upon running right now, it's completely ignoring the where parameters.  What am I missing here?

multi value parameters with ODBC connections


I am trying to pass in a multivalue value parameter as a report parameter.  I am able to preview it in the "Data" tab in Design view with the "Run" icon.  On the "Preview" tab, I am able generate the data for the 2 parameters.  But, when I try to view the report, I get an error.  It states, "An error occurred during local report processing.  An error has occurred during report processing.  Cannot add multi value query parameter '?' for data set 'EMS' because it is not supported by the data extension."

I have found some online suggestions that recommended joining the parameters.  So, on the "Parameters" tab of my Dataset, I have added =Join(Parameters!Location.Value, ", ").  This is pulling the values from the other dataset I'm using.

I also tried putting the entire select in the "Parameters" tab of the Dataset, sort of like this "=SELECT <column> FROM <table> WHERE <column> in ('" & Join(Parameters!Location.Value, ", ") & "')".  But, this doesn't work.  Maybe I didn't do it correctly.

I need to use ODBC connections because we need to keep our SQL environments abstracted as the reports are moved from development through to production.

Any suggestion

Setting default parameters in dataset-populated pick lists


Visual Studio 2008 with R2 added. At this point I'm only using Preview (undeployed reports).

While I can easily set a parameter when the user has to supply a value for the report parameter, it is not at all clear how to set the defaults (and expect them to work) when setting the Available Values to be populated from a DataSet--with or without multiple values. I have seen the suggestion to use the pick-list dataset as a way to populate the default values list but this does not work.

I understand how to create a query that can accept "all" as an alternative input parameter to return all rows from the product, but shouldn't we be able to selectively choose from the list of valid parameter values by adding them as values in the list of defaults?


William Vaughn
Mentor, Consultant, Trainer, MVP

"Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)"

Please click the Ma

ReportExecutionService.Render multi page reports and secondary streams (2008R2)


Hello.  I found the example that uses 'streamIDs' to determine the number of pages in the rendered report.  However, steamIDs always returns a zero length array.

The workaround to get all the pages in the report:

  pages = new Byte[0][];
  numberOfPages = 1;
  byte[] currentPageStream = new byte[1] {0x00}; // put a byte to get the loop started
  while (currentPageStream.Length > 0)
   deviceInfo = String.Format(@"<DeviceInfo>" + _deviceInfoAddl + "<StartPage>{0}</StartPage></DeviceInfo>", numberOfPages);
   currentPageStream = _renderEngine.Render(_renderFormat.Name, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
   if (currentPageStream.Length == 0 && numberOfPages == 1)
   renderException = EnumRenderException.ZERO_LENGTH_STREAM;

Passing Multi-Value Parameters between reports



I have two Reports PropertySearch and PropertyDetails. In PropertySearch I have  2 parameters PropertyNameSearch (textbox) , City (multi-select from a drop down). It takes some propertyName and Few Cities as input and displays a list of properties matching these two. So the list has a link to PropertyDetails for which I just need PropertyID that passes internally to the PropertyDetails. Everything works perfect.

But when I was in second report I want to go back to the first report ( which was displaying results with based on parameters) to choose a different property. So for this I have created the parameters @PropertyNameSearch , @City in my second report and I set it to hidden. Now I'm passing these two values to the second report with actual parameter PropertyID.  I have placed a link to first report in the second report that passes the @PropertyNameSearch and @City to first report. I can pass @PropertyNameSearch successfully but my problem is I cannot pass multi-value parameter @City between two reports.

Any help or suggestions ?



Pass parameters into multi stored procedures



I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :


        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

Pass parameters into multi stored procedures



I have a code that want to call 2 stored procedures with a sqlcommand; I'm used :


        string sqlcmds = "EXEC ShowDetailsSp;EXEC ShowBannersSp;";
        cmd = new SqlCommand(sqlcmds, sqlcon);
        cmd.Parameters.AddWithValue("@Number", number);
        cmd.Parameters.AddWithValue("@Code", Code);

each stord procedure has a parameter :

@Number is for ShowDetailsSp and @Code is for ShowBannersSp, but when I run my page give error :

Procedure or function 'ShowDetailsSp' expects parameter '@Number', which was not supplied. 

Setting method parameters to accept only string or Type.


I have a dictionary object I'm working on that can take keys of either of two types: string or System.Type. Currently I have TKey set as object, and then explicitly check the type. Is there a better way to handle this?


as an example, in the below code, I want to ...

  replace IDictionary<object,IDataTranslator> with IDictionary<string or Type,IDataTranslator>

  replace the two internal dictionary with one, that is only string/Type - that way I don't have to do any redundant type checking?


//stores data for specialized/default translations between types/strings
//IDataTranslator is a custom interface used in the library
public class translators : IDictionary<object,IDataTranslator>
 //holds the string-based translators
 protected Dictionary<string,IDataTranslator> m_translators_s;
 //holds the dictionary based translators.
 protected Dictionary<Type,IDataTranslator> m_translators_t;

Linq: how to share parameters between lambda expressions ?

When using Linq to objects, you will quickly feel the need to pass some parameters from a method to another but it's not so easy because each Linq method is not calling the following one. In a Linq sequence, each method is using the result computed by the previous one. So, local contexts are not visible from one method to another.
The compiler is using two technical different ways to let parameters go out of a method.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend