It is not the same question for individual rows (this expression): =Iif( RowNumber(nothing) mod 2 = 0, "Gray", "White") My problem is to alternate colorby 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

Try RowNumber ("GroupName"), where GroupName is the name of the group, such as: Code Snippet =Iif( RowNumber("GroupName") mod 2 = 0, "Gray", "White")

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

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>

Beautiful!!!!! Thank you Teo... thats is I call "Know How" it works perfect. Will be public (documtent) this kind of resources in future? thanks

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

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

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

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

found it : )) setting row color to: = IIF(RunningValue(Fields!LoanId.Value, CountDistinct, Nothing) Mod 2, "Gainsboro", "White")

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

Very good idea! Thank you.

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?