Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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

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
tikkub
Asked:
tikkub
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
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
 
tikkubAuthor Commented:
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
 
tikkubAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now