Parameter passing from master to subreport

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

Thanks for any help.

Wayne E. Pfeffer

Yes you can do this. I suggest the following: In your subreport make the report parameter a multi-value parameter. Test the subreport stand alone and make sure it works. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters.

Scenario 1 (multi value parameter pass through):
The Main report has report parameter A which is a multi value parameter. You want to pass it to SubReport1 which has a report parameter B which is also defined as multi value parameter. In this case the mapping is just a simple parameter expression: =Parameters!A.Value
The subreport RDL element would look like this:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 2:
The Main report has no report parameters. You have a SubReport1 with a report parameter B defined as multi value parameter. You want to pass e.g. three selected values "A", "B", "C" as parameter values to the subreport. You need to create a multidimensional object array on-the-fly, e.g. with the Split function, e.g.: =Split("A,B,C", ",")
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Split("A,B,C", ",")</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 3:
The Main report has a multi-value parameter A. You want to pass only the first selected value from the main report's parameter to the subreport and the subreport's report parameter B is a single-value parameter. You can do this by using e.g. =Parameters!A.Value(0)
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value(0)</Value>
</Parameter>
</Parameters>
</Subreport>

-- Robert

|||Thank you for the help, it worked out.

Wayne|||Is there a limit on how many parameters you can pass if you are using Scenario1. I have 5 parameters and 1 subreport. I can pass in the first 4 (A,B, C,D) and the report works fine but when I try to pass in the last one (E), which is no different than the previous 4 parameters other than it might have a few more values than the others, I get an error saying that one or more parameters need has not been specified.|||

There is no limit on the number of parameters.

Instead, it seems like the parameter values passed in violate constraints defined for the subreport parameters. For example, the subreport parameter's data type is integer and you pass string values. Or you pass a NULL value ('Nothing' in VB) as one of the parameter values - which is not allowed for multi value parameters, etc.

-- Robert

|||

Okay, I am sort of new to reporting services...where would I go to check the data type?..I don't believe anything is a null but where would I go to check that? The subreport works fine on its own...The master report will be/is populated by stored procedures so at first I thought something may be off with the stored procedures but all the other parameters work just fine when hooked up to the subreport's parameters. Currently, the subreport is running off of data from the cube however, it has been suggested that I change the subreport to pull from the same stored procedures as the master report and it will work fine...which I don't understand because again the first four parameters work fine with out using the stored procedures. Any further assistance would be greatly appreciated.

Thanks.

Parameter passing from master to subreport

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

Thanks for any help.

Wayne E. Pfeffer

Yes you can do this. I suggest the following: In your subreport make the report parameter a multi-value parameter. Test the subreport stand alone and make sure it works. Then drop the subreport into your main report, and map the subreport parameters to the main report parameters.

Scenario 1 (multi value parameter pass through):
The Main report has report parameter A which is a multi value parameter. You want to pass it to SubReport1 which has a report parameter B which is also defined as multi value parameter. In this case the mapping is just a simple parameter expression: =Parameters!A.Value
The subreport RDL element would look like this:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 2:
The Main report has no report parameters. You have a SubReport1 with a report parameter B defined as multi value parameter. You want to pass e.g. three selected values "A", "B", "C" as parameter values to the subreport. You need to create a multidimensional object array on-the-fly, e.g. with the Split function, e.g.: =Split("A,B,C", ",")
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Split("A,B,C", ",")</Value>
</Parameter>
</Parameters>
</Subreport>

Scenario 3:
The Main report has a multi-value parameter A. You want to pass only the first selected value from the main report's parameter to the subreport and the subreport's report parameter B is a single-value parameter. You can do this by using e.g. =Parameters!A.Value(0)
RDL example:
<Subreport Name="SubReport1">
...
<Parameters>
<Parameter Name="B">
<Value>=Parameters!A.Value(0)</Value>
</Parameter>
</Parameters>
</Subreport>

-- Robert

|||Thank you for the help, it worked out.

Wayne|||Is there a limit on how many parameters you can pass if you are using Scenario1. I have 5 parameters and 1 subreport. I can pass in the first 4 (A,B, C,D) and the report works fine but when I try to pass in the last one (E), which is no different than the previous 4 parameters other than it might have a few more values than the others, I get an error saying that one or more parameters need has not been specified.|||

There is no limit on the number of parameters.

Instead, it seems like the parameter values passed in violate constraints defined for the subreport parameters. For example, the subreport parameter's data type is integer and you pass string values. Or you pass a NULL value ('Nothing' in VB) as one of the parameter values - which is not allowed for multi value parameters, etc.

-- Robert

|||

Okay, I am sort of new to reporting services...where would I go to check the data type?..I don't believe anything is a null but where would I go to check that? The subreport works fine on its own...The master report will be/is populated by stored procedures so at first I thought something may be off with the stored procedures but all the other parameters work just fine when hooked up to the subreport's parameters. Currently, the subreport is running off of data from the cube however, it has been suggested that I change the subreport to pull from the same stored procedures as the master report and it will work fine...which I don't understand because again the first four parameters work fine with out using the stored procedures. Any further assistance would be greatly appreciated.

Thanks.

Parameter not refreshing default data selection

Here is the senerio:

Parameter 1: Company - multi-value

Parameter 2: Employee - multi-value

Available values: Query - Dataset: EmpList

Default values: Query - Dataset: EmpList

Parameter 2 is dependant on Parameter 1.

When a Company is selected, it does update the list of employees in parameter 2. But what it isn't doing is updating my default values.

I select Company A. It populates the Employee list with all of Company A's employees and marks them all as selected (check in the checkbox) and the "Select All" checkbox is also checked.

I then decide I want to include Company B. It populates the Employee list with all of Company B's employees but it does NOT select the employee's from Company B. It is like it ignored the default values query.

I would also think that if "Select All" is checked all new values coming in would be checked.

Is there a way around this problem?

I need to know if this is a bug or if there is something I can do to fix this.

Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter