Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-09
13
Medium Priority
?
696 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 1200 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 1200 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
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 …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

916 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