This blog post covers using Report Builder 3 (with Microsoft SQL Server 2008R2) to lookup a value in another dataset.
Scroll straight to the bottom for the key function, the rest of the article is setup.
NOTE: The back end of this example is an Oracle database but the solution will work for any database, you just will need to modify the SQL.
Start Report Builder 3
Select "Blank Report" from the New Report or Dataset wizard.
Right click "Dataset" in the treeview on the left and select "Add Dataset ..." from the popup:
Change the radio group on the right from "Use a shared dataset" to "User a dataset embedded in my report":
Click the "New ..." button next to the Data source drop down:
Change the selected radio group item from "Use a shared connection or report model" to "User a connection embedded in my report":
Change the drop down to "Oracle", click "Build" to enter your server, username, and password details. Click "Test Connection" to make sure everything is ok and then click "OK". This returns you to the "Dataset Properties" dialog:
Change the name to "KEY" and enter the Query:
SELECT 1 AS KEY FROM DUAL UNION
SELECT 2 FROM DUAL UNION
SELECT 8 FROM DUAL UNION
SELECT 3 FROM DUAL UNION
SELECT 4 FROM DUAL UNION
SELECT 5 FROM DUAL UNION
SELECT 7 FROM DUAL
Click "OK".
Add a second Dataset following the same steps above (you can re-use the data source) except name this Dataset "VALUE" and enter the Query:
SELECT 1 AS KEY, '01 DESC' AS KEYLABEL FROM DUAL UNION
SELECT 2, '02 DESC' FROM DUAL UNION
SELECT 3, '03 DESC' FROM DUAL UNION
SELECT 4, '04 DESC' FROM DUAL UNION
SELECT 5, '05 DESC' FROM DUAL UNION
SELECT 6, '06 DESC' FROM DUAL UNION
SELECT 7, '07 DESC' FROM DUAL UNION
SELECT 8, '08 DESC' FROM DUAL UNION
SELECT 9, '09 DESC' FROM DUAL UNION
SELECT 10, '10 DESC' FROM DUAL
You will end up with something like this:
Create an empty table on the report and drag/drop the KEY field from the KEY dataset onto the table:
Click on the cell next to "[KEY]" (with "Data" showing in the image above) and enter an expression:
=Lookup(Fields!KEY.Value, Fields!KEY.Value, Fields!KEYLABEL.Value, "VALUE")
This will give you something like:
Click "OK", click "Run" (at the top left on the ribbon):
And the lookup is complete ...
Scroll straight to the bottom for the key function, the rest of the article is setup.
NOTE: The back end of this example is an Oracle database but the solution will work for any database, you just will need to modify the SQL.
Start Report Builder 3
Select "Blank Report" from the New Report or Dataset wizard.
Right click "Dataset" in the treeview on the left and select "Add Dataset ..." from the popup:
Change the radio group on the right from "Use a shared dataset" to "User a dataset embedded in my report":
Click the "New ..." button next to the Data source drop down:
Change the selected radio group item from "Use a shared connection or report model" to "User a connection embedded in my report":
Change the drop down to "Oracle", click "Build" to enter your server, username, and password details. Click "Test Connection" to make sure everything is ok and then click "OK". This returns you to the "Dataset Properties" dialog:
SELECT 1 AS KEY FROM DUAL UNION
SELECT 2 FROM DUAL UNION
SELECT 8 FROM DUAL UNION
SELECT 3 FROM DUAL UNION
SELECT 4 FROM DUAL UNION
SELECT 5 FROM DUAL UNION
SELECT 7 FROM DUAL
Click "OK".
Add a second Dataset following the same steps above (you can re-use the data source) except name this Dataset "VALUE" and enter the Query:
SELECT 1 AS KEY, '01 DESC' AS KEYLABEL FROM DUAL UNION
SELECT 2, '02 DESC' FROM DUAL UNION
SELECT 3, '03 DESC' FROM DUAL UNION
SELECT 4, '04 DESC' FROM DUAL UNION
SELECT 5, '05 DESC' FROM DUAL UNION
SELECT 6, '06 DESC' FROM DUAL UNION
SELECT 7, '07 DESC' FROM DUAL UNION
SELECT 8, '08 DESC' FROM DUAL UNION
SELECT 9, '09 DESC' FROM DUAL UNION
SELECT 10, '10 DESC' FROM DUAL
You will end up with something like this:
Create an empty table on the report and drag/drop the KEY field from the KEY dataset onto the table:
Click on the cell next to "[KEY]" (with "Data" showing in the image above) and enter an expression:
=Lookup(Fields!KEY.Value, Fields!KEY.Value, Fields!KEYLABEL.Value, "VALUE")
This will give you something like:
Click "OK", click "Run" (at the top left on the ribbon):
