Using a lookup table in a report
-
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.
-
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
-
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.
-
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.
-
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.
-
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") -
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:
Error1The 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.
Error2The 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.
Error3The 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.
-
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.
-
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.
-
Are all the text boxes inside a List? The tablix itself is not visible from the screenshot.
-
You can actually see the screen shot? I can't.
To answer your question, no the textboxes are not in a list. At least I'm pretty sure they're not in a list. I wrote this report 3 years ago, and haven't worked on it in 2 years, so I don't remember all of the details. In looking at the report in the designer I don't see a list encompassing the textboxes.
Kindest Regards, Rod Connect with me on LinkedIn.
-
OK, here's another image, with more details, including the menu items from Visual Studio, where I do the development. Hopefully this will show you the tablix you speak of.
WPF RFS Report Design 2[/url] by Doctor Who[/url], on Flickr
Kindest Regards, Rod Connect with me on LinkedIn.
-
A "tablix" is a data region that can be configured as a table, a list, or a matrix. I have been making the assumption that your data expressions were inside the context of some kind of tablix, since that has been a requirement since SSRS 2008 R2.
Is this a SSRS 2008 R2 report design? If this was created before SSRS started to use tablix regions, then it was also before the LookUp function was added, which was 2008 R2.
-
On second thought, I guess you could get away without a tablix if your dataset contains only one row of information, or is filtered to just one, as it seems might be the case for your report. However, that means that the text boxes have no "default" scope or dataset context.
In this context, you will need a hybrid of your first attempt at the Lookup and a my expression suggestion, something like this:
=Lookup(First(Fields!Gender.Value, "RfsDataSet"), Fields!Gender.Value, Fields!GenderDesc.Value, "GenderDataSet") -
geoff5,
This report started it's like as a SSRS 2005 report. I believe I started it with VS 2008 or earlier (can't remember now). I've migrated it to VS 2010, using .NET 4.0, so I'm thinking it might be a 2008 R2 report now. Also, although I didn't say it before, this report exists as a .RDLC report within the application. Our database is currently a SQL Server 2005 database (we're working on upgrading it, but that's going to take some time). My point is that this report runs within the context of a .NET application, not within SSRS.
However, your whole point of the Lookup() function not being introduced until SSRS 2008 R2 makes me wonder. Within the designer, when I look at the expression, there's a red wavy line under Lookup. Could that be because, even though the .RDLC is within a .NET 4.0 app, it is still hitting against a SQL 2005 database?
Kindest Regards, Rod Connect with me on LinkedIn.
[/url]Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply