Report Manager upload error

Question

All,

I have a problem I hope some of you may have experienced before.

 

I have a Matrix report in Visual Studios 2008 version 3.5 SP1, that uses a secondary dataset and a Lookup() function in the background color properties.  Shown below:

=iif(Fields!DateValue.Value = "5000-JAN-31" or Fields!Market.Value = "-AA1", "MediumBlue",

 iif(Fields!DateValue.Value = "5000-JAN-26" and Fields!DropRate.Value > 

 Lookup(Fields!Market.Value, Fields!Market.Value, Fields!ID50000130.Value, "DataSet2"), "Firebrick",

 "Transparent"))

 

When I preview the report in Visual Studios, it works just fine.  But when I try to upload the report to the Report Manager, said to be version 2008, it fails to load and gives the following error message:

The BackgroundColor expression for the text box ‘DropRate’ refers to the field ‘ID50000130’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. (rsFieldReference)

 

Report Manager understands all of the rest of my reports I upload just fine but this is the only one that uses a lookup() function.

Have any of you seen this before?  Is there a service Pack we can load on the Report Manager that might help?  I have a feeling the report manager just needs to be brought further up-to-date.

Any of you have another way to have a field in a matrix table refer to it'self in an earlier outputted column location to conditionally format without using a secondary dataset and a lookup function?

 

Thanks for your help in advance

Answer 1

Hello,

I think 2008 version does not support LookUp function .

not sure this might help you Alternate color in matrix

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 2

Thanks, but I can alternate the row or column color just fine.

If it's true that Report Manager 2008 does not understand the Lookup() function, can anyone suggest an alternate way to do a conditional formatting based on looking back at a far previous cell in a matrix?

The design of the matrix is of course very simple as shown below;

	Date
Market	Sum(BlockRate)

Now the output is as shown further below and what I need to do;

			MTD Target	Median	Mon 10/08	Sun 10/07
Alabama			0.610	0.928	0.842	0.590
Albuquerque			0.650	1.252	0.714	0.701
Arkansas			0.480	0.567	0.400	0.500
Atlanta-Athens			0.520	0.739	0.459	0.459
Austin			0.510	0.652	0.894	0.446

I am conditionally formatting the one and only cell (Sum of BlockRate) in the Matrix to look back at a much previous cell (shown as MTD Target and underlined) and deciding if to bold (shown here in example as Bolded) based on if it is greater than the "MTD Target" Sum of BlockRate.

Can I do this without using a lookup() and a secondary dataset as I have presented in my first post?  Please give an example if you can.

Answer 3

Hi Pink Floyder,

The Lookup() function is a new function in SQL Server Reporting Services, so it is not supported in previous versions.
Based on your description, it seems that you create two dataset which retrieve data from two table in database. In order the return fields in a single dataset, please refer to the following query.

SELECT Columns_in_yourtable From Table1INNER　JOIN Table2ON Table1.Market=Table2.Market

The following screent shot shows the design sturcture and the perview of a similar report. Please take as reference.

Note: For the background color property of the text box which display the value of the “Rate” field, please refer to the following expression:
=IIF(Fields!Target.Value<sum(Fields!Rate.Value),"Red","No Color").

Regards,
Fanny Liu

---

Fanny Liu

TechNet Community Support

Answer 4

Fanny Liu,

Thanks for your input.  Your example and approach to resolving my issue worked very well for stepping down the intelligence of my reports in order to enable them to be posted to a lesser version of Report Manager.

Thanks again,

PinkFloyder