pass field value as parameter ssrs

Question

I have a field customerid which is a field in the query but i am not showing this customerId in the report. The field customerid is in dataset1.

I have another dataset named dataset2 and i have query "select ordername.... where order.customerId = o_custId" (I need to pass customerId field in dataset1 to the parameter o_cust_id).

So i created a parameter c_customer_id and i set in the "default values" in the wizard/properties of the parameter, the value field as customerid and dataset as dataset1 after i selected "get values from query". Then i clicked on the properties of the dataset2 and in parameter I set :o_cust_id = [@c_customer_id] (which is the parameter).

So basically i set the value of o_cust_id to the parameter c_customer_id whose value is set to the customerid field in dataset1.

The problem is I am just getting the same values for all the customerid. The ids are not getting updated in dataset2 but are getting updated in dataset1. So the same parameter value is passed to dataset2. What is wrong here?

Thanks, raghul

Answer 1

It sounds as though dataset2 is being reported within its own report item, rather than within a report item (table, list etc) for dataset1.

The technique you describe is how subreports are created in SSRS - see here and here.

As shown in the supplied links, a subreport needs to be within the body of a report item for the main dataset - so the solution in this case could be to add the report item for dataset2 inside the report item for dataset1.

Alternatively, it could be simpler to combine the two datasets into a single dataset.