Passing multivalued parameter from parent report to child report in Reporting service

I have report which accepts multi Value paramter in the Parent Report, this report has a drill down feature. but when i try passing the multi value parameter to the child report only the 1st value in the list is passed.

Parent Report accepts multiple users and based on which it shows the total % of issues submitted by thoses selected users in the multi value list. on drill down i need to send the user list again from the parent so that the child report shows each issue in detail. but when i use Parameters!Users.value i get only the 1st selected value.

could any one pls help me achive this requirement of mine.

thanks

Chandresh Soni

Are the data coming from Analysis Services or are they based on a relational data source?

For the relational case I recommend reading the following forum posting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

-- Robert

|||

Yes the data in the first report was coming through relational database. and i had given a drill down feature in the first report which takes to the second report which shows the details information off the parameter(multivalued) selected from the first. it worked for me.

I appreciate yr help - Robert

passing multiple values from parent to child package

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so?

Your perception is right.

Kevin Rodgers wrote:

The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

I may not be understanding you correctly; but I think with either 'parent package variables' or 'XML configuration file' you still need to create an entry in package configuration organizer for every property you want to override; so no difference there. The only difference is that in a XML file, yes you're right, multiple object-properties values can be defined.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

you mean to make the parent package 'aware' of the variables available in every child package...not sure how good that would be; but you can submit a suggestion to Microsoft:

http://connect.microsoft.com/feedback/default.aspx?SiteID=68

|||

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

The XML config file allows you to specify lots of configurations. A configuration is for a single property only.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

That would mean the parent package needs to have some knowledge of what is in the pckage it is calling. That doesn't really fit with the concept of abstraction - which is what the ability to do parent-child packages is all about really (in my mind anyway). However that isn't a complete justification and I can see why this would be useful - perhaps you should submit the request at Connect?

Hope that helps.

-Jamie

Passing a variable between a parent and child package doesn't work

Has someone managed to pass successfully a variable from a parent package to a child package? I’ve tried a zillion permutations and I can’t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is:

1. The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table.
2. I created a child package variable with the same name.
3. In the child package, I’ve created a parent package configuration that points to the ExecutionID variable.
4. I am trying to read the variable in a Derived Column Task in which I have a column linked to @.ExecutionID.

This doesn’t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated.

Oh, I didn’t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.

Never mind. My configuration got messed up. I am still not sure what needs to be done to debug a child packge though. Ideas?

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!