Home > Home, MSDN, SSRS > SSRS : Set BackGround Color of Matrix based on Column Values

SSRS : Set BackGround Color of Matrix based on Column Values

This question I got from MSDN,  Link to Original question click here

In brief:- I have a requirement to set Red color for NULL values 

Actually what user wants is like when in column group header if any null value comes then entire column values should highlighted red color.

Lets create one sample Matrix report based on sample data query as given below:-

select ‘485018N’StudentID ,‘PLW’Section ,‘NULL’Outcome UNION

select ‘4P0258P’,‘H+SC’,‘NULL’ UNION

select ‘447627K’,‘GEM’,‘NULL’ UNION

select ‘504911P’,‘PLW’,‘NULL’ UNION

select ‘506517P’,‘PLW’,‘NULL’ UNION

select ‘508297P’,‘PLW’,‘NULL’ UNION

select ‘600217T’,‘PLW’,‘NULL’ UNION

select ‘602600R’,‘PLW’,‘NULL’ UNION

select ‘603210S’,‘PLW’,‘NULL’ UNION

select ‘604221T’,‘PLW’,‘NULL’ UNION

select ‘608991R’,‘H+SC’,‘NULL’ UNION

select ‘700469V’,‘PLW’,‘NULL’ UNION

select ‘604797T’,‘GEM’,‘NULL’ UNION

select ‘705941V’,‘PLW’,‘NULL’ UNION

select ‘706996V’,‘PLW’,‘NULL’ UNION

select ‘707082V’,‘INFT’,‘NULL’ UNION

select ‘605388T’,‘GEM’,‘Education’ UNION

select ‘701380V’,‘SPRT’,‘Education’ UNION

select ‘703485V’,‘PLW’,‘Education’ UNION

select ‘604292S’,‘GEM’,‘Education’ UNION

select ‘603135S’,‘PLW’,‘Education’ UNION

select ‘505070P’,‘PLW’,‘Education’ UNION

select ‘449216M’,‘H+SC’,‘Education’ UNION

select ‘14072112’,‘A+D’,‘Not in Paid Employment’ UNION

select ‘1367026’,‘PLW’,‘Not in Paid Employment’ UNION

select ‘1367203’,‘BATS’,‘Not in Paid Employment’ UNION

select ‘1366337’,‘PLW’,‘Not in Paid Employment’ UNION

select ‘14072148’,‘H+SC’,‘Not in Paid Employment’ UNION

select ‘14072006’,‘HABE’,‘Not in Paid Employment’ UNION

select ‘14071834’,‘INFT’,‘Not in Paid Employment’ UNION

select ‘14071411’,‘SPRT’,‘Not in Paid Employment’

Above query will result dataset with three columns as given below:-

DataSetColumns

Drag Matrix control on report body and select below fields in different area in Matrix:-

Section Field –> Matrix Rows Value

Outcome Field –> Columns Value

StudentID   (Take Count Of StudentID)–>Data  Value

Matrix Columns

After above things applied report design and layout is look like below:-

Matrix Design

Lets Apply background color to the Data Values section based on Column group header.

I.e Count(studentID) field background color expression based on Outcome field value.

User wants when outcome value is NULL (its a string value not database null) then Count(studentID) complete column should be shown red.

Lets write below expression :-

=IIF(Fields!Outcome.Value=”NULL”,”Red” ,”White”)

Above expression shown below:-

Expression

After setting just run the report, report look like below:-

Report

This is what user highlighted, requirement is complete column should be RED.

Then modify the expression as below:-

=IIF(ReportItems!Outcome.Value=”NULL”,”Red” ,”White”)

ReportItems!Outcome.Value —> Outcome is the textbox name of the column group as shown below:-

TextBoxProperty

After setting above expression, run the report. Report look like below:-

Report_fix

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Advertisements
Categories: Home, MSDN, SSRS Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: