Hello, I have simple matrix report, in rows there are products of our company, in columns are last 30 working days. In data is always "X" when product was not available in stock in particular day. I need to change color of data background for all products, which were not available in 3 or more consecutive days (All "X" colored) Data will be exported to Excel and could have 30 columns and hundreds of rows. People which uses such report has to see at first look, where are problems. Thx for help

I think you will be better of having a claculated column color or whatecer equel 0 or 1 based on your requirement.... Then in Matric you just write IIF expression for cell color, what do you think?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi URi, I probably dont understand what you mean. I see, that there will be another issue. They dont want to see only "X" but also number of orders placed by all customers that day. So instead of "X" therewill be number. So if for days 25.1.2011, 26.1.2011, 27.1.2011 will be for product "Bike" values "123","15","16" then all cells has to be colored. Could you give me little bit more info what you meant by calculated column color Thx a lot for your help

create table #t (dt datetime ,product varchar(20)) insert into #t values ('20110313','Bike') ---consistent insert into #t values ('20110314','Bike') insert into #t values ('20110315','Bike') insert into #t values ('20110320','Car') ---not consistent insert into #t values ('20110322','Car') insert into #t values ('20110323','Car') with cte as ( select *, row_number() over (partition by product order by dt) rn from #t ),cte1 as (select cte.*,coalesce(t1.dt,cte.dt) dt1 from cte left join cte t1 on cte.rn=t1.rn+1 and cte.product=t1.product ) select dt,dt1,product, case when exists (select * from cte1 c where datediff(d,dt1,dt)>1 and cte1.product=c.product ) then 1 else 0 end from cte1Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/