[Webinar] Streamline your web hosting managementRegister Today

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
?
413 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 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

608 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