Solved

Building a cross tab report with custom cell colours based on the cell value, and group value

Posted on 2012-03-30
3
404 Views
Last Modified: 2012-06-27
If I have an input set as defined in the excel document, and desired output report, please let me know of a reporting services solution to accomplish the goal.
conditional-color-with-grouping-.xlsx
0
Comment
Question by:tikkub
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
It helps if you'd put your question in the question box instead of in a cell in an attached spreadsheet...

So your question is:

"This is the desired output.  The output is a crosstab report based on the input group on client, and employee.  Note that based on the status value, the cells have to be coloured.  Actual means green, and prohibited means red.  I can use a matrix report with client, adn employee as row group columns, and Status, and Year as column group, and create a cross tab report.  I can also check for the value to be 1, and colour appropriately.  However, I need to change the color based on the presence of value, and also the status.  Your help is very much appreciated."

Looks like only the coloring functionality is the part you're having some trouble with?  Could you explain a bit more what exactly you'd like to achieve?  Are you looking for a way to combine several fields in the same expression?

The cell property that you need to set is called BackgroundColor.  It accepts expressions, so you can write something like this:

=Switch(
  Fields!Status.Value = 1 and Not IsNothing(Fields!YourValueField.Value), "Green",
  Fields!Status.Value = 1 and IsNothing(Fields!YourValueField.Value), "Red",
  True, "White"
)

I've got a couple of other examples in my article on the Switch statement: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2279-Adding-an-Else-to-your-Switch.html
0
 

Author Comment

by:tikkub
Comment Utility
Sorry for the sloppy setup.  Thanks for parsing the statement from the file.

I am able to set color based on the value column.  I am also able to cross tab years using column groups.

Note that there are two column groups here.  One is the year column, and the other is the status column.  Client, and employee will form the row group. The main problem for me is that color is dependent on two values.

The output from dataset will always show a value of 1 for shown combinations.  The colors have to be set for shown years based on the status values.  Any missing values will be shown without colors.

Thanks for your help.
0
 

Author Comment

by:tikkub
Comment Utility
I have been able to solve the problem.  Your idea, and your article helped.  I had to use the switch statement combining two fields.  Thanks for your help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

11 Experts available now in Live!

Get 1:1 Help Now