CODEWITHSUNDEEP
reporting-serviceswhere-clausereportbuilderssrs-expression
Seb
Seb

Reputation: 899

LAST WHERE clause in SSRS expression

For the visibility of some fields, i want to check if a field has a specific value.

Right now i'm using this visibility expression:

=IIF (Sum(IIF(Fields!TagName.Value = "Option1" , Fields!Val.Value, 0), "ParamDataset") > 0, true, false)

If option1 is 1, the field is shown, if its 0 then its hidden.

Its working, but the solution is bad. It checks all the entrys of Option1, and if it were at one time 1, the field is shown, even if the last entry is 0. Also it differentiates only between 0 and 1.

I'm searching for a expression which checks only the last entry of "Option1", and if possible also if the value is a specific value, not only 0 or 1. For example if its 23 or whatever.

PS: i cannot use tablix and the corresponding filters, because the process data is in a different dataset than the configuration data.

Upvotes: 1

Views: 162

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

I don't think this is possible in an SSRS expression. I thought of using LAST but you want the LAST where OPTION = 1.

You might be able to make it work if you could sort the data so that Option1 is the last sorted followed by your current sorting:

ORDER BY CASE WHEN TagName = 'Option1' THEN 1 ELSE 2 END, <CURRENT SORT CRITERIA>

Now since your Option 1 TagName is last and you want the last of the Option1, you could use LAST:

=IIF(LAST(Fields!Val.Value, "ParamDataset") > 0, true, false)

It would work better if you could add a ROW_NUMBER to the ParamDataset:

,ROW_NUMBER()OVER(PARTITION BY TagName ORDER BY SOME_FIELD DESC) ROW_NUM

Then you could check for Tag Name = Option1 and ROW_NUM = 1:

=IIF(SUM(IIF(Fields!TagName.Value = "Option1" AND ROW_NUM = 1, Fields!Val.Value, 0), "ParamDataset") > 0, true, false)

Upvotes: 1

Related Questions