Using a lookup table in a report

Question

Using a lookup table in a report

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Rod at work SSC-Dedicated Points: 33900 More actions · Answer 1

I'm beginning to think that my problem is that the backend database is still at SQL 2005, regardless of the report writer (SSRS). Initially I wrote in the expression what you suggested, but it still complained about the "Gender" and "GenderDesc" as not being in context. So then I changed the expression to the following:

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

That eliminated the whole context issue, but now I'm left with the following error message:

Error1The Value expression for the textrun ‘Gender.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] 'Lookup' is not declared. It may be inaccessible due to its protection level.

So, "Lookup" is not declared. My guess is that's due to the backend database still being SQL 2005. Do you concur?

Kindest Regards, Rod Connect with me on LinkedIn.

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

It is not the version of Visual Studio that determines if certain functions are supported, but only the version of SQL Server and Reporting Services that is rendering the RDL file. If it is anything less than 2008 R2, Lookup() is not supported. It seems that such is the situation with your report. The key word "Lookup" would not have a wavy line under it if Reporting Services plug-in for Visual Studio was 2008 R2 or greater.

I guess you'll have to go back to pulling it in the query, which should work fine. Whenever you upgrade the SQL Server version, though, you'll have another option in your SSRS toolbelt.

Rod at work SSC-Dedicated Points: 33900 More actions · Answer 3

Yes, geoff5, I agree with you. Since we're currently at SQL 2005, I'm going to have to change the stored procedure.

Well, I've done that, but for some reason the RDLC report doesn't pick up on the new changes. I've even selected the dataset within the Data Sources panel, and then clicked on the Refresh button. Nothing. Why is that?

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 33900 More actions · Answer 4

I've performed the left join to the Gender table in the stored procedure. It works fine. But for some reason the SSRS report refuses to see the change in the stored procedure. How do I make it aware of the change?

Kindest Regards, Rod Connect with me on LinkedIn.

geoff5 Hall of Fame Points: 3560 More actions · Answer 5

I'm not sure what to suggest, but perhaps the RDL has some sort of glitch in it that is preventing the refresh of the included columns. I suggest deleting and re-creating the dataset based on the stored proc. As long as you recreate it with exactly the same name and all the same properties, everything else should work without altering anything else.

Also, previewing the report will force a refresh on the cache as it executes the stored proc to populate the report, so that might help too. I'd try that first before blowing away and recreating the dataset.

Rod at work SSC-Dedicated Points: 33900 More actions · Answer 6

I thought that performing a refresh would cause the dataset to update, so that's what I tried. It seemed to include the GenderDesc column, but it left the old Gender column there, and that's been removed from the stored procedure. Honestly, I don't know what it's doing or why.

Kindest Regards, Rod Connect with me on LinkedIn.