Background Color
-
Hi guys,
In a table I can select background color for rows. However I want to set the background color for every other row. Like even row numbers are gray, odd row numbers are transparent. I have no programming background, just pure SQL DBA and hardware guy. Is there a way to do this?
Thanks.
-
Yes this is easy to do in a table. Basically you need to make this an expression. This gets more complicated it you have grouping though. There is a RowNumber function which in its simplest use will return the current row number during table rendering, just check if this is odd or even and set the appropriate colour.
The expression should look something like:
=iif(RowNumber(nothing) mod 2 = 0, "Silver", "White")
As I manage lots of reports I actually use a stored proc to return a recordset of my custom colours so that I can make one change to update all reports. I can supply more details if anybody is interested.
-
Thanks for the reply. I forgot the IIF
. However the result set is a table come with a sort order input by the users. Currently I use the IF... THEN and dynamic SQL:IF @SortOrder = 'ABC'
BEGIN
set @SQLString = 'SELECT * FROM TABLE #SOMETHING ORDER BY ' + @SortOrder
exec SP_EXECUTESQL @SQLString
END
IF @SortOrder = 'DEF'
BEGIN
set @SQLString = 'SELECT * FROM TABLE #SOMETHING ORDER BY ' + @SortOrder
exec SP_EXECUTESQL @SQLString
END
To add a row number the only thing I can think of is to do a SELECT INTO in the SQLString to get the result set in another temp table, alter table to add an identity column, and return the result set to the Report server.
IF @SortOrder = 'ABC'
BEGIN
set @SQLString = 'SELECT * into #FINAL FROM TABLE #SOMETHING ORDER BY ' + @SortOrder
exec SP_EXECUTESQL @SQLString
ALTER TABLE #FINAL ADD ID INT IDENTITY (1,1)
SELECT * FROM #FINAL
END
IF @SortOrder = 'DEF'
BEGIN
set @SQLString = 'SELECT * INTO #FINAL FROM TABLE #SOMETHING ORDER BY ' + @SortOrder
exec SP_EXECUTESQL @SQLString
ALTER TABLE #FINAL ADD ID INT IDENTITY (1,1)
SELECT * FROM #FINAL
END
Is there a better way to do same thing?
Thanks.
-
You don't say which version of SQL and RS you are using. But if you are using SQL 2005 use the interactive sort feature with the IIF code above and it should work fine. With no need to have a row number in the source data.
I am not quite sure why you need to add row numbers to the source data as Reporting Services will handle that for you. The RS RowNumber function will generate these.
If you need the row numbers for something else in T-SQL 2005 there is the Row_Number function which would save you using the alter table.
To avoid the dynamic SQL you could use a case statement to create a new column to sort by in a sub select, but you would still have to alter the table for SQL 2000. See the example below
select *
from (
select case @SortOrder
when 'abc' then col1
when 'def' then col2
else col3
end as SortOrder,
<Other fields>
from SomeTable
) as Src
Order by Src.SortOrder
I hope this helps.
-
Thanks Piers. I am using SQL 2000 RS so the Iff(RowNumber) is not applicable, at least as you said so. I will modify the stored procedure to remove the dynamic SQL string.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply