Search code examples
reporting-servicesreportrdlcrdl

"lookup" function of RDLC report isn't work in this case

I have three related three dataset like these enter image description here

I need to display "INVOICE_CODE" from DatasetA concatenate with "COUNTRY_NAME" from DatasetC Example :

  "INV123-Korea"

I tried to use "Lookup" function by this step

1.) First table is used for main table in a report. So I will assign DatasetA to my tablix1

enter image description here

2.) At Tablix1, rigth click on a cell and create expression via

enter image description here

3.) Put this concept code. (A--->B--->C)

      =Lookup(A.FK,B.PK,    Lookup(C.FK,B.PK,C.ANS,"Dataset C") , "Dataset C")

But It's not work.

Solution

  • In this case using Lookup function is not my first preference but if you want to accomplish using lookup you can do something like this.

    =Lookup(
        Lookup(Fields!Customer_Code.Value, 
               Fields!Customer_Code.Value,
               Fields!Country_Code.Value, 
              "Dataset B"),
      Fields!Country_Code.Value,
      Fields!Country_Name.Value,
      "Dataset C")

    Note: SSRS is case sensitive so make sure you are using correct casing for your fields and Dataset names.

    Lookup function returns only the matching value for the dataset you referenced. There is another SSRS function LookupSet which can be used to return the set of matching values based on name/value pair.

    First lookup you get Country_Code from DataSet B by supplying the customer_Code value from the Dataset A. Second Lookup function will use the result of first lookup function to get the Country_Name from the DataSet C.

    To show both Invoice from Dataset A and Country_Code from DataSet C. Create two placeholders. In the first placeholder directly put =Fields!Invoice.Value and in the second place holder ut the above lookup expression.