I want to share the SSRS Lookup function and how I used it to overcome my reporting issue. Using this function I was able to pseudo join two datasets together to get two disparate sets of data in a single row in the matrix.
I was asked to reproduce a report that one of our Deputy Chief's had seen at a major police department. In this report each row is a year and the columns are months with quarter, semi-annual and annual totals and an annual change calculated field.
I thought at first that I would be able to use the previous function but quickly realized that because of my groupings that wasn't going to work:
The previous function doesn't respect the groupings and reset at each new Incident Type as shown by the red -23 in the chart above.
So I added another dataset to my report. I used a recursive query to get the annual totals and then joined back to it based on the year being 1 more so I can compare current and prior year in the same record and perform the subtraction to obtain the yearly delta and concatenated together the three pieces of information into an ID field
concat( a.RMS_IncidentType, a.ReportedYear, a.sector) as Id,
t.YearlyTotal as PriorYearTotal,
a.YearlyTotal - t.YearlyTotal as ChangeFromPriorYear
from annTotal a
left outer join annTotal t on
a.sector = t.sector and
a.RMS_IncidentType = t.RMS_IncidentType and
a.ReportedYear = t.ReportedYear + 1
order by a.sector, a.RMS_IncidentType, a.ReportedYear desc
AnnualCounts query results:
Now I used the Lookup function in the Y/E Diff column. By adding the ID field to both datasets (remember that is the concatenation of the Sector, IncidentType and Year) I was able to easily "join" the two datasets together. The function assumes the first value Fields!Id.Value is associated with the dataset tied to the tablix. The next parameter is the name of the field in the dataset we want to look in and the name of the field whose value we want to return, the final parameter is the name of the dataset to look in for the values, in this case "AnnualCounts". Since our matrix is grouping by Sector, Incident Type and Year there will ever only be a single row in the matrix that will be found in the AnnualCounts dataset.
In the final report I get the year end difference by group.