Solved

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

Posted on 2009-07-09
13
677 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.
0
Comment
Question by:3dNOVA
  • 7
  • 4
  • 2
13 Comments
 
LVL 8

Expert Comment

by:Hadush
ID: 24814065
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
 

Author Comment

by:3dNOVA
ID: 24814273
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 24815385
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:3dNOVA
ID: 24815615
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
 

Author Comment

by:3dNOVA
ID: 24817788
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 300 total points
ID: 24820948
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
 

Author Comment

by:3dNOVA
ID: 24821937
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 300 total points
ID: 24822416
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
 

Author Comment

by:3dNOVA
ID: 24822610
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 24822950
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
 

Author Comment

by:3dNOVA
ID: 24823859
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
 
LVL 8

Expert Comment

by:Hadush
ID: 24825331
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
 

Accepted Solution

by:
3dNOVA earned 0 total points
ID: 24925456
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
How to increase the row limit in Jasper Server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question