Change row background color when column value changes

I am looking for a way to alternate the row color when a value in a
column changes. I am using a simple table report. The output of which
looks like
Application Name Version Usages
MicroStation 8.1.2.1 10
MicroStation 8.9.2.1 4
MicroStation 9.1.1.1 1
MS Word 8.0.0.0 1
MS Word 9.1.2.1 2
Excel 9.1.2.1 2
What I want to do is have the background color change when the
Application Name value changes. In this case the rows for MicroStation
and Excel would be the same color and MS Word would have another color.
In ASP.NET I would do this by creating some temp variables to keep
track of the previous row value and the current color. When the row
value changes I would flip the current color value. But I am not sure
how to have variables in Reporting ServicesUse the Previous function to compare the previous row:
Previous(Fields!ApplicationName.Value)
--
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"mkellerm" wrote:
> I am looking for a way to alternate the row color when a value in a
> column changes. I am using a simple table report. The output of which
> looks like
> Application Name Version Usages
> MicroStation 8.1.2.1 10
> MicroStation 8.9.2.1 4
> MicroStation 9.1.1.1 1
> MS Word 8.0.0.0 1
> MS Word 9.1.2.1 2
> Excel 9.1.2.1 2
> What I want to do is have the background color change when the
> Application Name value changes. In this case the rows for MicroStation
> and Excel would be the same color and MS Word would have another color.
> In ASP.NET I would do this by creating some temp variables to keep
> track of the previous row value and the current color. When the row
> value changes I would flip the current color value. But I am not sure
> how to have variables in Reporting Services
>|||It isn't that simple, as I also need to know what the previous rows
background color was. and I can't find a way to pull that value into
the expression
Steve Mann wrote:
> Use the Previous function to compare the previous row:
> Previous(Fields!ApplicationName.Value)
> --
> RDA Corp
> Business Intelligence Evangelist Leader
> www.rdacorp.com
>
> "mkellerm" wrote:
> > I am looking for a way to alternate the row color when a value in a
> > column changes. I am using a simple table report. The output of which
> > looks like
> >
> > Application Name Version Usages
> > MicroStation 8.1.2.1 10
> > MicroStation 8.9.2.1 4
> > MicroStation 9.1.1.1 1
> > MS Word 8.0.0.0 1
> > MS Word 9.1.2.1 2
> > Excel 9.1.2.1 2
> >
> > What I want to do is have the background color change when the
> > Application Name value changes. In this case the rows for MicroStation
> > and Excel would be the same color and MS Word would have another color.
> >
> > In ASP.NET I would do this by creating some temp variables to keep
> > track of the previous row value and the current color. When the row
> > value changes I would flip the current color value. But I am not sure
> > how to have variables in Reporting Services
> >
> >|||mkellerm,
I don't know if this will meet your needs, but perhaps it will spark an
idea. Also, this function works in SQL Server 2005, but probably not in
SQL Server 2000.
SQL Statement:
SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
applicationName, version, usages FROM applications
Results:
rowID groupID applicationName version usages
-- -- -- -- --
6 1 Excel 9.1.2.1 2
1 2 MicroStation 8.1.2.1 10
2 2 MicroStation 8.9.2.1 4
3 2 MicroStation 9.1.1.1 1
4 3 MS Word 8.0.0.0 1
5 3 MS Word 9.1.2.1 2
Then make your background color an expression like:
=Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
That will alternate the row color when the value changes. If you need 3
colors, use a Switch like:
=Switch(Fields!groupID.Value Mod 3 =0,"Transparent",Fields!groupID.Value Mod 3 =1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
Hope that helps a little.
-Josh
mkellerm wrote:
> It isn't that simple, as I also need to know what the previous rows
> background color was. and I can't find a way to pull that value into
> the expression
>
> Steve Mann wrote:
> > Use the Previous function to compare the previous row:
> >
> > Previous(Fields!ApplicationName.Value)
> >
> > --
> > RDA Corp
> > Business Intelligence Evangelist Leader
> > www.rdacorp.com
> >
> >
> > "mkellerm" wrote:
> >
> > > I am looking for a way to alternate the row color when a value in a
> > > column changes. I am using a simple table report. The output of which
> > > looks like
> > >
> > > Application Name Version Usages
> > > MicroStation 8.1.2.1 10
> > > MicroStation 8.9.2.1 4
> > > MicroStation 9.1.1.1 1
> > > MS Word 8.0.0.0 1
> > > MS Word 9.1.2.1 2
> > > Excel 9.1.2.1 2
> > >
> > > What I want to do is have the background color change when the
> > > Application Name value changes. In this case the rows for MicroStation
> > > and Excel would be the same color and MS Word would have another color.
> > >
> > > In ASP.NET I would do this by creating some temp variables to keep
> > > track of the previous row value and the current color. When the row
> > > value changes I would flip the current color value. But I am not sure
> > > how to have variables in Reporting Services
> > >
> > >|||Josh,
Thanks, this looks promissing, but where does the DENSE_RANK function
come from SQL 2005 says it is unrecognized function name,
-Marcus
Josh wrote:
> mkellerm,
> I don't know if this will meet your needs, but perhaps it will spark an
> idea. Also, this function works in SQL Server 2005, but probably not in
> SQL Server 2000.
> SQL Statement:
> SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
> applicationName, version, usages FROM applications
> Results:
> rowID groupID applicationName version usages
> -- -- -- -- --
> 6 1 Excel 9.1.2.1 2
> 1 2 MicroStation 8.1.2.1 10
> 2 2 MicroStation 8.9.2.1 4
> 3 2 MicroStation 9.1.1.1 1
> 4 3 MS Word 8.0.0.0 1
> 5 3 MS Word 9.1.2.1 2
> Then make your background color an expression like:
> =Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
> That will alternate the row color when the value changes. If you need 3
> colors, use a Switch like:
> =Switch(Fields!groupID.Value Mod 3 => 0,"Transparent",Fields!groupID.Value Mod 3 => 1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
> Hope that helps a little.
> -Josh
>
> mkellerm wrote:
> > It isn't that simple, as I also need to know what the previous rows
> > background color was. and I can't find a way to pull that value into
> > the expression
> >
> >
> > Steve Mann wrote:
> > > Use the Previous function to compare the previous row:
> > >
> > > Previous(Fields!ApplicationName.Value)
> > >
> > > --
> > > RDA Corp
> > > Business Intelligence Evangelist Leader
> > > www.rdacorp.com
> > >
> > >
> > > "mkellerm" wrote:
> > >
> > > > I am looking for a way to alternate the row color when a value in a
> > > > column changes. I am using a simple table report. The output of which
> > > > looks like
> > > >
> > > > Application Name Version Usages
> > > > MicroStation 8.1.2.1 10
> > > > MicroStation 8.9.2.1 4
> > > > MicroStation 9.1.1.1 1
> > > > MS Word 8.0.0.0 1
> > > > MS Word 9.1.2.1 2
> > > > Excel 9.1.2.1 2
> > > >
> > > > What I want to do is have the background color change when the
> > > > Application Name value changes. In this case the rows for MicroStation
> > > > and Excel would be the same color and MS Word would have another color.
> > > >
> > > > In ASP.NET I would do this by creating some temp variables to keep
> > > > track of the previous row value and the current color. When the row
> > > > value changes I would flip the current color value. But I am not sure
> > > > how to have variables in Reporting Services
> > > >
> > > >|||Marcus,
Not sure what is going on there... The DENSE_RANK function works just
fine for me. This MSDN page has a sample query that uses the
AdventureWorks database. Does this change anything?
http://msdn2.microsoft.com/en-us/library/ms173825.aspx
-Josh
mkellerm wrote:
> Josh,
> Thanks, this looks promissing, but where does the DENSE_RANK function
> come from SQL 2005 says it is unrecognized function name,
> -Marcus
> Josh wrote:
> > mkellerm,
> >
> > I don't know if this will meet your needs, but perhaps it will spark an
> > idea. Also, this function works in SQL Server 2005, but probably not in
> > SQL Server 2000.
> >
> > SQL Statement:
> > SELECT rowID, DENSE_RANK() OVER (ORDER BY applicationName) AS groupID,
> > applicationName, version, usages FROM applications
> >
> > Results:
> > rowID groupID applicationName version usages
> > -- -- -- -- --
> > 6 1 Excel 9.1.2.1 2
> > 1 2 MicroStation 8.1.2.1 10
> > 2 2 MicroStation 8.9.2.1 4
> > 3 2 MicroStation 9.1.1.1 1
> > 4 3 MS Word 8.0.0.0 1
> > 5 3 MS Word 9.1.2.1 2
> >
> > Then make your background color an expression like:
> > =Iif(Fields!groupID.Value Mod 2 = 0,"Transparent","LightYellow")
> >
> > That will alternate the row color when the value changes. If you need 3
> > colors, use a Switch like:
> > =Switch(Fields!groupID.Value Mod 3 => > 0,"Transparent",Fields!groupID.Value Mod 3 => > 1,"LightYellow",Fields!groupID.Value Mod 3 = 2,"Gainsboro")
> >
> > Hope that helps a little.
> >
> > -Josh
> >
> >
> > mkellerm wrote:
> > > It isn't that simple, as I also need to know what the previous rows
> > > background color was. and I can't find a way to pull that value into
> > > the expression
> > >
> > >
> > > Steve Mann wrote:
> > > > Use the Previous function to compare the previous row:
> > > >
> > > > Previous(Fields!ApplicationName.Value)
> > > >
> > > > --
> > > > RDA Corp
> > > > Business Intelligence Evangelist Leader
> > > > www.rdacorp.com
> > > >
> > > >
> > > > "mkellerm" wrote:
> > > >
> > > > > I am looking for a way to alternate the row color when a value in a
> > > > > column changes. I am using a simple table report. The output of which
> > > > > looks like
> > > > >
> > > > > Application Name Version Usages
> > > > > MicroStation 8.1.2.1 10
> > > > > MicroStation 8.9.2.1 4
> > > > > MicroStation 9.1.1.1 1
> > > > > MS Word 8.0.0.0 1
> > > > > MS Word 9.1.2.1 2
> > > > > Excel 9.1.2.1 2
> > > > >
> > > > > What I want to do is have the background color change when the
> > > > > Application Name value changes. In this case the rows for MicroStation
> > > > > and Excel would be the same color and MS Word would have another color.
> > > > >
> > > > > In ASP.NET I would do this by creating some temp variables to keep
> > > > > track of the previous row value and the current color. When the row
> > > > > value changes I would flip the current color value. But I am not sure
> > > > > how to have variables in Reporting Services
> > > > >
> > > > >