Question On Passing Multi-value Parameter For Multiple Branches
Apr 6, 2007
Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.
Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))
ReportViewer1.ServerReport.SetParameters(paramList)
pInfo = ReportViewer1.ServerReport.GetParameters()
Let me know if you have any suggestions!
Thanks.
View 4 Replies
ADVERTISEMENT
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Jul 23, 2005
I have a query :Exec 'Select * From Receiving Where Code In (' + @pCode + ')'@pCode will contain more than one string parameter, eg : A1, A2, A3How can i write that parameters, I try use :set @pCode='A1','A2','A3'but get an error : Incorrect syntax near ','Please help meThanks
View 10 Replies
View Related
Jun 29, 2007
Hello,
I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.
Any help appereciated.
Thanks
View 1 Replies
View Related
Aug 13, 2007
I've seen some theads on this but cant seem to get this right. Report A has 5 parameters 2 of them are multi value-- when I click on part of Report A I want to jump to report B and pass all parameter values from report A. The single value ones work but multi value only passes on value and not all values selected-- what am I missing here? Do I need to pass the values in the multi value parameters in an array??
thanks
k
View 1 Replies
View Related
Aug 7, 2007
I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.
View 2 Replies
View Related
Mar 9, 2007
I have a multi-valued parameter that I want to pass to a subreport. The values are 11, 12, 13, and 14.
So here's what I've done:
1. For the properties of the clickable field of the first report, I have gone to the Navigation tab and chosen the subreport in the "jump to report" pulldown.
2. Then I clicked on the Parameters button and added a parameter name for the multi-valued parameter I am trying to send. For the Parameter value, I have tried 11,12,13,14. I have also tried =Split(11,12,13,14)
3. On the subreport, I create a report parameter with the same name, data type is string, multi-value is checked, and the available values pulldown is populated by a query.
So I run the first report and click on the link that brings me to the subreport, but all the subreport shows is the results for 11, not 12, 13, and 14.
How can I get the second report to understand that I want it to show all the records related to 11, 12, 13, and 14? If I bypass the first report and simply use the drop-down to choose 11, 12, 13, and 14 in the subreport, it works fine. I just can't seem to figure out how to correctly have the first report tell the subreport that it wants 11, 12, 13, and 14.
Thanks in advance for any advice!
Dan
View 4 Replies
View Related
Jul 2, 2015
I have a sp where query is as below.
DECLARE @ServerCIName varchar(5000)
SELECT * FROMÂ dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
How do I query the data?
View 6 Replies
View Related
Mar 9, 2014
How do I pass the multiple value in the single parameter? Here is the select simple select statement with two fields (ID and Map_Area):
SELECT *
FROM TestParamOne
Here is the output:
ID Map_Are
12345KK45
657463IIY7
34345FGD
44342DFRE
4646DSAW
424245DSAW
12121DSAW
5753FRDE
575737FRDE
1121FRDE
1121F5FR
646462F5FR
8568F5FR
Here is my simple stored proc. Right know I only can execute with one value at the time but I am trying to run the stored proc with more than one value.
CREATE PROCEDURE TestParam1
@Map varchar(4)
AS
BEGIN
SET NOCOUNT ON;
SELECT ID, Map_Area
FROM TestParamOne
WHERE Map_Area=@Map
END
GO
View 7 Replies
View Related
Aug 20, 2007
Here's tricky one.
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
Essentially, I want to create a for each loop.
Any ideas?
View 4 Replies
View Related
Jan 11, 2005
Hi,
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
http://search1.workopolis.com/jobshome/db/work.search_cri
View 1 Replies
View Related
Oct 16, 2007
Hello,
I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.
Thanks a lot for any feedback!
View 6 Replies
View Related
Aug 13, 2015
we are using SSRS 2012.Oracle 9i as back end database. Select A,B,C from view where A in (Param1) in above query when I am passing 1 value getting output in 30 sec. when I passed two values getting time out error in SSRS. how to pass multiple values in Oracle 9i in SSRS report.
View 4 Replies
View Related
May 21, 2014
Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?
View 1 Replies
View Related
Feb 4, 2008
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
View 4 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
Dec 1, 2005
I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine.
View 6 Replies
View Related
Feb 15, 2007
Hi folks,
I am trying to create a SSRS subscription from an asp.net app. I collect the parameter values from the client and make the arrangements to send them to the CreateSubscription method. When the report has single value parameters, my code works perfectly. But if there is a report param with multiple values, I get the error
System.Web.Services.Protocols.SoapException: Default value or value provided for the report parameter '<paramName>' is not a valid value
My parameter is string type and supports multiple values. I am passing the following string as its Value:
AVA,BEL,CAL,CAP,CCA,CEL,COC,COM,ECE,EDT,EMC,EMT,EPB,EPC,EPM,EPO,EPS,ETB,MET
where each element separated by comma is a valid param value. Here´s the code I use to set the params values before the call to CreateSubscription:
Dim ReportParams As New NameValueCollection
ReportParams = Session("ReportParams")
NumeroParametros = ReportParams.Count
Dim parametrosRpt(NumeroParametros - 1) As ReportService2005.ParameterValue
For i = 0 To NumeroParametros - 1
Dim parameter As New ReportService2005.ParameterValue()
parameter.Name = ReportParams.GetKey(i)
parameter.Value = ReportParams(i)
parameter.Label = Nothing
parametrosRpt(i) = parameter
Next
RS.CreateNewSubscription(Session("Reporte"), "Some description", scheduleXml, txtPara.Text, _
txtCopia.Text, txtCopiaOculta.Text, txtAsunto.Text, comboFormato.SelectedValue, memoCuerpo.Text, _
parametrosRpt)
Getting nuts on this one. Any help/code samples will be greatly appreciated.
Cato
View 9 Replies
View Related
Dec 20, 2007
Hello,
I have report A and Report B. In Report A I am using the Jump to Report functionality to go to Report B. I have a multi-valued parameter in botht the reports. So In Parmeters I am giving
Parameters!xx.value to pass the value from Report A to Report B. The multi valued parameter has values 1,2,3,4 in both the reports.
I have a scenario where I need to pass a value of 5 to the report B. When I try to hard code the value I run into error while going to the second reeport saying parameter not declared.
Can any one help me with this. Its very urgent.
Thanks,
SqlNew
View 1 Replies
View Related
Apr 24, 2007
Hi,
I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.
As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.
Has anyone tried this before?
Thanks,
Dan
View 1 Replies
View Related
Nov 6, 2007
I have designed a report that allows the user to select multiple values to be passed to SSRS. When I deploy and run the report from Report Manager I can select 1 to n values and then run the report. It works great. Now I attempt to call Reporting Services from my Web Site. I format the URL and attempt to pass the mutliple values in the URL. An example would be http://YadaYada&rs:Command=Render&rs:Format=HTML4.0&rcarameters=false&OrderTypeID='1,2' The report uses a Stored Procedure that is expecting @Ordertype Varchar(1000). And Yes, the Stored Procedures handles the multiple values being passed to it. What I receive is an error message (rsReportParameterTypeMismatch). Any suggestions on how to pass these values?
Thanks in Advance
Tom
View 2 Replies
View Related
Dec 1, 2004
Hi,I have five tables p,pm,m,i and bu. Tables 'pm' and 'm' are a branch of table 'p', and table 'bu' is another branch of table 'p'. I know how to run a query to get through the first four tables 'p','pm','m' and 'i' in the manner:
Code:
sql="SELECT p.id,p.project_name, p.fngp_item,p.project_leader,p.project_lead_center,p.project_location,m.milestone_name,i.keyed_name" & _", i.name, m.milestone_leader, m.mgr_opinion FROM PROJECT p INNER JOIN PROJECT_MILESTONE pm ON p.id = pm.source_id INNER " & _"JOIN MILESTONE m ON pm.related_id = m.id INNER JOIN [IDENTITY] i ON m.milestone_leader = i.id WHERE m.mgr_opinion='75' " & _ "order by p.project_location, p.project_lead_center"
But when i find an item where m.mgr_opinion is equal to 75 i don't know how to include in the query the item that belongs to the table 'bu', because it is in a different branch.Can anybody help me on that? Thanks
View 2 Replies
View Related
Jul 26, 2004
Okie, this one has me a little stumped so bear with me an I will explain as best I can....
I have a family tree database...
The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).
Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.
If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.
I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.
View 8 Replies
View Related
Jan 16, 2007
Hi,
In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this.
Thanks in advance
View 2 Replies
View Related
Sep 5, 2007
I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:
Code Snippet
SE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]
(@Region int = Null)
AS
BEGIN
SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,
Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,
ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,
Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,
Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,
Forecast.SalesPerson_Purchaser_Code
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN
RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN
(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget
) AS Forecast INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON
Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON
Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND
Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND
Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code
WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007)
END
what am i doing wrong?
View 1 Replies
View Related
Jan 15, 2008
Hi:
I am building a report and have a few parameters. One of this parameters is set up as Multi-Value. When I only select one value everything is running fine. But when I select multi values I get an error saying I must declare my variable.
Any idea why this is happening?
Ben
View 3 Replies
View Related
Sep 3, 2007
hi,
i read from this forum that to pass array to report is using multi value parameter . my problem now is
1. can i pass multi dimension array.
2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table.
rgds,
charles
View 1 Replies
View Related
Dec 11, 2006
I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction
(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)
AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)
RETURN
View 15 Replies
View Related
May 27, 2008
Is it possible for an AND statement to take mulitple parameters? See below code:
SELECT tblQuestion.Question, tblAnswer.Answer
FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID)
WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14)
Is something like this possible?:
AND (tblAnswer.QuestionID = 14, 26)
Thanks
View 2 Replies
View Related
Jun 23, 2007
OK Guys....
If you answer this one, you will save my life...and you WILL be the MAN or WOMAN!!!!
Problem: I have a set of 24 matrix's that need to calculate the difference between the last two years and display in a field to the right of the last rendered column. Since I have been struggling with this, let's just assume there is no better way than how I currently have it set up. (one table that does the calculations for me and I set one field on the report to display the most recent two columns difference in my report)
what I can not figure out is: when I choose one of my parameters the report displays the information I want...but when I choose more than one...well there is the problem....
In order to obtain the most help for myself I will ask this in the most general way possible so as not to get bogged down into my specific solution...
Desired Result: How to pass all my parameter values from my multi-value parameter during runtime to a SQL Stored proc from my dataset within Reporting Services at runtime, Match the parameter to the field, get the result and store it in a variable, then do it again and add the second to the first within the variable, and so on and so on , until all of the parameters are used. Then sum the values and display in a field.
HELP, HELP, HELP Please....
View 3 Replies
View Related
Jun 28, 2007
Hello,
I have a multi-value parameter field and each item comes from a table in the DB.
This multi-value parameter allows to select all or some of the items.
What if I don't want to select ANY item??
I read in the forum the several tray to do the same but the answers are not so clear..
If I say that at the end is a bug in SSRS... AM I right?
Thank you
Marina B.
View 1 Replies
View Related
Apr 24, 2007
Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:
WHERE ( [Field1] = @Field1_Filter OR @Field1_Filter IS NULL )
How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.
I also don't know how you would check for the "not entered". Someone suggested the following SQL:
WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_Filter IS NULL )
This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".
How is this supposed to be done?
Thanks for any insight
View 12 Replies
View Related
Mar 19, 2007
hi,
i'm trying to perform a query against a db2 database like this:
SELECT ... FROM ... WHERE (field IN (?))
Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:
An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension
But when i type the query like this
SELECT ... FROM ... WHERE (field IN ('value1','value2'))
it executes flawlessly.
I am using the IBM ole db driver for db2 if that matters
Can anyone help me?
View 5 Replies
View Related
