Passing Multi-Valued Parameter To Subreport

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


ADVERTISEMENT

Passing Multi-valued Parameter To A Sp

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

Reporting Services :: Multi-value Parameter Not Passing In Subreport Parameter List

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

Transact SQL :: Passing Multi-valued Data As Single Parameter In Query

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

Passing Multi-Valued Parameters Between Reports.

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

Referencing A Multi-valued Parameter In A Dataset

Oct 22, 2007



Hello,
I am attempting to create a cascading parameter.
Parameter's 1 & 2 are multi-valued.
Datasets 1 & 2 supply Parameter's 1 & 2.
The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'.
The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1.
Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing.
What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?

Any help is appreciated.

View 2 Replies View Related

Setting Defaults On A Multi-valued Parameter

Jan 27, 2006

I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.

Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.

I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...

Trent

View 3 Replies View Related

Parameterizing Multi Valued Parameter In Data Set

Aug 16, 2007

Hi,
I am creating a data set for a ROLAP report which executes a dynamic SQL using EXEC. I am finding problems parameterizing multi-valued report parameters in the SQL being executed through EXEC.
To consider a sample code:

EXEC('select * from country where country_key in ('+@country+')')

where @country is a multi-valued report parameter.

Regards,
Emil

View 7 Replies View Related

Inline Table-valued Function With Multi-value Parameter

Jul 18, 2007

Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View 7 Replies View Related

Expanding The Multi-Valued Parameter Dropdown Menu

Aug 14, 2007

Hello Everyone,
I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects.
Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.

Thank You!!!

View 2 Replies View Related

Returning All Selected Values In Multi-valued Parameter

Feb 15, 2007

How does one return all selected values in a multi-valued parameter? Right now i have a filter on the dataset where

(Expression)

=Fields!LOCATION_ID.Value

(Operator)

=

(Value)

=Parameters!Loc.Value(0)

This is just giving me data from the first value that is selected in the multi-valued dropdown. I need all returned from the parameter. Any ideas.

View 4 Replies View Related

String Type Multi-Valued Parameter Issue

Jul 3, 2007

Hi There.



I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?



Thanks, Mike

View 9 Replies View Related

How To Change Report Name Based On Multi Valued Parameter

May 2, 2008

Hi All,

I'm having trouoble with changing my report name when I swtich to different parameter values.

I use the following expression.


IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))


This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.

Is there any solution for this?

Thanks

View 1 Replies View Related

Parameter Passing From Master To Subreport

Dec 9, 2005

Is it possible to pass a multi-value parameter from a master report to one or more subreports? If so, how?

View 12 Replies View Related

Error With Multi-Valued Report Parameter Using Stored Procedure

Dec 19, 2007

Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:

CREATE PROCEDURE spprodsales
@productid int

AS

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

RETURN


And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

So, can anyone please help me out possibly using the same stored procedure as above.

Thanks,
Kripa

View 5 Replies View Related

Passing DateTime Parameter To Subreport (Error)

Feb 21, 2007

Hi guys,

I have managed to pass a string parameter form the main report to the subreport with no problems, but when i try and use the same method to pass a datetime type, i am issued with the following error messase:

"[rsErrorExecutingSubreport] An error occurred while executing the subreport €˜SubReportName€™: The value provided for the report parameter 'MasterDate' is not valid for its type."

I cant see why it work for strings, but not for datetime. Any suggestions will be very much appreciated

View 7 Replies View Related

Reporting Services :: How To Create Multi-valued Parameter For Only One Specific Condition

Jul 21, 2015

I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name  in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .

View 5 Replies View Related

Keep A Multi-valued Parameter Cleared From Selections Until The User Select His Choice

Apr 28, 2008

I have a report that includes two multi-valued parameters.
In the Default Values section, I choose 'from query' and select dataset and value field.
In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field.
When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected).
How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.

View 5 Replies View Related

Reporting Services :: Multiple Valued Parameter Passing In SSRS For Getting Performance

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

Passing Parameter To Subreport Based On User Action.

Sep 12, 2007

I have a main report and a subreport. Subreport is not at row level but is at report level. The sub report has a parameter that needs to be passed from mainreport. However, the subreport's parameter should be set and the subreport should be refreshed when the user clicks on a row in mainreport. Can I use drill-through / navigation feature in any way to build this functionality?

Any help will be most appreciated.

View 3 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

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

Reporting Services :: Field Value Not Passing To A Parameter In Drill Through Subreport

Sep 6, 2015

I have two reports 'Product Details' and 'Sales by Product'

When i click on 'Product ID' field in 'Product Details' report, It has to drill through to 'Sales by Product' which has a parameter 'Product ID' in it. 

I have written the following javascript

="javascript:void(window.open('http://servername/Rportserver/Pages/Report.aspx?ItemPath=%2fTestDrillThru%2fSales+By+Product&rs:Command=Render&ChildProductID="& Fields!ProductID.Value &"','_blank',',resizable=1,width=1600,height=800,left=50'))"

When i drill through, field value is not passing to 'child product id' in sales by product. I have to give the value again in child report.

View 5 Replies View Related

How To Passing Multi Value Parameter In SQL Query

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

Passing Multi Value Parameter Between Reports

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

Passing Multi Value Parameter To Stored Procedure

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

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

Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.

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

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

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

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

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

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

Displaying Multi-valued Parameters

Jul 3, 2007

Hi There.



I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.

This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!



Thanks, Mike

View 4 Replies View Related







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