Getting Data to the Hand ( helds ) of your Executives

Creating content for mobile devices is an exciting prospect, but it often requires either having an external website that users can access, or developing specifically for the operating system of the device you are targetting. If you don't have the time or the inclincation, another option is to send a nicely formatted email. All mobile devices have email capabiliity, and most executives are often more interested in metrics over flash ( individual results may vary! ).

In my case, I had several executives with Blackberry's that wanted to receive metrics on their phones. While there is a Blackberry development kit, we did not have the time to learn it, so a simpler solution was to email them with the information that they wanted. The information was metrics out of sales and quoting systems, and was stored in a SQL Server 2000 database.

I had sent emails with information before using xp_sendmail, and used string manipulation to line up the numbers. While it got the information across, the formatting was tedious, and didn't really come across like a report; it was more of a data dump than a dashboard. Another wrinkle was that my manager wanted to be able to show this year and last year metrics side by side with the proper headings above them. I quickly realized that my old way of formatting the email using padded varchars was not going to be flexible enough to provide the layout we wanted.

To solve the problem, I created a solution consisting of a table to hold my desired results, a procedure that would allow me to build a formatted table in memory, and then build up the string value of it in a predictable pattern to return. Coupled that with a new fuction that would center a value, and I had myself a primitve xp_sendmail report writer!

The following articles walks you through setting up the components needed to get a "pretty" email output.

1. Create table to hold the report values

2. Create stored procedures that gets the metrics that you want.

3. Create the procedure that loads the report table with your metrics.

4. Create the procedure that returns a formatted report.

5. Create the procedure that rebuilds the report table and emails the formatted report.

"Pretty" Output

Here is what I wanted my output to look like:

Step 1: Create table to hold the report values

To accomplish this, I first created a table to store the metric values that I would want to present. The table was very simple: a key describing what the row contained, a full value, and a value in millions, as this was the format that I wanted to present in. Because I would be loading everything into a string eventually, I set all my columns to by varchar.

Here is the SQL to create the table:

CREATE TABLE [dbo].[report_values](
 [report_field] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [report_value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [report_in_mil] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Step 2. Create stored procedures that gets the metrics that you want.

One unique point is that while your metrics will often be a number, in this case I am storing them as a varchar, and so I return varchar from my procedures that build up my metrics.

 /* Step 2. Create stored procedures that gets the metrics that you want. */
Create procedure [dbo].[up_report_display_value_metric_one]
@ai_current_period numeric ,
 @as_year varchar(4),
 @as_metric_one_string varchar(20) output
as

declare @as_metric_one_money money

/* Load your own select here; this is a dummy one just to get some data back */
set @as_metric_one_money = 1000000
set @as_metric_one_string = Convert( varchar(20), @as_metric_one_money, 1)

If @as_metric_one_string = ''
Begin
 set @as_metric_one_string = '0'
End

One really important thing to note is that I use the datatype of money instead of numeric. This allows me to convert it to a varchar and perserve the commas and decimals in the string, which makes the number look really great in the output. Compare 12345678 to 12,345,678.00 and you can see what a difference the commas make.

I create one of these procedures for each metric that I want to include in my report.

Step 3: Create the procedure that loads the report table with your metrics.

To load the values, I created specific stored procedures that would load up exactly what I wanted, in this case called up_report_display_value_metric_one, with @as_invoiced_string as the output that I wanted to return.

I created a seperate up_report_display_value_[ metric name ] for each metric that I would want to store.

 /* Step 3: Create the procedure that loads the report table with your metrics. */
Create PROCEDURE [dbo].[up_report_load]
as
-- Clear the report file
delete report_values

-- Setup variables
declare @as_this_year varchar(4)
declare @as_last_year varchar(4)
declare @ai_month numeric

declare @as_metric_one_string varchar(20)

-- Start setting month and date values
set @ai_month = Month( GetDate() )
set @as_this_year = Cast( Year( GetDate() ) as varchar(4) )
set @as_last_year = Cast( ( Year( GetDate() ) - 1 ) as varchar(4) )

/* Metric One Section -- repeat this section for each stored procedure that loads a metric for you */

-- Metric One this year
exec up_report_display_value_metric_one @ai_month, @as_this_year , @as_metric_one_string output 

-- Load the value to the report row
insert into report_values( report_field , report_value )
values ( 'metric_one_this_year', @as_metric_one_string )

-- Metric One last year
exec up_report_display_value_metric_one @ai_month, @as_last_year , @as_metric_one_string output 

-- Load the value to the report row
insert into report_values( report_field , report_value )
values ( 'metric_one_last_year', @as_metric_one_string )

-- Set the report_in_mil column
/* Note - If you do not want to display your data in millions, 
just copy the report_value straight over to report_in_mil */
-- I want in millions
update report_values
set report_in_mil = Convert( varchar(20), ( Cast( report_value as money )/1000000 ), 1) 
-- I want the real number
--update report_values
--set report_in_mil = report_value

In my case each metric was very different from each other, so having a specific stored procedure to load it allowed me to compartmentalize my logic. Also note that if you want to display the real number instead of the in millions number, uncomment the lines at the bottom.

Step 4: Create the procedure that returns a formatted report.

To get the output for the email, I had a stored procedure that would create the output in a format that would be exactly what I wanted to display. It would return the output as a select statement so that it could be put directly into an email from xp_sendmail.

 /* Step 4: Create the procedure that returns a formatted report. */
Create Procedure up_build_report_output
as
declare @report table(
report_line varchar(30)
)
declare @left_value varchar(10)
declare @middle_value varchar(10)
declare @right_value varchar(10)
declare @as_this_year varchar(4)
declare @as_last_year varchar(4)
declare @as_month_display varchar(12)
declare @ai_month numeric
set @as_this_year = Cast( Year( GetDate() ) as varchar(4) )
set @as_last_year = Cast( ( Year( GetDate() ) - 1 ) as varchar(4) )
set @ai_month = Month( GetDate() )
set @as_month_display = Case @ai_month
 when 1 then 'Jan'
 when 2 then 'Feb'
 when 3 then 'Mar'
 when 4 then 'Apr'
 when 5 then 'May'
 when 6 then 'Jun'
 when 7 then 'Jul'
 when 8 then 'Aug'
 when 9 then 'Sep'
 when 10 then 'Oct'
 when 11 then 'Nov'
 when 12 then 'Dec'
 End
-- Metric 1 Header
set @left_value = [dbo].[uf_center_string]( '<sl>' )
set @middle_value = [dbo].[uf_center_string]( 'Metric 1' )
set @right_value = [dbo].[uf_center_string]( '<sl>' )

Insert into @report( report_line )
values( @left_value + @middle_value + @right_value )

-- Metric 1 Column Header
set @left_value = [dbo].[uf_center_string]( @as_month_display + ',' + @as_this_year )
set @middle_value = [dbo].[uf_center_string]( '' )
set @right_value = [dbo].[uf_center_string]( @as_month_display + ',' + @as_last_year )

Insert into @report( report_line )
values( @left_value + @middle_value + @right_value )

-- Metric 1 Values
set @left_value = [dbo].[uf_center_string]( ( select report_in_mil from report_values where report_field = 'metric_one_this_year' ) )
set @middle_value = [dbo].[uf_center_string]('')
set @right_value = [dbo].[uf_center_string]( ( select report_in_mil from report_values where report_field = 'metric_one_last_year' ) )

Insert into @report( report_line )
values( @left_value + @middle_value + @right_value )

-- Blank Row
set @left_value = [dbo].[uf_center_string]( '<dl>' )
set @middle_value = [dbo].[uf_center_string]( '<dl>' )
set @right_value = [dbo].[uf_center_string]( '<dl>' )
Insert into @report( report_line )
values( @left_value + @middle_value + @right_value )
-- ... COPY ABOVE FOR METRIC 2 and METRIC 3 ...

-- Return the formatted email
select report_line from @report

The procedure creates a table variable called @report with report_line. This table will act like a grid, holding the output together for me. I then set up some variables to handle displaying the date information nicely. Then we get down to business and start loading the @report table one row at a time.

Think of loading this as you would writing out a report one line at a time. The first line is the heading, so I pass a token of <sl> to my centering function which will return a single centered line of six dashes, like this:

------

I set the middle column to my metric heading, in this case "METRIC ONE", and then set the right column to be another set of dashes using the <sl> token.

The next line will be the headings for the individual numbers, this year and last year. I set the left column by passing the date display and year to my centering function. The center column should be blank, so I pass an empty string to the centering function. The right column in the heading for last year, so I pass the date and last year to the centering function.

Next will be the actual values, which I can get for the left column by using a sub select to my report table, getting the value for 'mertic_one_this_year' that I populated earlier. The center column is blank, and the right column is a sub select for the value 'metric_one_last_year'.

The final row of this section is just a break row filled with equal signs, using the <dl> token to the centering function.

Finally I select the report_line from the @report table, which returns the nicely formatted table.

Bonus Function: Centered Content

The funtion that will do the centrering is:

CREATE FUNCTION [dbo].[uf_center_string](@str varchar(10)) RETURNS varchar(10) AS
 BEGIN
 declare @length numeric
 declare @remainder numeric
 declare @output varchar(10)
 --
 -- Determine special cases
 If @str = '<sl>'
 Begin
 set @output = '----------'
 return @output
 End
 --
 --
 If @str = '<dl>'
 Begin
 set @output = '=========='
 return @output
 End
 --
 --Do the centering
 --
 set @length = Len( @str )
 set @remainder = ( 10 - @length ) / 2
 set @output = space( @remainder ) + @str + space ( @remainder )
 return @output
 END

You could create any number of special tokens by extending out the If logic of the @str that was passed in, but these were enough to get me a single line and the double line.

Step 5: Create the procedure that rebuilds the report table and emails the formatted report.

Now that all of the other pieces are in place, you are ready for the procedure that actually sends out the exec pretty report.

/* Step 5: Create the procedure that rebuilds the report table
 and emails the formatted report. */
Create procedure [dbo].[up_report_send_email]
as
--
-- Build the report table
exec up_report_load

-- Use xp_sendmail to send the nice report

/* To Customize:
1. Change the @recipients to who you want to get the report
2. Change the @query = 'exec devel. to whatever database you have loaded this into
*/

Exec master.dbo.xp_sendmail 
 @recipients = 'CEOandOtherExecs@MyCompany.com',
 @message = '',
 @subject = 'Nicely Formatted Metrics',
 @query = 'exec devel.dbo.up_build_report_output',
 @no_header = TRUE

One important thing to note is that the @no_header flag is set to TRUE; this will keep the column name from my report table from appearing at the top of the email and spoiling the nice format.

Conclusion

My "pretty" email was well received, and several new metrics were immediately requested to be added, which was relatively easy to do.

This whole process is wide open for customization; for example, I locked each of my columns at 10 characters wide, and a grid of n rows but only three columns. You could add columns, change the size of the columns, and because the centering function can return blank space to pad your empty cells, create as many creative layouts as imagination and screen real estate will allow.