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 Tuesday, June 25, 2013 8:31 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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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
Post #1467195
Posted Tuesday, June 25, 2013 8:39 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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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,

Rod
Post #1467201
Posted Tuesday, June 25, 2013 9:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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.

Post #1467226
Posted Tuesday, June 25, 2013 9:22 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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")

Post #1467230
Posted Tuesday, June 25, 2013 10:32 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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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
Post #1467278
Posted Tuesday, June 25, 2013 10:41 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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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,

Rod
Post #1467285
Posted Tuesday, June 25, 2013 11:51 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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.
Post #1467308
Posted Tuesday, June 25, 2013 1: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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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
Post #1467362
Posted Tuesday, June 25, 2013 3:09 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: Thursday, April 10, 2014 1:08 PM
Points: 721, Visits: 1,264
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
Post #1467394
Posted Tuesday, June 25, 2013 7:12 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:02 PM
Points: 150, Visits: 445
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.
Post #1467416
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse