Let me lay out what I'm looking at here Multivalue parameter collects the externalId. The parameter is set up to be multivalue and collect from a separate dataset. We're good there. WHERE (cust.externalId IN (@customerExternalId)) <--- the where statement Another parameter not dependent on the multivalue parameter, and is a normal, dropdown box parameter collecting data from a third dataset. (TransactionType) Alright, in the actual report, we have a row of customer data, grouped by the customer name (which is really the cust.externalId), and the subreport contained within the group, so that depending on the customer, and the TransactionType, we may see some Line data for that customer. The problem then comes in sending the data to the subreport. customerExternalId = =split(join(Parameters!customerExternalId.Value, ", "), ", ") StartDateRange = @StartDateRange EndDateRange = @EndDateRange TransactionType = @TransactionType. The WHERE statement in the subreport is NOT set up to accept multiple externalIds, due to it being only one line per externalid. In my testing, I can only get Line data (subreport data) if I select one externalId and a transaction type; not multiples. Which leads me to believe that it is not passing the externalId's correctly. Subreport's where statement is as follows bc.Customer = @customerExternalId and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (Customer.externalId = @customerExternalId)

TheTurdFerguson, You can pass the multivalue parameter to any subreport using just the name, Parameters!customerExternalId.Value Now, you want all the values to be used in the query or just one id? If multiple Ids, then first of all set the sub-report parameter that will store the externalid values to allow multiple values. In the query, you have to use IN bc.Customer IN (@customerExternalId ) and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (Customer.externalId IN (@customerExternalId) ) Or incase of single value to be passed, Pass the parameter as: =split(join(Parameters!customerExternalId.Value, ", "), ", ")(0) Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

helpful, but not quite enough. The subreport displays!... Right now i am incredibly close and it still is only passing or getting one line worth of data, as now ALL the customer's externalids are returning the same Line data (subreport). subreport has changed bc.Customer = @customerExternalId and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) AND (Customer.externalId IN (@customerExternalId))

TurdFerguson, Reviewing your sub-report query: bc.Customer = @customerExternalId and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) AND (Customer.externalId IN (@customerExternalId)) I have one question (refer the bold text above), why = is used for bc.Customer and IN used for Customer.externalId ? Can you please help me understand it! Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

sorry, there is no = sign, it is an IN, so bc.Customer IN (@customerExternalId ) and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) AND (Customer.externalId IN (@customerExternalId))

Turd, I will need your help in understanding the issue you are getting, So, now from the Main report you are passing customerExternalId (single value) to subreport. Even passing a single value, you are getting multiple records in the sub-report. Is it the case? Please, can you give some more detail? Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

I'm just following your suggestions. I'm passing a multivalue parameter to the subreport. the main report is grouped by externalid, so we have alot of customers (externalid's) displayed. Now, each customer can have a subreport displayed obviously based on that above WHERE statement which is from the subreport. We only want one subreport per customer though (So I dont know if IN is the correct keyword for the subreports). Make sense?

Ok, let us go step by step: Main report, you have a parameter Externalid (multi-valued). So, all the customers (with a unique external id) are showing in the main-report When, user click on any customer you want to pass the externalid of that customer to a sub-report in order to show details for the same customer. Now, I assume the problem you are getting is: Whatever customer you are clicking, the external id passed is the same and resulting in similar data shown for all the customers. Previously, what I have posted =split(join(Parameters!customerExternalId.Value, ", "), ", ")(0) won't work, as it will always pass the first customer id. So, if you have a table in your main report and you are showing customers data in that, you must be having an Id field just like are showing in the parameter ExternalId. If yes, then you have to pass that field to the sub-report. Right-click the cell having customer name. And pass the customer id to sub-report. Hope I made my point clear. Do, let me know if you have any questions. Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Ok, let us go step by step: Main report, you have a parameter Externalid (multi-valued). So, all the customers (with a unique external id) are showing in the main-report When, user click on any customer you want to pass the externalid of that customer to a sub-report in order to show details for the same customer. We're not clicking on a customer, this is all displayed at runtime when the parameters are chosen, but otherwise yes. We are also passing the StartDate, EndDate, and TransactionType. Now, I assume the problem you are getting is: Whatever customer you are clicking, the external id passed is the same and resulting in similar data shown for all the customers. yes, at one point i was able to get unique line data for each customer, but i forget how i did it Previously, what I have posted =split(join(Parameters!customerExternalId.Value, ", "), ", ")(0) won't work, as it will always pass the first customer id. correct. So, if you have a table in your main report and you are showing customers data in that, you must be having an Id field just like are showing in the parameter ExternalId. If yes, then you have to pass that field to the sub-report. Right-click the cell having customer name. And pass the customer id to sub-report. I'm not sure why that would work. The externalId, from the same table, is also a unique identifer. So how will a second unique identifier help? But I tried it anyways like so bc.Customer IN (@customerExternalId ) and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) AND (Customer.externalId IN (@customerExternalId)) and AND (Customer.Id = @customerExternalId) bc.Customer IN (@customerExternalId ) and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) AND (Customer.externalId IN (@customerExternalId)) and (Customer.Id IN (@customerExternalId)) bc.Customer IN (@customerExternalId ) and (@startDateRange is null or bc.effectiveDate >= @startDateRange) and (@endDateRange is null or bc.effectiveDate <= @endDateRange) and (@transactionType is null or bc.transactionTypeId = @transactionType) or (bc.transactionTypeId = @transactionType) (Customer.Id IN (@customerExternalId)) none of which work for me. I think it has something to do with the ANDs and ORs relating to externalid and transactiontype, but I dont know how.

You can troubleshoot this, First of all test, if the correct External Id is passing to the sub-report. Create a dummy report with just the parameter and make it visible. Pass the value to this parameter from your main report. In the Action tab, select go to report and the dummy report. Run, the main-report click on the customer and check the value parameter is showing. Do it for multiple customers and check the parameter value. Likewise, test your sub-report query. Hard-code the externalid in the query and watch out for the result. Compare the results for different IDs. If its fine then the query is OK Otherwise you needs some query tweaking. Edit Part Can you please also state the requirement of using OR in you sub-query. Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Dummy Report query details : WHERE (Customer.externalId IN (@customerExternalId)) -- stripping it of all other stipulations parameters: @customerExternalId (multivalue, all other settings untouched), @transactionType passing: @customerExternalId and @transactionType. (doing a split+join doesnt help) Alright now I get an #error when i display the value of customerExternalId on the dummy report, as i did before in testing. what am i missing? The error i get with the #error is the following Warning 1 [rsWarningExecutingSubreport] Warnings occurred while executing the subreport ‘Subreport2’. [rsInvalidExpressionDataType] The Value expression used in textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid. (note, everything is set to text and externalId returns values like 'Test', 'Customer 1', 'ABA', etc)

Hi TheTurdFerguson, Thanks for your feedback. Let’s back the start of your post.According to your descritpion, your main report contains a tablix showing data group by customer name(which is really the cust.externalId), and the subreport was just nested within the customer group, so each subreport’s data was depending on the customer group value, as a result, what you need to do is passing the relevant customer value to the subreport rather than all the custmerExternalId. So please do the follow setting to pass the relevant value to subreport: customerExternalId=fields!customer.value StartDateRange = @StartDateRange EndDateRange = @EndDateRange TransactionType = @TransactionType. The WHERE statement in the subreport is NOT set up to accept multiple externalIds, due to it being only one line per externalid. That’s right, you subreport only contain one externalId, you don’t nedd to specify it accept multiple exteralIds. Please have a try and verify it works as you expected. Thanks, Bill Lu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

hi, Correct me if am wrong. Your requirement : Having main report with multiselect parameter customerExternalId. tell me what all parameters are passing from main report to your subreport . and also let me know that customerExternalId is used as single select parameter or multiselect parameter in sub report? Regards, Gayathri devi P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

I've already posted what each is getting, to an extent customerExternalId=@customerExternalId (Tried joins and splits here to no avail) StartDateRange = @StartDateRange EndDateRange = @EndDateRange TransactionType = @TransactionType. But thank you Zilong for pointing out that. I knew it was quite off when Manoj posted it, but I gave it a try.

Bump, I've still not gotten a concise answer nor have I figured it out myself. Can anyone be of assistance? I think I've mentioned all the properties to my issue that are relevant.