SSRS Expression Doesn't Read Value from Lookup

Question

My problem: I have an SSRS report that groups by metric, facility, and month_end dt. The report displays each month and its corresponding calculated value using the grouping I have but the YTD does not work. I first had YTD outside of the group and I've tried it inside the group.

I can get a YTD value to appear but it repeats the first metric's YTD value over and over instead of differentiating between the different metric names although in the dataset the data is there. The SSRS takes a facility parameter to run so it only runs one facility at a time so that group may not be necessary.

YTD is a little complicated because the date range I use for YTD changes by the metric name so not all 12 months are included so I have it calculating the values I need for YTD in SQL before it comes to the SSRS dataset, but I think something to do with how I store it and how I am grouping is preventing me from picking it up in an expression.

SSRS DESIGN

Data Structure

As you can see in the data structure example I have the YTD value stored with a different Agg_Type.

I tried this in the YTD Test and YTD expression:

Lookup("Y",Fields!ROW_AGG_TYPE.Value, Fields!DASH_VALUE.Value, "FIT_Dash_Data")

but it just gives me the value for the first metric over and over instead of differentiating between the metrics although the row grouping should force that??

I've tried creating a Join column on the main dataset and then a second dataset that it just filtered to the Y values and then a lookup on that but I just get no data when I do that. I feel like I should be able to do this from one data set though since I have full control of my SQL design.

This one connects to a second dataset to lookup the Y value but returns nothing.

=Lookup(Fields!JoinColumn.Value,Fields!JoinColumn.Value, Fields!DASH_VALUE.Value, "YTD")

This is how the Join column was created and stored in both datasets:

=Fields!METRIC_NAME.Value & "||" & Fields!FAC_CD.Value & "||" & Fields!ROW_AGG_TYPE.Value

Does anything stand out to anyone that may be preventing me from picking up the value from my data? I am pretty new to SSRS as a report development tool so I am sure I am just missing something.

Hannover Fist Hannover Fist · Accepted Answer · 2021-03-19T20:59:02

The reason that your LOOKUP is always returning the first value is that the LOOKUP checks for any record on with a "Y" and always finds the first and stops. I think you could fix it by using the JOIN that you mentioned.

=Lookup(Fields!METRIC_NAME.Value & "||" & Fields!FAC_CD.Value & "||" & "Y", 
        Fields!METRIC_NAME.Value & "||" & Fields!FAC_CD.Value & "||" & Fields!ROW_AGG_TYPE.Value, 
        Fields!DASH_VALUE.Value, "FIT_Dash_Data")

This would check for the record with a matching metric and facility.

But I don't see why you can't just aggregate your current values from the one dataset with

=MAX(IIF(Fields!ROW_AGG_TYPE.Value = "Y", Fields!DASH_VALUE.Value, NOTHING)

This would check for the records in the group and give the MAX value for the ones that are Y while ones that are not Y are NOTHING (NULL for SSRS). From the data, there is only one Y record per FAC.

SSRS Expression Doesn't Read Value from Lookup

1 Answers