2

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.

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
Fame th
  • 1,018
  • 3
  • 17
  • 37
  • Are you still looking for a solution for this question? – Anup Agrawal Sep 03 '15 at 08:44
  • Yes, I attempt to use Lookup function. becuase I don't need edit a dataTable. I know it can resolve by joining tables by sql but It's not support my team. So I want to know "Can Lookup function use for multiple key?" or "What function can support this problem?" – Fame th Sep 03 '15 at 10:10
  • Always add a reporting-services tag for questions regarding SSRS to get broad exposure. – Anup Agrawal Sep 03 '15 at 10:31
  • I am sorry for my english. You tell me that I Should or not for tagging by "reporting-services" tag? – Fame th Sep 03 '15 at 10:40
  • You should add the reporting-services tag for wider exposure to your questions.. For this question, I added it for you. – Anup Agrawal Sep 03 '15 at 10:43

1 Answers1

2

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.

Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32
  • Added the comma after the first lookup. – Anup Agrawal Sep 03 '15 at 11:06
  • Thank you very much. It's work. I have one more question? How to use lookup function in textbox on page header. Becasue It can't set Datasetname like a Tablix. – Fame th Sep 03 '15 at 11:31
  • 1
    One of the tricks for using other functions in header and footer is: CReate a hidden textbox in body and then put the desired expression in that textbox. After that access the textbox in header and footer using ReportItems collection. `=ReportItems!TextBoxHidden.Value` – Anup Agrawal Sep 04 '15 at 03:31