We help IT Professionals succeed at work.

Matrix (Sum): Conditional format works in Preview, but not when published

3dNOVA
3dNOVA asked
on
809 Views
Last Modified: 2012-05-07
MS SQL Server 2005 Reporting Services
I have a Matrix setup that has the Sum column, which I changed to Avg. I want all to have the same conditional formatting for the background color. I have found that if I put the conditions on the data field, it automatically carries through to the Avg column...in the Preview. However, when I deploy it, and bring it up through a Web link, the final Avg column has no formatting, just a white background. I have also found that if I put the conditional formatting on the Avg column (using the green triangle in the corner), it doesn't work as expected, but it does carry over to both the preview and the deployed version.

My data field is: =Cint(Avg(Fields!Ping.Value))

The condition is: =iif(Avg(Fields!Ping.Value) > 150 and Avg(Fields!Ping.Value) <= 250,"Yellow", iif(Avg(Fields!Ping.Value) > 250,"Red","Green"))

This condition works perfectly on the data field, but when I put it on the final Avg column, it's always RED, even though the Average value is rarely over 150.  

I either need to figure out why the Preview looks OK, but not the deployed version. Or, figure out why the condition works in the data field, but not the Avg field.
Comment
Watch Question

Commented:
Try to delete the rdl file in report manager and redeploy your report. Some times it doesn't overwrite it even if you redeploy multiple times.

Author

Commented:
The deployment seems to be working just fine.  I've made a few changes, and each change shows up when I re-deploy.  Actually, this problem was there the very first time I deployed this report.  I thought I had it finished, because it looked perfect in Preview.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
What Service Pack is your deployment machine on?  (seems like a possible bug - not that I know of one that causes this but better to rule it out than be sorry....)

Author

Commented:
The OS you mean?  It is 2003 R2 Enterprise SP1.  
In case it matters:  Microsoft SQL Server Reporting Services Designers
Version 9.00.4035.00

Author

Commented:
Update: I have noticed that the Average column isn't ALWAYS Red.  Sometimes it's Green, sometimes Yellow.  So, the conditional is working there, I just can't tell what it is keying on.  So, the entire column will have a single background color, instead of each individual having it's own, dependent on the value.
This happens if I put the condition on the Average column itself.  Again, if I put nothing there, it picks up the conditional format from the rest of the matrix in the Preview, but is always a white background in the Deployed version.

Is there maybe someway to change the condition script to refer to itself?   I tried  me.Value instead of Avg(Fields!Ping.Value)   but that didn't work.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That looks fine for adding a fourth condition to the background color.  Interesting, but not really my problem.  Did you put anything in the properties for the average field (i.e. rt-click the green triangle)?  If not, then yours is working the same as mine, except yours apparently works in both preview and deployed. You're saying it might be caused by the service pack level of the OS?
What happens if you try putting conditions on the average fields themselves?  
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
The project is publishing to: http://localhost/reportserver
So, development and deployment are on the same server.  Is there anything else to check, as far as version level?
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Well, you've probably upgraded all components when you installed SP3 but to be 100% sure you could connect to your report server using the URL that you posted in Internet Explorer.  It should tell you "Microsoft SQL Server Reporting Services Version 9.00.4035.00"

Any luck with the report that I attached?

Author

Commented:
It is the correct version.  Got the Galactic database and the report installed.  Looks fine in both Preview and Deployed.  Haven't yet figured out the difference.  It seems to be a bit more complex than my simple Matrix report, but other than that, the field setup and conditions are the same.  If I figure out the difference and get mine working, I will post here.

Commented:
The easiest way is to change the Matrix into tabular if it is not that complicated or not time consuming. The matrix is not as flexible as Tabular in 2005 version.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.