How To Handle Multi Value Parameters Using SP As A Data Set?

May 8, 2008

Hello all,

I am using SP as my data set to generate report and there are some multi-value parameters which past as an array back to sql server. Since sql server can not handle the array pass in , how to handle it?

thx

View 3 Replies


ADVERTISEMENT

Passing Multi-Value Parameters To A Data Driven Subscription

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

How To Pass Multi-value Parameters To DDS (data Driven Subscription)?

Jun 29, 2006

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



View 22 Replies View Related

Multi-parameters Issue In Data Flow Task SSIS

Dec 4, 2006

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

View 1 Replies View Related

Reporting Services :: Using Multi-Valued Parameters With Data-Driven Subscriptions

Jun 27, 2009

I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.

Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:

paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))
FROM Reports..SalesForce t2
WHERE t1.SalesManagerID = t2.SalesManagerID
ORDER BY SalesManagerID
FOR XML PATH( '' )), 3, 1000 )

To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.

View 8 Replies View Related

SQL Server 2008 :: Handle Space Between Multi-value Parameter Values In SSRS?

Jul 8, 2015

how to handle space between multi-value parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.

View 3 Replies View Related

How Do You Handle SQL Parameters In An IN Clause?

Sep 8, 2006

Hi all,

if I have a SQL string that has an IN clause in its WHERE statement so that I only bring back rows for certains primary key values, how do I handle this with SQL Parameters without having to do any string concatenation?

Thanks in advance.

View 5 Replies View Related

How To Handle Optional Parameters When Called A Report Via URL

Jul 5, 2007

Please i have lots of reports, i would like to pass parameters, i may pass or i will not pass.



but all of my reports are using stored procs from sql server database.



please guys can some one give me an idea how can i have optional parameters defined in the stored procedure.



in the reports layout collections property where i have to specify the parameters need to run the report there how can i define. wether it is optional of required parameter.



i see the parameters collections object there are hidden, pass null or pass a default value or pass the info via query etc.



please help thank you very much for the information.

View 1 Replies View Related

Multi-Value Parameters

Apr 12, 2005

 I have a dataadapter on my asp.net page and am having issues passing values to the sql statement.  See below:
 
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp
INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID
WHERE (tblTemp.TempTime BETWEEN @From AND @To)
AND (tblTemp.SensorID IN (@Sensor))
ORDER BY tblTemp.TempTime
 
@Sensor is populated in vb with the selected values from a checkboxlist control.  I attempted to test this in the "Data Adapter Preview" window, but continually get an error.  Is there a way to pass multiple values to a parameter?
Any help would be appreciated, thanks!

View 2 Replies View Related

Multi-value Parameters

Jun 29, 2007

I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005.
I’m new to the product and struggling with the TSQL syntax.

(I have simplified my SQL statement for the purposes of this question)
I have a dataset below which has 1 parameter;

="SELECT * " &
" FROM dbo.Table" &
Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )

The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options.
I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.

Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?

Any help would be great.

View 1 Replies View Related

Multi-value Parameters

Jun 24, 2007

Now do you know how to take multi-value parameter array values and pass to Sql Stored Proc for summing ?

View 3 Replies View Related

Multi-value Parameters

Mar 9, 2007

Greetings - I see a few postings regarding problems regarding multi-value parameters, and see no solution responses.

Am attempting to filter on data using a multiple values drop-down parameter. Dataset has been created and runs correctly. The Multi-value parameter setting has been checked. When report is run and 1 value selected from drop-down, report runs fine. If multiples (or all) are selected, then receive "Query execution failed for dataset "mydataset" incorrect syntax near ','" error.

Can someone please advise a solution to this? Tks & B/R

View 6 Replies View Related

Multi-Value Parameters!

Nov 4, 2007



Hi every body!
I have two reports ,in first i have one multi value parameter .in first report i select more than one value ,then in second report i want to have these values becuse i have another multi value parameter ,when i join these reports via navigation
in parameters (in first report) i put the values of multi value parameter in fisrt report for value of multi value parameter in second report,but in this case fore example i have it:
in first reprt-> navigation->parameters->
ParameterName ParameterVaue
EmployeeID =Parameters!EmployeeID.Value(0)

but in second report i need all of my selections in first report not just first value

Can anybody help me?

View 1 Replies View Related

Multi-Value Parameters

Jan 21, 2008



I know that there have been a number of Posts with reference to Multi-Value Parameters, but none have helped me resolve my issue.

I am relatively new to SSRS, and use it in it's simplist form. i.e. I do not use xml code or SQL Statements, I just use the Menus.

I have created a Multi-Value Parameter (Report > Report Parameters) and have assigned a number of Non Queried Labels and Values. I have also assigned this to the Filter Tab via Table Properties.

When previewing, if I select each parameter individually and view the Report everything is fine. If I select one or more, the report only returns values for the first parameter listed.

Have I missed a step, is there a bug or do I just need to be trainied to use SQL Statements? (Hopefully not the latter!!)

Help would be very much appreciated.

Shodman

View 5 Replies View Related

Multi Value Parameters

Sep 5, 2007



I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.




Code Snippet
USE [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

View 5 Replies View Related

IIF And Multi-Valued Parameters

Jan 14, 2008

I have a MultiValued parameter called Owners, which stores Owner Ids.

I wish to set the value of a textbox to the following:

=iif(3 IN(Parameters!Owners.Value),"foo","bar")

but the "IN" is not recognised.

How can I achieve this functionality using an expression?

Thanks,
Kate

View 1 Replies View Related

Help With Oralce 8 - Multi-Value Parameters

Nov 8, 2007

I need help in creating a custom string for a multi-value parameter going against an Oralce 8 database.
I'm trying to build a string that looks like ('1401', 'HACT', '1504')
If I do it as an expression in a test report the results are perfect but in production it does not like it.

This is what I have based on some examples I have found but it complains it needs a )
These are the last few lines of the query

and f.shipto = a.shipto
and f.salesman in & " ('" & Join(Parameters!Salesrep.Value,"','") & "')"
GROUP BY a.company_id, a.whse#, f.salesman, .product_line, d.product_group, e.name
)
order by conoxx, whsexx, slsrepxx, sortcode

View 8 Replies View Related

Drill Down In Multi-value Parameters

Jul 21, 2006

Hi,

can anybody tell me if it is possible in RS2005 to create a multi-value parameter in wich it is possibility to drill down? Say i want to use a multi-value parameter to show data for one or more specific years or months. Then the multivalue parameter would contain a list of years, with a + sign next to them, making it possible to drill down to the specific months.

Any help greatly appreciated.

Christopher

View 5 Replies View Related

Blank Multi-value Parameters

Jul 19, 2007

Hi,



Lets say I have two multi-value parameters on a report. One is required, the other is not. The multi-value parameter that is not required is allowed to be blank. In the WHERE clause of my query I wanted to do this:






Code Snippet AND
EXISTS
(SELECT * FROM SomeTable AS ST_1
WHERE (MyCode IN (@My_Codes2)) OR @My_Codes2 IS NULL)








Of course I can't do that since multi-value parameters are not allowed to be null. I see that they are allowed to be blank, but how do I check for that? I tried






Code Snippet

OR @My_Codes2=''



That doesn't work. I can set a default value of 'None' and say:






Code Snippet

OR @My_Codes2='None'



Unfortunately this doesn't work. As long as the default value is 'None' it's fine. When I fill in the parameter I get the following error:



An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please note that I am not using a TSQL stored procedure here. It's just a plain old embedded SQL statement.



Thanks,

DD

View 3 Replies View Related

IIF And Multi-valued Parameters

Apr 15, 2007

Hi



i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.



basically, the report gets loads of data back from DataCube.



if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.



but

i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart



so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)



am doing this by trying to get the selected value or "xxxxx" if noting selected

to hide the second chart when only one category has been selected, tried



e.g. for the third chart

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

Parameters!LOOKUPITEMCategoryDescription.Label(2),

"xxxxx")



doesnt work - gives an #Error as a reult



But it works fine when there are > 2 categories selected



also



this works fine when there is 2 or >2 category selected

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

"more than 2",

"not more than 2")





it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!



help please

thx

View 4 Replies View Related

Linked Multi-value Parameters

Apr 3, 2008

Hi,

I have a report that needs to use two multi-value parameters that are linked.

The report shows stock availability for the production of trial kits and the requirement is to enter multiple quantities of multiple kits as shown below:

Parameter 1 Parameter 2
Kit A 10
Kit B 15
Kit C 25

Is there a way I can select multiple kits and then enter a specific quantity for each selection?

Any help or advice is greatly appreciated.

Thanks.

View 1 Replies View Related

Null Multi-value Parameters

Apr 26, 2007

How do you configure a multi-value parameter so it will allow the user to not enter the parameter?

I'm using integer multi-value parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".

How do you specify a multi-value parameter to allow "empty" or "null"?

View 5 Replies View Related

T-SQL (SS2K8) :: Appropriate Query For Multi Parameters?

Mar 13, 2014

I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.

Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.

For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.

SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName

[Code] ....

View 3 Replies View Related

Dynamic Query With Multi-value Parameters

Jun 1, 2008

Is anybody here knowing how to create a dynamic query based on a multi-value parameter?

e.g. there is a multi-value report parameter called names. For a static query, the where clause of a select statement likes the following

select * from students where name=@names

For the dynamic one, I tried something like the below, but it did not work.

="select * from students where name=(" & Join(Parameters!names ,',') & ")"

Any suggestion would be great appreciated.



Thanks,
KY

View 2 Replies View Related

Error In Using Multi Varied Parameters!

Dec 7, 2005

Hi,

View 8 Replies View Related

Display Multi Value Parameters In Textbox

Jun 8, 2007

Hello,



I have the following problem.

i have a report where you need to fill in a few parameters

start date, end date and subject selection.

the subject selection is a multivalue paramter (select all, param1, param2, param3)

the multivalue parameter is based on another dataset with paramid as value field and paramdescription as value label.



in my report i want to display something like this :

from startdate to enddate

summary for campaign(s) : selected parameters.



in the selected parameters is can display a =join(Parameter!campaig.value) but this only returns the 32bit guid.

instead i would like it to display the parameterdescription label.



anybody has some ideas ?



greetings

vince

View 1 Replies View Related

Ole DB Multi Value Named Parameters Problem.

Aug 30, 2005

I'm trying to use cascading report parameters using the mult-value select option.  The first parameter is used as input for second parameter.

View 6 Replies View Related

Multi-Value Parameters - Conversion Error

Jul 9, 2007

I have read that there isn't a simple way to use MVPs in stored procedures, however I'm simply using nested SELECT statements.

Select A.attr1, B.attr4

From( Select * from tb1) A, tbl2 B

Where A.attr1 = ((@param))

For some reason, I'm getting the "Conversion failed when convertng the nvarchar value ..." error. Is there some setting that I'm missing? This is just a text command, not a stored procedure. Has anyone had this problem before?

Erik

View 1 Replies View Related

Problem With Multi-valued Parameters Since SP1

May 2, 2006

Hi,

after the installation of SP1 I have a problem with multi-valued report parameters.
The option to select all values are gone.
Is there a bug within SP1?

Before the installtion of SP1 multi-valued report parameters works fine, the option to select all values was added automatically within the preview in Visual Studio 2005 and in published reports on the server.

The server was migrated from SQL2000 SP4 to SQL2005.

So long
Lemmi

View 41 Replies View Related

Setting Multi-value Parameters Using ReportExecutionService

Apr 24, 2007

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 12 Replies View Related

Passing Multi Value Parameters To CreateSubscription

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

Charts And Multi-valued Parameters

May 7, 2007

I am creating a Line chart report from a table. The source table includes a code column (String), date column (Datetime), and 12 statistic columns (Int). The dates are end of month dates only. ("1/31/2006", "2/28/2006","3/31/2006", etc.) There are about 8 different codes for each month. The statisic columns are the totals for the month for each code for a particular statistic.



PeriodEndDate Code Statistic1 Statistic2 Statistic3 Statistic4 ...
XX/XX/XXXX XX 999 999 999 999 ...



On my line chart I want to show one or more statistics for one year for one or more codes. (A line for each statistic for a given code) The report has multi-valued parameters for the codes and the statistics. I have figured out how to create the chart for one or more codes for a given statistic. How can I select one or more statistic? Can any one help me out?

Thanks,

Fred

View 1 Replies View Related

Multi-Value Report Parameters For Visability

Apr 11, 2007

I am trying to use a Multi-Value Report Parameter to determine Visibility, I found this to work great when I only had 2 values (0 or 1), but I have 5 now (0, 1, 2, 3, 4).



I can see what is happening, but I don't know how to write the Visibility Expression to get it to work for all conditions. I can clearly see that when more than one option is selected from the drop down box the Report Parameter goes from Parameters!ReportSelect.Value(0) to

Parameters!ReportSelect.Value(0)

Parameters!ReportSelect.Value(1)

Parameters!ReportSelect.Value(2)

Parameters!ReportSelect.Value(3)

Parameters!ReportSelect.Value(4)

one for each number of variables selected. I need to evaluate for all possible values and if any of them are the one selected I want it to be visable.



Currently I am using this expression on Visability:

=IIf(Parameters!ReportSelect.Value(0)= 1, FALSE, TRUE)

But this only works if the "=1" is the 1st one selected.



I need it to work for all 4 options, so that all selected will be visible.



Does this make sense?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved