SSRS Color Wheel

SSRS (2005) Color Wheel

...more like a color list, but who's keeping score

I don't think there is anything in the development world more looked down upon than writing a report. It's kind of got a little shame attached to it. While your friends are (seemingly) out there creating the next Twitter you are stuck building a list of sales grouped by region. However, I have always been a little fond of it. I have an interest in design and layout, problem solving, and organizing data: reporting can touch on all of them.

Recently I had a request to change the color scheme of a report I was working with. This wasn't out of the ordinary except they weren't too sure what they wanted. It got to the point where I started to wonder if their significant other had similar thoughts when they shopped for paint.

However, our team had an idea - why not provide a report that listed all the default colors available in the system, along with all their official names. This had some immediate advantages:

• The user could refer to a color they wanted by name
• SSRS 2005 has a "bug" where colors on the report don't look the same when that report is exported to Excel

 

Now that's what I call a sexy report!

[The Plan]

I looked into different ways of building this report and came up with the plan below. Unfortunately I was not able to generate the color list completely in code. It got to the point where I thought I would have to build an external library to provide the list from System.Drawing, and I'm not a fan of maintaining a code library for a simple color list.

Instead, I built the list of available colors using a simple query. While this may seem a little rudimentary, it has the additional benefit of encouraging management to implement a standard color scheme. This could be the first step towards using the database to provide a standardized color palette for reporting.

I'm not big on providing downloads for examples. I feel that involves a level of trust that I cannot provide. So I explain how to do it below. I use SQL Server SSRS 2005 terms, because that's what the client is using.

[The Dataset]

MainDS:
-- manual list of colors in VS2005 system
Select 'Black' as [color_name] union all
Select 'White' union all
Select 'DimGray' union all
Select 'Gray' union all
Select 'DarkGray' union all
Select 'Silver' union all
Select 'LightGray' union all
Select 'Gainsboro' union all
Select 'WhiteSmoke' union all
Select 'Maroon' union all
Select 'DarkRed' union all
Select 'Red' union all
Select 'Brown' union all
Select 'Firebrick' union all
Select 'IndianRed' union all
Select 'Snow' union all
Select 'LightCoral' union all
Select 'RosyBrown' union all
Select 'MistyRose' union all
Select 'Salmon' union all
Select 'Tomato' union all
Select 'DarkSalmon' union all
Select 'Coral' union all
Select 'OrangeRed' union all
Select 'LightSalmon' union all
Select 'Sienna' union all
Select 'SeaShell' union all
Select 'Chocolate' union all
Select 'SaddleBrown' union all
Select 'SandyBrown' union all
Select 'PeachPuff' union all
Select 'Peru' union all
Select 'Linen' union all
Select 'Bisque' union all
Select 'DarkOrange' union all
Select 'BurlyWood' union all
Select 'Tan' union all
Select 'AntiqueWhite' union all
Select 'NavajoWhite' union all
Select 'BlanchedAlmond' union all
Select 'PapayaWhip' union all
Select 'Moccasin' union all
Select 'Orange' union all
Select 'Wheat' union all
Select 'OldLace' union all
Select 'FloralWhite' union all
Select 'DarkGoldenrod' union all
Select 'Goldenrod' union all
Select 'Cornsilk' union all
Select 'Gold' union all
Select 'Khaki' union all
Select 'LemonChiffon' union all
Select 'PaleGoldenrod' union all
Select 'DarkKhaki' union all
Select 'Beige' union all
Select 'LightGoldenrodYellow' union all
Select 'Olive' union all
Select 'Yellow' union all
Select 'LightYellow' union all
Select 'Ivory' union all
Select 'OliveDrab' union all
Select 'YellowGreen' union all
Select 'DarkOliveGreen' union all
Select 'GreenYellow' union all
Select 'Chartreuse' union all
Select 'LawnGreen' union all
Select 'DarkSeaGreen' union all
Select 'LightGreen' union all
Select 'ForestGreen' union all
Select 'LimeGreen' union all
Select 'PaleGreen' union all
Select 'DarkGreen' union all
Select 'Green' union all
Select 'Lime' union all
Select 'Honeydew' union all
Select 'SeaGreen' union all
Select 'MediumSeaGreen' union all
Select 'SpringGreen' union all
Select 'MintCream' union all
Select 'MediumSpringGreen' union all
Select 'MediumAquamarine' union all
Select 'Aquamarine' union all
Select 'Turquoise' union all
Select 'LightSeaGreen' union all
Select 'MediumTurquoise' union all
Select 'DarkSlateGray' union all
Select 'PaleTurquoise' union all
Select 'Teal' union all
Select 'DarkCyan' union all
Select 'Cyan' union all
Select 'Aqua' union all
Select 'LightCyan' union all
Select 'Azure' union all
Select 'DarkTurquoise' union all
Select 'CadetBlue' union all
Select 'PowderBlue' union all
Select 'LightBlue' union all
Select 'DeepSkyBlue' union all
Select 'SkyBlue' union all
Select 'LightSkyBlue' union all
Select 'SteelBlue' union all
Select 'AliceBlue' union all
Select 'DodgerBlue' union all
Select 'SlateGray' union all
Select 'LightSlateGray' union all
Select 'LightSteelBlue' union all
Select 'CornflowerBlue' union all
Select 'RoyalBlue' union all
Select 'MidnightBlue' union all
Select 'Lavender' union all
Select 'Navy' union all
Select 'DarkBlue' union all
Select 'MediumBlue' union all
Select 'Blue' union all
Select 'GhostWhite' union all
Select 'SlateBlue' union all
Select 'DarkSlateBlue' union all
Select 'MediumSlateBlue' union all
Select 'MediumPurple' union all
Select 'BlueViolet' union all
Select 'Indigo' union all
Select 'DarkOrchid' union all
Select 'DarkViolet' union all
Select 'MediumOrchid' union all
Select 'Thistle' union all
Select 'Plum' union all
Select 'Violet' union all
Select 'Purple' union all
Select 'DarkMagenta' union all
Select 'Fuchsia' union all
Select 'Magenta' union all
Select 'Orchid' union all
Select 'MediumVioletRed' union all
Select 'DeepPink' union all
Select 'HotPink' union all
Select 'LavenderBlush' union all
Select 'PaleVioletRed' union all
Select 'Crimson' union all
Select 'Pink' union all
Select 'LightPink'

[Extra Credit: Show each color's HTML Code color]

Report > Report Properties

References:
Added System.Drawing

Code:
Public Function HTMLColor(my_color as String) as String
Dim colorObj As System.Drawing.Color = System.Drawing.Color.FromName(my_color)
return String.Format("#{0:X2}{1:X2}{2:X2}", colorObj.R, colorObj.G, colorObj.B)
End Function

I got the meat of this function from a StackOverflow post:
http://stackoverflow.com/a/5207560/103131

[Layout]

Having everything in a query allowed me to get the most out of the layout options in SSRS. This made it really easy to setup a Table in the Layout to show what I wanted. I also added a column of White as a control. It really helped see the subtle differences in some of the lighter shades.

I put together a mock-up below:

[Column Name]: Property
[Color Name]: (value)=Fields!color_name.value
[Color]: (BackgroundColor)=Fields!color_name.value
[White]: (BackgroundColor)=White
[HTML Code]: (value)=Code.HTMLColor(Fields!color_name.Value)

I think this qualifies as a boring report, but it can be very useful when communicating which colors users want on their reports.

SSRS Report - Last Modified ...

Sometimes you know all the pieces and never think to use them together in a different way.

I've always wanted to add some sort of easy version number/system to my SQL Server Reporting Services (SSRS) 2005 reports.  I thought it would be a nice thing to have for users, especially ones that get well-acquainted with a certain report and want to know if you've published a change they requested.

The solution I came up with isn't perfect, and it suffers from some of the same reasons I resisted rolling our own solution.  Perhaps it will be better addressed in future versions. 

I always knew SSRS keeps a lot of data about the reports in a ReportServer database. Heck, I even wrote my own custom report server as a project for a past ASP.Net class, and used the ReportServer as a guide.  What never occurred to me was that I could use that meta-data on the ReportServer database for the reports themselves.

On a SQL 2005 box with SSRS, there is database called [ReportServer] that contains all kinds of meta information about the server and reports  A table called ReportServer.dbo.Catalog contains a column named "Modifieddate".  Instead of a version number, I can write a query to show the last time the report was modified (i.e. deployed).

First and foremost, your users need to have read access to the ReportServer database.  If they do not, you can't do this because your users will not be authorized.

In the report:
Setup new Dataset:
Name: dsReportInternals
SQL:
SELECT
top 1
[name],
[Path],
Max(Modifieddate) as [Modifieddate]

FROM
ReportServer.dbo.Catalog

WHERE
[Name]=@ReportName
and [Type]=2

GROUP BY
[name],
[Path]
;

If you might have the same report in two different locations on the server, you will want to use the Path column as your guide, add something like this in your WHERE clause:
[Path] = @ReportPath +'/'+ @ReportName

To avoid having too many report parameters, I setup this parameter inside the dataset:
@ReportName =Globals!ReportName

For the report parameters, I setup a new parameter like this:
Name: Global_LastModifiedDate
Internal: true
Available Values: Non-queried (i.e. nothing set)
Default Values:
From query = true
Dataset = dsReportInternals (from above)
Value field: ModifiedDate

Hopefully this will be helpful to someone.  It still has the issue that it's not a dead simple thing to setup for each report, but it's better than nothing. 

SSRS Multi-Select Parameters for Stored Procedures in SQL 2005

[Intro]

Using SSRS multi-value parameters with stored procedures can be a bit of a pain. Multi-Valued parameters are sent to Stored Procedures as a comma-delimited string. What follows is the method I use to write and debug stored procedures for SSRS reports.
The web is full of tutorials on handling multi-valued parameters in stored procedures. What I’m hoping to show is a method to set them up so that you can easily debug the stored procedure later on down the road.

[Background]

I found some code on SQLServerCentral.com that does a good job of splitting a comma-delimited string into a table of individual values. I wish I could credit the person who originally posted this idea, but there are lots of variations of this code floating around, and I modified the version I originally found, so please accept my apologies if this looks familiar.
This function will break down a comma-delimited string into a table of individual values. The one I’m posting here returns a table of ints, but I have others that return a table of other values (i.e. strings):

---------------------------------------------------- 
-- [dbo].[fnc_split_int] 
-- Parse a comma delimited string and insert the values into a table variable. 
-- Useful for sending a comma-delimited string parameter to a stored proc that will 
-- use it in a where IN clause.     
-- 
-- Ex: 
-- select * from customers     
-- will not work: Where customer_id in (@parameter)     
-- will work        : Where customer_id in (select item from fnc_split_string(@parameter,',')) 
--------------------------------------------------------- 
ALTER    FUNCTION [dbo].[fnc_split_int](
       @list varchar(8000),   
       @Delimiter VARCHAR(10) = ','
)
RETURNS @tablevalues TABLE (
       item int
)
AS
BEGIN

DECLARE @P_item varchar(255)
                                   
WHILE (DATALENGTH(@list) > 0)
BEGIN
   IF CHARINDEX(@Delimiter,@List) > 0
           BEGIN
           SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
               SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
           END
   ELSE
           BEGIN
               SELECT @p_Item = @List
       SELECT @List = NULL
           END

       INSERT INTO @tablevalues
   SELECT Item = convert(int, LTRIM(RTRIM(@p_Item)))
END

RETURN

END
       

Here’s a simple example of a stored procedure using this function:

create proc dbo.salesinfo (
       @customers varchar(max)
)
as
      
Select * from dbo.sales
where customer_id in ( select item from fnc_split_int(@customers, ',') )

[Using Table Variables]

The method above works well, but can get kind of annoying if you ever have to debug the stored procedure. The stored procedure could be huge, and you might not care too much about which customers are displayed. However it now requires a comma-delimited string of customers anytime you want to run some tests.
To keep me from losing my mind, I add a few table variables in my stored procedure that are designed to handle the multi-value SSRS parameters.


This is probably best explained in an example:

create proc rpt.salesinfo (
   @customers varchar(max) = null    --set to null by default 
)
as
      
--Build local customer table
declare @customerlist table (
   customer_id int
)

if @customers is null
begin
       --Load all customers (null) 
       insert into @customerlist
       select customer_id from customers
end
else
begin
       --Load only those customers chosen in @customers 
       insert into @customerlist
       select item from fnc_split_string(@customers, ',')
end


--report query (using @customerlist) 
select * from sales
where customer_id in (select customer_id from @customerlist)

Notes:

• It might seem a little excessive to build a local table of values when you already have those values in comma-delimited form. But using this method has saved me a lot of headaches when I’m trying to debug something in the stored procedure.


• I could use a join instead of putting the @customerlist table in the where string. However, I’ve found that performance hasn’t really been an issue for our setup. I also think it’s a little easier to read – a new developer doesn’t have to worry about finding a join that is only used to limit the resultset. From what I remember, the server does a lot to optimize queries anyway, so things like this usually do not matter as much.

[Final Thoughts]

This example shows a method I use to test and debug stored procedures for SSRS reports that use multi-valued parameters. There isn’t any ground breaking functionality introduced, just a bunch of smaller things strung together to hopefully make life easier. I should mention there are other ways of doing this, some might be better than this one. At the time of this writing I was only using SQL Server 2005.

Please feel free to ask questions or suggest improvements.

SSRS code examples

I use SQL Server 2005 Report Services (SSRS) quite a bit. I thought I'd share some code examples I keep handy.


First day of Month
To get the first day of the current month:
=CDate(Month(Today).ToString() & "/1/" & Year(Today).ToString())


Last day of Month
To get the last day of the current month (use first day of the next month and subtract one):
=DateAdd("d",-1,DateAdd("m",1,CDate(Month(Today).ToString() & "/1/" & Year(Today).ToString())))


Page number
Simple page count, easy to do but handy to have a copy of it around. I usually put this and the global execute time in the footer of the report:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages


Global Execution time
Good for displaying in the footer in case it gets printed - the person looking at the report then knows when the report was ran.
=Globals!ExecutionTime


Clean URL for a direct link to a report
If want to provide a direct link to a report most people usually end up copying it from the Address bar in their web browser. However, that address usually has a lot of extra characters such as %20. I've found an address that can provide a somewhat cleaner link. Use the example below (substitute your own servername and Report+Name.

Example of a easy to paste direct link to a report (notice no %20, etc).
http://servername/ReportServer/Pages/ReportViewer.aspx?/Reports/Report+Name


These are pretty simple examples, but I can't remember how many times I've thanked myself for keeping them around in a small text file.