Background color alternate in tablix for group (not individual rows)

Question

It is not the same question for individual rows (this expression):

 

=Iif( RowNumber(nothing) mod 2 = 0, "Gray", "White")

 

My problem is to alternate color by group (all rows in this group) and alternate for the next group.

 

I have IDGroup, but it is not consecutive in filter context.

 

Any idea about?

 

Thanks

 

 

 

 

Answer 1

Try RowNumber ("GroupName"), where GroupName is the name of the group, such as:

 

Code Snippet

=Iif( RowNumber("GroupName") mod 2 = 0, "Gray", "White")

 

 

Answer 2

 

thanks Teo, but it doesn't work... this make that every row in the group alternate color and i'm not looking for that, i need all the rows in the group with the same color and alternate with the next group.

 

Something like that....

 

--------------------------------------------------

Group1         Row1

------------------------------

Row2

-------------------------------

Row3

---------------------------------------------------

Group2         Row1

------------------------------

Row2

--------------------------------------------------

Group3          Row1

------------------------------

Row2

------------------------------

Row3

------------------------------

 

Answer 3

I see now what you are after. Thanks for explaining this. This requires some "black belt" programming  Take a look at the attached report. Before you start analyzing it, you should know that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics. I know this may sound to you like Greek , so let me jump into the implementation details:

 

1. The report has a EvenRow code-behind function that toggles each time it's executed.

2. In the Category group (double-click it to access its properties), a EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.

 

The rest is easy. I set the BackgroundColor property for each textbox to use this variable. BTW, the report uses the SSAS Adventure Works cube.

 

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<DataProvider>OLEDB-MD</DataProvider>

<ConnectString>Data Source=.;Initial Catalog="Adventure Works DW 2008"</ConnectString>

<IntegratedSecurity>true</IntegratedSecurity>

</ConnectionProperties>

<rd:DataSourceID>f3ce144f-01cb-4810-9095-67f988f3bcb6</rd:DataSourceID>

</DataSource>

</DataSources>

<DataSets>

<DataSet Name="DataSet1">

<Fields>

<Field Name="Category">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Category]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Subcategory">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Subcategory]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Product">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Categories].[Product]" /&gt;</DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Reseller_Sales_Amount">

<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Reseller Sales Amount]" /&gt;</DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>DataSource1</DataSourceName>

<CommandText> SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<rd:DesignerState><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:DesignerState>

</Query>

</DataSet>

</DataSets>

<Body>

<ReportItems>

<Tablix Name="Tablix1">

<TablixBody>

<TablixColumns>

<TablixColumn>

<Width>1in</Width>

</TablixColumn>

</TablixColumns>

<TablixRows>

<TablixRow>

<Height>0.25in</Height>

<TablixCells>

<TablixCell>

<CellContents>

<Textbox Name="Textbox1">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>Reseller Sales Amount</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Textbox1</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixCell>

</TablixCells>

</TablixRow>

<TablixRow>

<Height>0.25in</Height>

<TablixCells>

<TablixCell>

<CellContents>

<Textbox Name="Reseller_Sales_Amount">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>=Fields!Reseller_Sales_Amount.Value</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<BackgroundColor>=iif(Variables!EvenRow.Value, "White", "PaleGreen")</BackgroundColor>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixCell>

</TablixCells>

</TablixRow>

</TablixRows>

</TablixBody>

<TablixColumnHierarchy>

<TablixMembers>

<TablixMember />

</TablixMembers>

</TablixColumnHierarchy>

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<TablixHeader>

<Size>1.22917in</Size>

<CellContents>

<Textbox Name="Textbox7">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>Category</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Textbox7</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember>

<TablixHeader>

<Size>1.46875in</Size>

<CellContents>

<Textbox Name="Textbox9">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>Subcategory</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Textbox9</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember>

<TablixHeader>

<Size>1.85417in</Size>

<CellContents>

<Textbox Name="Textbox11">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>Product</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Textbox11</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember />

</TablixMembers>

</TablixMember>

</TablixMembers>

</TablixMember>

</TablixMembers>

<KeepWithGroup>After</KeepWithGroup>

</TablixMember>

<TablixMember>

<Group Name="Category">

<GroupExpressions>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

<Variables>

<Variable Name="EvenRow">

<Value>=Code.EvenRow()</Value>

</Variable>

</Variables>

</Group>

<SortExpressions>

<SortExpression>

<Value>=Fields!Category.Value</Value>

</SortExpression>

</SortExpressions>

<TablixHeader>

<Size>1.22917in</Size>

<CellContents>

<Textbox Name="Category">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>=Fields!Category.Value</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<BackgroundColor>=iif(Variables!EvenRow.Value, "White", "PaleGreen")</BackgroundColor>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember>

<Group Name="Subcategory">

<GroupExpressions>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Group>

<SortExpressions>

<SortExpression>

<Value>=Fields!Subcategory.Value</Value>

</SortExpression>

</SortExpressions>

<TablixHeader>

<Size>1.46875in</Size>

<CellContents>

<Textbox Name="Subcategory">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>=Fields!Subcategory.Value</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<BackgroundColor>=iif(Variables!EvenRow.Value, "White", "PaleGreen")</BackgroundColor>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember>

<Group Name="Product">

<GroupExpressions>

<GroupExpression>=Fields!Product.Value</GroupExpression>

</GroupExpressions>

</Group>

<SortExpressions>

<SortExpression>

<Value>=Fields!Product.Value</Value>

</SortExpression>

</SortExpressions>

<TablixHeader>

<Size>1.85417in</Size>

<CellContents>

<Textbox Name="Product">

<CanGrow>true</CanGrow>

<KeepTogether>true</KeepTogether>

<Paragraphs>

<Paragraph>

<TextRuns>

<TextRun>

<Value>=Fields!Product.Value</Value>

<Style />

</TextRun>

</TextRuns>

<Style />

</Paragraph>

</Paragraphs>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

<Border>

<Color>LightGrey</Color>

<Style>Solid</Style>

</Border>

<BackgroundColor>=iif(Variables!EvenRow.Value, "White", "PaleGreen")</BackgroundColor>

<PaddingLeft>2pt</PaddingLeft>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

</Style>

</Textbox>

</CellContents>

</TablixHeader>

<TablixMembers>

<TablixMember>

<Group Name="Details" />

</TablixMember>

</TablixMembers>

</TablixMember>

</TablixMembers>

</TablixMember>

</TablixMembers>

</TablixMember>

</TablixMembers>

</TablixRowHierarchy>

<DataSetName>DataSet1</DataSetName>

<Height>0.5in</Height>

<Width>5.55208in</Width>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</Tablix>

</ReportItems>

<Height>2in</Height>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</Body>

<Code>Public _evenRow As Boolean

Public Function EvenRow() As Boolean

_evenRow = Not _evenRow

return _evenRow

End Function</Code>

<Width>7.98958in</Width>

<Page>

<PageHeader>

<Height>0.25in</Height>

<PrintOnFirstPage>true</PrintOnFirstPage>

<PrintOnLastPage>true</PrintOnLastPage>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</PageHeader>

<PageFooter>

<Height>0.25in</Height>

<PrintOnFirstPage>true</PrintOnFirstPage>

<PrintOnLastPage>true</PrintOnLastPage>

<Style>

<Border>

<Style>None</Style>

</Border>

</Style>

</PageFooter>

<Style />

</Page>

<rd:ReportID>580b4577-4adb-4b9c-91cb-dd6b6efbbe02</rd:ReportID>

<rd:ReportUnitType>Inch</rd:ReportUnitType>

</Report>

 

 

Answer 4

Beautiful!!!!!

 

Thank you Teo... thats is I call "Know How" it works perfect.

 

Will be public (documtent) this kind of resources in future?

 

thanks

Answer 5

Documentation is coming up. Robert Bruckner has written a great blog about report variables. I've also documented the solution in my blog.

Answer 6

Teo, this behavior can't be done with RS2005?

Answer 7

Yes. In SSRS 2005 things are simpler because the code variables survive report paging.

 

Answer 8

Teo Lachev wrote:

Yes. In SSRS 2005 things are simpler because the code variables survive report paging.

 

Teo,

could you please explain how to implement this in SSRS 2005?

I have a report with two tables, each filled from its own dataset. The idea is to change the row color within table when a specific field changes its value. So there will be sets of several rows of the same color, then another set of alternate color within each of the two tables.

 

Thank you

Answer 9

 

found it : ))

setting row color to:

 

= IIF(RunningValue(Fields!LoanId.Value, CountDistinct, Nothing) Mod 2, "Gainsboro", "White")

Answer 10

The above is good for this implementation...however I want to do something similar, yet different, and the above method does not work.

In 2008, I want to be able to put a BORDER around the whole group.  That will entail bordering the "group header" and "group footer", however in 2008, there is neither.  In 2005, there was a specific row for each, and hence group header and footer were EASILY formatted separately from the detail rows (just select, and format each individually as desired).

 

?

 

Thx,

Allen

 

 

 

 

Answer 11

Very good idea! Thank you.

Answer 12

This method fails when I sort my group by an aggregate such as [Sum(Revenue)]. The colors are there but they are all mixed up from row to row, like red-red-red-green-green-red-red. This only seems to work when I sort by the same field that I'm grouping by. It looks like the group variable is set in order based on the grouped field being assumed to be what the report will be sorted by. But when I set the sorting tab to set the group to sort by a different field than it is being grouped by, the group variable values don't get recalculated and the row colors end up mixed up and out of order. Does anyone have a workaround for this?

Answer 13

I have the same problem -- if I remove my sorting based on an aggregate, the grouped formatting works.  It's like the group's variable values are determined pre-aggregated sort.  Any ideas?