Archive

Posts Tagged ‘Matrix’

SSRS : Set BackGround Color of Matrix based on Column Values

March 14, 2016 Leave a comment

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: , ,