Conditional color in Matrix subtotal column

Hot Topic

SQL Reporting Date Parameters
SQL2000 Linked Server to Oracle
SQL2005 AS Named instance - SQL Browser issue?
Detect whether runtime or executiontime in custom component
How to positioning the tables.?
Recreating the functionality of a extended stored procedure that uses srv_impersonate_client
Problem updating SQL2005 From MSAccess Form
RS 2005, VS 2005 and specifying printer trays
SSIS Package is "hanging" after validation
Execute stored proc on a named instance

SQL Server

AdventureWorks\AdventureWorksDW question
Schedulling Jobs on SQL 2005
A lock could not be acquired after 16 attempts and timed out
parent and child reports
General error handling
creating a server instance on SQL server 2005 enterprise
Error with install of sept CTP Enterprise edition
WebServices with IIS or SQL?
Need Help creating a SSIS Package (Newbie)
rsAccessDenied

Conditional color in Matrix subtotal column

I am using matrix in my report with calculated fields, for example I have 3 columns : Actual amount, Budget and Variance. Variance should be in Red if it is negative. I can set up an expression to change the color on the row level, but not in Subtotal ot Total row. I cannot use sum of Fields! values, because Budget amount is also calculated field based on the "Category" value which is columns group on the matrix.

So anyway, if I am trying to reference ReportItems!Variance.Value in Subtotal Level, it gives me an error about group scope.

How can I access the cells values in subtotal and total group levels If anybody knows any tricks for this fairly simple task

Thanks for your help.

Olga

Answer this question

Conditional color in Matrix subtotal column

Blake Foster

Yicong,

Your approach would work if I did not use calculated fields for actual, budget and variance on the report. I cannot use expression: IIF((Sum(Fields!budget.Value)- Sum(Fields!actual.Value))

Instead I an using : ReportItems!Units.Value - ReportItems!Budget.Value, which does not allow me to include SUM function.

Thanks

Olga
Larry Charlton
Instead of setting an expression for the color of the row, you can set the color of all the textboxes in the detail and the corresponding textbox in the subtotal will change as well. Attached a RDL for your reference:

=============

< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>456fa1cb-315b-451b-ad6c-0c9338cc9ba0</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="empid">
<rd:DefaultName>empid</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!empid.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
<Grouping Name="matrix1_empid">
<GroupExpressions>
<GroupExpression>=Fields!empid.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="year">
<rd:DefaultName>year</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!year.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_year">
<GroupExpressions>
<GroupExpression>=Fields!year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.25in</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Actual</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Budget</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Variance</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Width>4in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
</Style>
<Height>1in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="actual">
<rd:DefaultName>actual</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!actual.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="budget">
<rd:DefaultName>budget</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!budget.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>=IIF((Sum(Fields!budget.Value)- Sum(Fields!actual.Value)) > 0,"Green","Red")</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!budget.Value)- Sum(Fields!actual.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>9bb6370e-2606-4c48-af9b-f266a2c9fa9b</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as empid, 1999 as year, 10 as actual, 9 as budget
union all
select 1 as empid, 2000 as year, 10 as actual, 12 as budget
union all
select 2 as empid, 1999 as year, 17 as actual, 15 as budget
union all
select 2 as empid, 2000 as year, 12 as actual, 9 as budget
union all
select 3 as empid, 1999 as year, 7 as actual, 9 as budget
union all
select 3 as empid,2000as year, 9 as actual, 11 as budget</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="empid">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>empid</DataField>
</Field>
<Field Name="year">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>year</DataField>
</Field>
<Field Name="actual">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>actual</DataField>
</Field>
<Field Name="budget">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>budget</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>7in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Pete Morrison

Ian,

Thank you for the code. I used it in my report and works partially. In case if total variance is negative, actual and budget amounts are also displayed in red, which is not my case.

Another issue I was trying to overcome - calculating the variance subtotals using RunningValue function for each row. I was using InScope function to reset m_value to 0 and it also worked only in some cases.

I think I will redesign the whole report and use MDX query to calculate all values I need, then I can use regular table instead of Matrix, or at least I could be able to use Fields values instead of calculating variance on report itself. So then I should have control of the colors.

Matrix properties is a pain. I wish MS made some improvements in future release or SP2.

Thanks, anyway. I might use your function in another cases.

Regards,

Olga
TRexian
Hello Olga,

There is a workaround that may work for you. It is fairly simple, but does require some custom code and a hidden column. The matrix data cell in the hidden column executes a custom method that adds the current value of the Variance data cell to a running total. This running total value is used in another method which is called when the Color property expression in the Subtotal cell is executed. This method checks the running total against 0 and returns the appropriate color as a string.

For example,

Add the following code to your custom code section in the Report Properties dialog.

Private m_value As Double

Public Function AddValue(aValue As Double) As Double
m_value = m_value + aValue
Return m_value
End Function

Public Function GetColor() As String
Dim retVal As String
If m_value < 0 Then
retVal = "Red"
Else
retval="Black"
End if

m_value = 0

Return retVal
End Function

Set the Value for the Cell in the hidden column to the following expression.

=Code.AddValue(ReportItems!Variance.Value)

Set the Color property of the Subtotal cell to the following expression.

=Code.GetColor()

For more information on custom code:
Using Custom Code References in Expressions (Reporting Services)
How to: Add Code to a Report (Report Designer)

Ian
Conditional color in Matrix subtotal column

Answer this question