CODEWITHSUNDEEP
reporting-servicesexpressionssrs-2012
Chad Baldwin
Chad Baldwin

Reputation: 2602

SSRS Expression: First(Value) but only if Value2 = X

Sorry, not sure how much sense my title makes.

Basically...I have a report and the dataset (Named DataSet1) returns data something sort of like this.

Quarter   MonthID  Month        Value1   Value2   QtrAvgValue1  QtrAvgValue2
Q-1       1        Mar 2017     12       28%      12.3          24.0%
Q-1       2        Feb 2017     15       12%      12.3          24.0%
Q-1       3        Jan 2017     10       32%      12.3          24.0%

Q-2       4        Dec 2016     18       25%      17.3          24.3%
Q-2       5        Nov 2016     14       18%      17.3          24.3%
Q-2       6        Oct 2016     20       30%      17.3          24.3%

The last two columns are just the averages for that rows entire quarter. The [Quarter] column is read like -1 means last quarter, -2 means the quarter before. The MonthID column is just an auto increment that goes up for every month older going back. So the most recent month would be 1, the next month older would be 2.

I need an expression that will let me say something like this:

WHEN Quarter = Q-1 THEN return QtrAvgValue1; Which would return 12.3;

I would also need an expression that could say:

WHEN MonthID = 1 THEN return Value1; Which would return 12;

Is this possible? I would hate to have to pivot my dataset to be a crap ton of columns with ridiculous column names

NOTE: I have tried using IIF with SUM/AVG and it works with numerical values as a sort of hack...but I cannot think of a way to handle string values.

Upvotes: 0

Views: 2387

Answers (1)

Chad Baldwin
Chad Baldwin

Reputation: 2602

UPDATE: I figured it out, using the Lookup and LookupSet functions in SSRS, which I have never used before.

Example:

=LookupSet("Q-1", Fields!Quarter.Value, Fields!QtrAvgValue1.Value, "DataSet1")(0)

LookupSet is normally used for One to Many relationships, but it will still work in one to one relationships as well.

This expression basically says to look up all rows that have the Quarter of "Q-1" and return the column [QtrAvgValue1].

The Lookup set function puts data into an array, so the "(0)" at the end indicates to use the first value in the array...essentially the same thing as the "First()" function.

Upvotes: 2

Related Questions