Conditional formatting in SSRS with lookup

Question

• • • For DBS, Safeguarding training and first aid - The column should be highlighted red if no date appears (ie they have not done the training or no record of DBS) – orange where they expire in the next 3 months.
• I can get the cell to highlight red, but struggling to get it to highlight orange, as the 3 month date i created from expiry is in another dataset hence the reason i need to use the lookup function
• this is what i have tried with no luck
• =IIF(LOOKUP(Fields!PERSON_ID.Value,Fields!PERSON_ID.Value,Fields!ALLEGATIONS_AGAINST_STAFF.Value, "safe") > Today(),"WHITE","RED") =iif(LOOKUP(Fields!PERSON_ID.Value,Fields!PERSON_ID.Value,Fields!THREE_MONTH_DATE.Value, "safe") < dateadd(dateinterval.Day,-90,Today()),"WHITE","YELLOW")

Answer 1

Hi @Nelson, Michael

Do you have a lookup function not working or do you want to use the lookup function in multiple criteria?

If the lookup function doesn't work, have you used the lookup function correctly? For the usage of the lookup function in SSRS, you can check this link: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-ver16.

I did a simple test and created two datasets, "PERSON_ID" in "DataSet1", "PERSON_ID" and "Date" in "safe".

Then I created a table, imported the data from "DataSet1" into the table, and then used the expression in the background color. It worked.

If what you don't know is the logic of your conditional formatting. Then you need to sort out your thoughts according to the situation. You can check out this link to learn how to use the decision function: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/expression-examples-report-builder-and-ssrs?view=sql-server-ver16#DecisionFunctions.

For example, no date is returned in red, dates less than three months are white, and others are orange. Then we first use to determine whether the return is empty or not, and then judge the rest of the case.

=iif(isnothing(LOOKUP(Fields!PERSON_ID.Value,Fields!PERSON_ID.Value,Fields!Date.Value, "safe")),"red",iif(LOOKUP(Fields!PERSON_ID.Value,Fields!PERSON_ID.Value,Fields!Date.Value, "safe") < dateadd(dateinterval.Day,-90,Today()),"white","orange"))

Preview:

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya