Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-30
3
Medium Priority
?
411 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
[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
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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