Solved

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

Posted on 2012-03-30
3
407 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
ID: 37790807
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
ID: 37792008
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
ID: 37792078
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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