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

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.
3dNOVAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HadushCommented:
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.
0
3dNOVAAuthor 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.
0
ValentinoVBI ConsultantCommented:
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....)
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

3dNOVAAuthor 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
0
3dNOVAAuthor 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.
0
ValentinoVBI ConsultantCommented:
I took one of my test reports that contained a matrixx and added totals for both the main row and column groups.  And then added an expression on the data field for the background coloring.  And it's working fine, both in preview and when deployed (on SSRS 2005 with SQL Server SP3 installed).

This is what my data field is showing, in other words the Value property of the Data textbox:
=Avg(Fields!TotalAmount.Value)

And this is the conditional formatting, the BackgroundColor property of that same data textbox:

=IIF
(
    Avg(Fields!TotalAmount.Value) = 0,
    "Blue",
    IIF
    (
        Avg(Fields!TotalAmount.Value) < 100,
        "Red",
        IIF
        (
            Avg(Fields!TotalAmount.Value) >= 100 and Avg(Fields!TotalAmount.Value) < 300,
            "Yellow",
            "Green"
        )
    )
)

With this expression the totals will be either red, yellow or green.  Same goes for the data values.  The gaps in the matrix data (cells without value) will be blue.
0
3dNOVAAuthor 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?  
0
ValentinoVBI ConsultantCommented:
With Service Pack I was not referring to the OS but to the SQL Server installation on your deployment machine.  Your development environment has got SP3 installed (9.00.4035), is it the same in your deployment environment?

I did not change anything in the Subtotal properties, it seems to work fine without any modification there.

I'll attach the RDL, then you can try the report that I used in your environment.  You'll need the database as well, it can be downloaded.  This report was built following instructions in a book long time ago (I always keep these to play around with :-)
The database can be downloaded here: http://www.mhprofessional.com/product.php?cat=112&isbn=0072262397

If you click the Code link you'll get a ZIP file.  In this file is a file called "Galactic Database Install.zip", that's the one you need.

Hope this helps you get your issue resolved.

BTW: remove the .txt on the attachment, I couldn't get it uploaded otherwise...

ConditionalBackground.rdl.txt
0
3dNOVAAuthor 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?
0
ValentinoVBI ConsultantCommented:
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?
0
3dNOVAAuthor 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.
0
HadushCommented:
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.
0
3dNOVAAuthor Commented:
Well, after looking over all the test stuff that Valentino sent, and writing the test matrix up that he suggested, I found that all that worked fine.  However, this was all identical to the way I already had mine made up.  So, I started from scratch with mine, and it worked just fine.  I compared my new one with my old one, and I can't see any difference (except that one works when deployed and the other doesn't).  So, my conclusion is that, when I was working on my original and changing properties all over the place, that somehow caused a glitch.  By the time I got it written correctly, it would no longer publish correctly, for some unknown reason.
So, in the end, I was right all along, and just had to redo it to fix.   I will mark one of Valentino's responses as an accepted solution, so he get's points for trying to help out.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.