SSRS - combine datasets in a lookup not working

Epicor ERP 10
,
1

I have modified the standard time report RDD to add in the ProjPhase table. I then have it as a separate data source in my SSRS report. I am trying to pull the data from the ProjPhase table based on a conditional statement. I am attempting to use the ProjectID and PhaseID as keys together to get the right value out of the ProjPhase table. Here is what I created:

=iif(InStr(Fields!PhaseIDDescription.Value,"Root Phase")>0,"",
Lookup(First(Fields!PhaseID.Value, "ProjPhase") & First(Fields!ProjectID.Value, "ProjPhase"),
Fields!PhaseID.Value & Fields!ProjectID.Value, 
First(Fields!UserMapData.Value, "ProjPhase"),"test"))

However, this gives me the following SSRS error:

System.Web.Services.Protocols.SoapException: The Value expression for textrun ‘Textbox3.Paragraphs[0].TextRuns[0]’ contains an aggregate function in an argument to a Lookup or LookupSet function. Aggregate functions cannot be used for the destination or result Expression parameter of a lookup function.
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)

What does it think is an aggregate? I searched the Lookup function and the & is supposed to be the right way to use multiple source fields. Anyone know what I have wrong?

2

I found I had 2 things wrong with my report. First, I didn’t have the ProjectID in the select statement for the RDD generated dataset but I had the field declared, therefore no project ID was coming across. After I had the project ID, I was then able to get the lookup working like this:

=Lookup(Fields!PhaseID.Value & Fields!ProjectID.Value ,Fields!PhaseID.Value & Fields!ProjectID.Value ,Fields!UserMapData.Value,"ProjPhase")

I also added this same lookup into my IIF statement and now that works too. Hope this may help someone else in the future!

1 Like
3

I believe Lookup only works on 1 column. I would create a calculated field in both datasets that combines the columns you are using for keys and then use Lookup on the calculated field.

4

I discovered the bottom line issue for this not working. In my query for the RDD generated dataset I added the field ProjectID but didn’t have it in the query so it wasn’t getting any data. Once I fixed that the compound Lookup

=Lookup(Fields!PhaseID.Value & Fields!ProjectID.Value ,Fields!PhaseID.Value & Fields!ProjectID.Value ,Fields!UserMapData.Value,"ProjPhase")

worked! Hope this helps someone else in the future!