Monday, October 08, 2012 3:41 PM
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",
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
- Edited by Pink Floyder Tuesday, October 09, 2012 9:13 PM
Monday, October 08, 2012 3:59 PM
I think 2008 version does not support LookUp function .
not sure this might help you Alternate color in matrix
Hope this will help you !!!
- Edited by Sanjeewan Kumar Monday, October 08, 2012 4:02 PM
Tuesday, October 09, 2012 9:40 PM
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.
- Edited by Pink Floyder Tuesday, October 09, 2012 9:40 PM
Wednesday, October 10, 2012 4:36 AMModerator
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:
TechNet Community Support
Thursday, October 11, 2012 1:42 PM
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.