?
Solved

Conditional cell color formatting in SQL Report Table

Posted on 2007-11-26
9
Medium Priority
?
1,036 Views
Last Modified: 2010-05-18
Hello -
I've got a bit of a complicated formatting problem I can't figure out how to implement.  Basically I have a table with a datasource set to a stored procedure that computes a correlation matrix.  I would like to format four different settings.

1) All items in the last row are a shade of light blue
2) All itmes in the second to last row are a shade of light yellow (except the last cell)
3)All items in the last column are the same shade of light blue (easy - just set the bgcolor to the color)
4) All itmes along the horizontal line of the matrix are grey except the last item in the last column which should  remain light blue.

I've worked with much more basic formatting and using some simple embedded code but if anyone has a good example of how to best implement this I would SOOOOO appreciate it.

Thanks so much!
~E

0
Comment
Question by:gigglick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20351057
use reporting services
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20351064
Not sure if this is of any use but I gave up on making SRS color cells based on various report values.  I just returned a column called color from the stored procedure and set the bgcolor to color.value from the dataset.

You can get as complicated as you want in the stored procedure.

Made it easy to change colors too since I could just change the stored procedure.
0
 
LVL 5

Author Comment

by:gigglick
ID: 20351100
Hi kselvia:

Unfortunately, I don't think that will work for this particular problem as it's more of a row/cell issue I am having but it's it a GREAT (and very creative) idea I'll definitely try in the future!

~E
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 5

Author Comment

by:gigglick
ID: 20352419
I've got part 1-3 but I can't figure out the horizontal line:

=iif(RowNumber("HTrading_Impact")=14, "LemonChiffon",iif(RowNumber("HTrading_Impact")=15, "LightSteelBlue","Nothing"))

I am pretty sure I just need to change the last "Nothing" to another if where basically I do

if the (RowNumber-1/Columnumber) = 1 make it gray.  

Does anyone know how to deal with column numbers.  Everything I try is throwing errors.
0
 
LVL 5

Author Comment

by:gigglick
ID: 20352785
I've got part 1-3 but I can't figure out the horizontal line:

=iif(RowNumber("HTrading_Impact")=14, "LemonChiffon",iif(RowNumber("HTrading_Impact")=15, "LightSteelBlue","Nothing"))

I am pretty sure I just need to change the last "Nothing" to another if where basically I do

if the (RowNumber-1/Columnumber) = 1 make it gray.  

Does anyone know how to deal with column numbers.  Everything I try is throwing errors.
0
 
LVL 5

Author Comment

by:gigglick
ID: 20366191
Final Solution:

=iif((RowNumber("HTrading_Impact"))/2=1, "Gainsboro",iif(RowNumber("HTrading_Impact")=14, "LemonChiffon",iif(RowNumber("HTrading_Impact")=15, "LightCyan",iif(RowNumber("HTrading_Impact")=1, "Gainsboro","Nothing"))))
0
 
LVL 1

Expert Comment

by:ltmnm
ID: 20728275
Hello I'm trying to do something very close to what you have.  I have a report with the last column being a calculated result.  Basically I have an iif statement that checks if the difference between two numbers are in a range.  If they are it makes that row equal to a text value.  I want to add into this iif statement font coloring depending on the text.  Here is what I have working:

=IIF(Sum(Fields!FEQuant.Value)>=Fields!ContractLimit.Value, "VIOLATION", IIF(((Fields!ContractLimit.Value)-(Sum(Fields!FEQuant.Value)))=25,"WARNING",""))

How do I add to this so that it checks if it is a "VIOLATION" then make the font Red and if it is "WARNING" it should be Yellow?  

Thank you for all your help.
0
 
LVL 1

Accepted Solution

by:
ltmnm earned 2000 total points
ID: 20728483
Never mind my question.  I located where to put the IIF expression.  For anybody who would like to know: just highlight the cell then go to the properties panel at the right of the screen and click on color.  the drop down menu opens and you select <expression>.  In there you can put the color changing IIF statement.
0
 
LVL 5

Author Comment

by:gigglick
ID: 20769108
I answered my own ? and seems you did as well...so to close this ? I'm going to send the points your way.

Gig
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

770 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