Using column from main dataset as multi-value parameter in subreport

Question

Hi I am using SSRS 2008.

My set up is MainReport with dataset1 and 1 parameter - OrderID. One of the columns in dataset1 is ShippingID. My other report called - SubReport has 1 multivalue paramter - ShippingID.

I am trying to use ShippingID from Main report as multi-value parameter for Subreport. I tried different threads here but mostly they are diffrent than my scenario. How do I go about setting this up, is that even possible? I have been trying to set it up for whole day but unsuccessfully.

In my scenario one Order can be on multiple shipments.

One of the things I tried is creating new multi-value parameter in MainReport - call it shippingIDs_sub and mapping its default value to dataset1.ShippingID. But when I try to run report I get error "The 'shippingIDs_sub' parameter is missing value" - because my MainReport would sometimes return NULL in ShippingID column.

My scenario is very much similar to this SSRS passing parameter to subreport although I sometimes get NULLs in the column used as source for multi-value parameter. Is there any way to filter out NULLs and run subreport only for existing IDs?

Answer 1

In the Action where you call the sub-report, where you map to shippingIDs_sub, use an expression to pass some valid value whenever the value in your dataset is NULL.

=IIF(IsNothing(Fields!ShippingID.value),"Some Valid Value",Fields!ShippingID.value)

As to this:

Is there any way to filter out NULLs and run subreport only for existing IDs?

No, you can't turn off the ability to go to a subreport, but you can "hide" it by changing the appearance of whatever your user clicks on so that it doesn't look like it's clickable. For example, in our reports, when something is clickable, we make it underlined in blue. So if I don't want to user to click it, I just make it the same font color/style as all the other text. But it doesn't stop a determined user from clicking on it anyway if they want.