Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a lookup table in a report


Using a lookup table in a report

Author
Message
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 1930
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,RodConnect with me on LinkedIn.
Northern Monkey
Northern Monkey
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 315
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
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 1930
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,RodConnect with me on LinkedIn.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 1930
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,RodConnect with me on LinkedIn.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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")


Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 1930
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,RodConnect with me on LinkedIn.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1130 Visits: 1930
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,RodConnect with me on LinkedIn.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 543
Are all the text boxes inside a List? The tablix itself is not visible from the screenshot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search