Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Using a lookup table in a report Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 8:57 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
This is a very simple question, so I'm sorry to ask it, but here goes. I've written only a few SSRS reports, and as it so happens, the vast majority of them have the data resolved in the query. Well, there's one that doesn't, at least not for one of the fields in the returned query. So what I need to do is do a lookup against the lookup table. Only thing is I don't know how. So, how do you use a lookup in a SSRS report? I'm using VS 2010 for this development.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1466778
Posted Monday, June 24, 2013 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 6:16 AM
Points: 25, Visits: 180
Hi Rod

If the look up table is in the database can you include it in your data set from the query I tend to create all the data for the report with one query passed from SSRS as t sql?

Simon
Post #1466794
Posted Monday, June 24, 2013 9:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
Add a secondary dataset to the report design that pulls the values from the lookup table. In the tablix, use the Lookup() function to find the value corresponding to whatever lookup ID is in the primary dataset. Reply back if you need more specifics. I can give you sample code if you provide (a) the primary key name from the lookup table dataset, (b) the foreign key name from the primary dataset, and (c) the plain text field name from the lookup table dataset.
Post #1466796
Posted Monday, June 24, 2013 12:47 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
Although the lookup table is in the same database, so modifying the query would be easily done, I think I'll try using the Lookup() function. I could use the experience at doing that, which will probably be useful in the future.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1466868
Posted Monday, June 24, 2013 2:02 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
OK, I'm tryihng to use the Lookup() function. Here's what I've got in the expression:

=Lookup(First(Fields!Gender.Value, "RfsDataSet"), First(Fields!Gender.Value, "GenderDataSet"), First(Fields!GenderDesc.Value, "GenderDataSet"), "GenderDataSet")

When I compile the application I get the following error message:

The Value expression for the text box ‘Gender’ refers to the field ‘Gender’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.


RfsDataSet is the dataset associated with the query (a stored procedure in this case).
GenderDataSet is the dataset associated with the lookup table.

I'm not sure why I'm getting the error that I am. Where have I gone wrong?


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1466893
Posted Monday, June 24, 2013 2:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
The First() aggregate function should is not necessary; the scope should be such that there is only one value for the Gender field.

Also, the first parameter is always the foreign key field from the primary dataset. The second parameter is always the primary key field from the secondary dataset. the third parameter is always the "lookup value" from the secondary dataset. The fourth parameter is the secondary dataset name. There is no need to specify the dataset is the first three parameters.

Try this:

=Lookup(Fields!Gender.Value, Fields!Gender.Value, Fields!GenderDesc.Value, "GenderDataSet")

Post #1466909
Posted Monday, June 24, 2013 3:20 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
Hi geoff5,

Thank you very much for your help. Because of it, I'm making progress. I've done as your suggested, using the code that you specified. (Fields!Gender.Value happens to be the name of the foreign key in the primary table, as well as the name of the primary key in the lookup table, as you surmised.) However, when I used what you suggested I got the following 3 error messages, in this order:


Error 1 The Value expression for the text box ‘Gender’ refers to the field ‘Gender’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Error 2 The Value expression for the text box ‘Gender’ refers to the field ‘Gender’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Error 3 The Value expression for the text box ‘Gender’ refers to the field ‘GenderDesc’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.


I'm not sure why I'm getting the same error for error #1 and #2, but I am.


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1466926
Posted Tuesday, June 25, 2013 7:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
These are errors of "context," meaning that the formula may be correct, but is not working in the particular context where you have put it in the report design. Fixing these kinds of issues is difficult when the context is unseen. Can you post a screenshot of the report design, with some kind of comment as to where in the report you have put the Lookup expression?

What I can say is that the expected context is inside a text box within the "detail" section of a table that has been associated with the primary dataset. If this is in a grouped region or in a List or Matrix instead of a table, then the expression will need adjustment. But the method of adjusting will depend, again, on the context.
Post #1467154
Posted Tuesday, June 25, 2013 8:12 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, December 14, 2014 7:49 AM
Points: 738, Visits: 1,307
OK, geoff5, here's a screen capture of the report. I've circled in red, where I've currently got the lookup express. (I hope this works. When it comes to uploading photos to places like Flickr, I always seem to run into problems with it not working correctly.)



Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1467178
Posted Tuesday, June 25, 2013 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
Are all the text boxes inside a List? The tablix itself is not visible from the screenshot.
Post #1467191
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse