Question: How do you create a green-bar (zebra stripe) report using groups in reporting services?
Answer: I have seen many examples of creating green-bar reports where each record alternated colors. All one has to do is add a row_number field to their query and use the following expression in the Background color property of the Table Row.
=iif(Fields!row.Value Mod 2 = 0,"Green","White")
But when doing groups and you want each group to alternate colors it is a little more complex, but the trick is still the same. Following the same principal I needed a way to get row numbers for each Group of data. In my example I am pulling Employee data from the AdventureWorks database. To add the group row numbers I first use a Common Table Expression to pull a list of distinct states. Then join that list back to the view adding in the row numbers.
WITH States AS
(
SELECT DISTINCT [StateProvinceName]
FROM [AdventureWorks].[HumanResources].[vEmployee]
)
SELECT V.[EmployeeID]
,V.[Title]
,V.[FirstName]
,V.[MiddleName]
,V.[LastName]
,V.[Suffix]
,V.[JobTitle]
,V.[Phone]
,V.[EmailAddress]
,V.[EmailPromotion]
,V.[AddressLine1]
,V.[AddressLine2]
,V.[City]
,V.[StateProvinceName]
,V.[PostalCode]
,V.[CountryRegionName]
,V.[AdditionalContactInfo]
,S.row
FROM [AdventureWorks].[HumanResources].[vEmployee] AS V
JOIN (SELECT [StateProvinceName], ROW_NUMBER() OVER (ORDER BY [StateProvinceName]) AS row FROM States) AS S
ON V.[StateProvinceName] = S.[StateProvinceName]
ORDER BY V.[StateProvinceName]
Resources:
Chris Hays's Reporting Services Sleazy Hacks Weblog
Green-Bar Matrix
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
SQL Server 2005 Books Online (September 2007)
ROW_NUMBER (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186734.aspx