Search code examples
reporting-servicesssrs-2008ssrs-2012

Report item expressions can only refer to fields within the current dataset - SSRS 2016

I'm creating an SSRS report on Visual Studio 2015 but using 2 Data Sources, the reason is because 1 DB has the information that will be displayed on the table and the second DB has the metadata that needs to be displayed on the column header when an user hover the mouse.

I've created 2 Datasets, on 1 I'm using tool tip expression on the column headers to refer to a second Dataset that only has the metadata information but I'm getting an error "Report item expressions can only refer to fields within the current dataset".

I've followed a lot of guides and videos where it works for somebody else but it does not work for me . I was told to use lookup but it keeps failing when I try to preview the report.

Below is what I'm currently trying

Using Lookup to show data from another Dataset

Solution

  • Josh, Each item in a SSRS report item has a DataSet attached to it. Every function is assumed to be directed towards this DataSet.

    You can, however, "break out" and access other DataSets. Aggregate functions such as SUM, FIRST, and LAST take an optional second parameter which is the name of the DataSet. So, for example, your table can be attached to DataSet1 but you want the sum of all data from DataSet2:

    SUM(Fields!SomeData.Value, "DataSet2")

    You can also embed functions inside of your aggregates. For example, if you wanted a count of all males in DataSet2:

    SUM(IIF(Fields!Gender.Value = "male", 1, 0), "DataSet2")

    This should be enough for most cases but in these aggregate functions you can't reference data from any other DataSet than the one you are calling in the function. Sometimes you want to match data to another DataSet column and pull data from a different column. This is where LOOKUP and LOOKUPSET come into play. The syntax is LOOKUP(Local Field Name, Remote Field Name, Remote Field to Get, DataSet Name). For example, if you wanted to look up gender by a client's ID:

    LOOKUP(Fields!Client_ID.Value, Fields!Person_ID.Value, Fields!Gender.Value, "DataSet2")

    In the above example, Client_ID is a column in DataSet1. The rest of the columns exist in DataSet2. You can get more fancy with this by using string functions to check multiple columns but I assume that is more advanced than what you're trying to accomplish right now.

    LOOKUPSET works the same as LOOKUP but returns an array of matches. You would use this when there is not a 1 to 1 match on the data.

    Hopefully this helps out. I know LOOKUP was a little confusing for me at first and the documentation wasn't very helpful.