suppressing visibility of a tablix row when lookup returns nothing

db042190 1,516 Reputation points
2023-02-24T17:18:48.8+00:00

hi we run 2014 enterprise. I could probably post some redacted images but this question may be simple enough to forego doing that.

a friend has a report with two datasets a and b. a lookup is done on dataset b for the last column in the tablix. everything else in the tablix row comes from dataset a.

my friend want to enter an expression for row visibility that hides the row if nothing was returned from that lookup.

how would such an expression look?

the lookup itself looks like this...

Lookup(Fields!GidfromdsA.Value, Fields!GIDfromdsB.Value, Fields!valuefromdsB.Value, "ds_datasetb")

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,813 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. db042190 1,516 Reputation points
    2023-02-24T19:12:44.45+00:00

    we've evolved to this so far...

    =IIF(IsNothing(Lookup(Fields!GidfromdsA.Value, Fields!GIDfromdsB.Value, Fields!valuefromdsB.Value, "ds_datasetb")),false,true)

    ...in the row visibility expression.

    while the results of that lookup are actually going in to one tablix column successfully, adding it as a filter on the row is causing vs 2017 to throw an exception in preview mode. i just found out from my friend that there are over 1 million rows in the lookup. which she may or may not be able to whittle down.

    can a row visibility expression be based on what ends up in a tablix column cell or must it be based on incoming column values?

    what i'm thinking about right now is perhaps a hidden param with a list of the gidfromdsa values passed to ds b as a filter. but something tells me that wont work. in general, i think she believes there are about 4000 rows in ds a.

    0 comments
  2. AniyaTang-MSFT 12,321 Reputation points Microsoft Vendor
    2023-02-27T03:08:51.4833333+00:00

    Hi @db042190

    I kind of don't understand what you're doing. Are you using this expression as a filter or as a row visibility expression?

    I have a report that tests a Lookup Function to test row visibility expressions. This report has two datasets, "ID" and "Operation". I will use an expression to replace the id in the "Person_in_Charge" field with the name in the dataset "ID".1

    It can be seen that one of "Person_in_Charge" is empty, which means that the corresponding data has not been retrieved.2

    Then we use the expression in row visibility, notice that False is to show the report item and True is to hide the report item.

    3

    The row with an empty result is hidden.4

    Hope this can help you.

    Best regards,

    Aniya

    0 comments
  3. db042190 1,516 Reputation points
    2023-02-27T13:02:20.89+00:00

    thx aniya. its a row visibility expression. we were already doing what you are doing but unfortunately my friend informed me that the lookup ds has over 1 million rows in it. we suspect that volume is bringing the report down when its ALSO used in the row visibility expression. i've asked her pass some additional info to the lookup ds so it wouldn't return so much data. in the meantime, we are asking you if there is a way to build the row visibility expression based on what goes into that tablix cell where the lookup data goes INSTEAD of again invoking the lookup ds.

    0 comments
  4. db042190 1,516 Reputation points
    2023-02-27T20:21:12.8066667+00:00

    i asked my friend to compare her approach to aniya's. i also asked her to limit the lookup dataset count to about 5. it simply doesnt work even if she does it exactly as aniya does. we are going to try another approach where perhaps the lookups have to take place on the same db so she has only one dataset.