Solved

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

Posted on 2009-07-09
13
667 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:3dNOVA
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:3dNOVA
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now