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/

Well I am really sufffering with that thing :), I have tried to use it, I discovered, that when you add in table #t row insert into #t values ('20110324','Car') Then it is not working. Because he is still trying to compare it to first value -> in that meaning it has Coloration status 1. But as You see, it should be 0, because there are rows insert into #t values ('20110320','Car') insert into #t values ('20110322','Car') insert into #t values ('20110323','Car') insert into #t values ('20110324','Car') And next hypotetical question. What if people in our department decides to see such thing colored only if it appears 10 or more times in a row ? Such code will be nasty.

In fact I solved that with cursor, but only because I process datasets of maximally 1000 rows, I cant imagine what can happen, if there will be 100 000 rows in future.