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: 1132 Visits: 1931
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,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: 1132 Visits: 1931
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 by Doctor Who, on Flickr

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
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.
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
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")


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: 1132 Visits: 1931
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,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: 1132 Visits: 1931
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:

Error 1 The 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,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
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
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: 1132 Visits: 1931
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,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: 1132 Visits: 1931
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,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
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.
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