Share via

SSRS report - using Lookup with condition

mark goldin 691 Reputation points
Jan 12, 2022, 10:19 AM

I am trying to use a Lookup function to check some condition in a dataset. Something like this:

=Lookup(Fields!ID.Value, 26, Fields!Score.Value = -1, "dataset")

It's not working because as soon as Lookup finds ID = 26 it stops. I need to go through the entire dataset and check if there is score = -1 for ID = 26. Any idea?

Thanks

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,967 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Joyzhao-MSFT 15,601 Reputation points
    Jan 13, 2022, 3:16 AM

    Hi @mark goldin
    The Lookup function is used to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. Mostly used to refer to field values in the second dataset. A Tablix corresponds to a dataset, and the Lookup Function is used when you need to refer to other datasets.

    If you need to filter in the same dataset, you can do it in the following way. Set the fill property of the cell.
    As shown below:
    164611-05.jpg
    Use the IIF expression to set the fill background color. The following expression is set as: when the parameter value is "A" and the Type value is "Y", the cell background color is Red, otherwise it is white.
    164592-04.jpg
    Preview:
    164584-06.jpg

    Similarly, you can use the following expressions to filter the dataset:
    IIF(Fields!ID.Value="26" AND Fields!Score.Value = -1,"Red","White")

    If I misunderstand what you mean, please feel free to let me know.
    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.