Combine two datasets in SSRS Reporting Services and count

Question

I have two datasets - In dataset 1 MedAdministration I have Medical_Condition. In dataset 2 ProblemList I have Medical_Condition. I want to combine both datasets and count.

For instance Heart attack may be in dataset 1 and in dataset 2. I just want the report to show Heart Attack 20 which would be a combination of both datasets and the total from both into one total on the report.

I have tried the lookup function and the join, but I keep getting errors.

=Lookup(Fields!Medical_Condition.Value,Fields!Medical_Condition.Value, "ProblemList")

"System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Medical_Condition.Paragraphs[0].TextRuns[0]’ has an incorrect number of parameters for the function ‘Lookup’. at Microsoft.ReportingServices.Library.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Guid batchId, Warning[]& Warnings) at Microsoft.ReportingServices.Library.ReportingService2005Impl.SetReportDefinition(String Report, Byte[] Definition, Warning[]& Warnings) at Microsoft.ReportingServices.Library.ReportingService2010Impl.SetItemDefinition(String ItemPath, Byte[] Definition, String expectedItemTypeName, Property[] Properties, Warning[]& Warnings) at Microsoft.ReportingServices.WebServer.ReportingService2010.SetItemDefinition(String ItemPath, Byte[] Definition, Property[] Properties, Warning[]& Warnings)

I can't even get it to combine the data, let alone count it. What am I doing wrong?

Thanks in advance! Tara

Answer 1

Lookup function takes 4 parameters: Lookup(source_expression, destination_expression, result_expression, dataset)

Answer 2

To expand on SuperSimmer's answer, you'll need to use the correct lookup parameters within your expression to add the two. I don't know for sure how the report is laid out, but for example, if you need values summed for Heart Attack, you'll need a count of that field from each dataset and add those together.

 =Count(IIF(Lookup(Fields!Medical_Condition.Value,Fields!Medical_Condition.Value, Fields!Medical_Condition.Value, "ProblemList") = "Heart Attack", 1, Nothing)) 
+ Count(IIF(Lookup(Fields!Medical_Condition.Value,Fields!Medical_Condition.Value, Fields!Medical_Condition.Value, "MedAdministration") = "Heart Attack", 1, Nothing))

If you'd prefer a dynamic result or if the 'Condition' is provided from a parameter, you'd replace the "Heart Attack" with Parameters!Condition.Value to check each dataset for the value provided.