Using Taskcentre to Post HTML Tables to HipChat

HTML Email ExampleI use Taskcentre from Orbis Software quite a lot to send HTML emails to recipients. These usually are bog standard HTML tables with content. For example, this is a sample report based on Microsoft Dynamics Nav database. This is not the standard out of the box Cronus database, but the extended version. The standard database has very little data (about 600k rows of data), whereas the extended has closer to 2.5 million!

Email has it’s uses, however does have it’s disadvantages, such as:

  • E-Mail Ping Pong – Emails going backwards and forwards, but not getting anywhere
  • Users not replying to latest email – 20 minutes into an email conversation, someone else joins in using an earlier email
  • Too detailed – Sometimes people email a 20 line email, where a single sentence will do

More recently I’ve been trialling HipChat as an alternative to E-Mail. In a previous blog post, I covered posting error messages from Taskcentre and posting in Hipchat – Taskcentre Alert Notifications To HipChat.

Extracting Data From Nav
In order to extract information from Nav, first I need to use either ODBC or OLEDB to return the data. I prefer OLEDB as I’ve seen performance benefits over ODBC, but either is suffice.

Here’s the SQL query as generated by Taskcentre:

SELECT
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name AS Name,
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" AS "Salesperson Code",
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)") AS Sales,
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)"-(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Quantity"*dbo."CRONUS EXT UK Ltd_$Sales Line"."Unit Cost (LCY)")) AS Profit
FROM
	dbo."CRONUS EXT UK Ltd_$Sales Line",
	dbo."CRONUS EXT UK Ltd_$Sales Header",
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser"
WHERE
	(dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = 1) AND (dbo."CRONUS EXT UK Ltd_$Sales Header".No_ = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document No_" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document Type" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" = dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Code)
GROUP BY
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code",dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name

Building HTML for HipChat
VBScript Creating Table RowsNext we need to build the HTML for HipChat. Unfortunately the “Format as Text” tool will not work in this situation (it does not allow outputting of the created text), so we need to use VBScipt instead.

Firstly I’ve created a variable within the task called “TempHTML”. This variable will be used to store the created HTML before posting to HipChat. Then the VBScript step is created which is set to repeat for every row in the returned record set. This is done by checking the box accordingly as per the screenshot.

For clarity, here’s the code used:

' ProcessData Function
' 
Function Create_Table_Rows_Function()
Variables("TempHTML") = Variables("TempHTML") + "<TR>"
Variables("TempHTML") = Variables("TempHTML") + "<TD><B>" + ThisStep.RecordSource("Name") + "</B></TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Sales"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Profit"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "</TR>"
End Function

For those who have HTML skills, you will notice that this does not include any <TABLE> tags, nor column headers. In order to create these, a second VBScript step is created which finishes these off:

' ProcessData Function
' 
Function Create_Headers_Function()
Variables("TempHTML") = "<B>Sales Statistics:</B><BR/><TABLE BORDER=1><TR><TD><B>Salesperson</B></TD><TD><B>Sales</B></TD><TD><B>Profit</B></TD></TR>" + Variables("TempHTML")
Variables("TempHTML") = Variables("TempHTML") + "</TABLE>"
End Function

Posting to HipChat
Posting this to HipChat is very simple, using the HipChat Send Room Notification web service. I’m not going to cover it in this post as it was covered previously, simply use the variable inside the XML Web Service Tool.

Complete Task
Here’s the complete task (I’ve still left in the HTML email example, which is outside of the scope of this blog post):
Sales Order Totals With HipChat

When the task runs, here’s the posting to HipChat:
HipChat HTML Message - Table

Borders
I’ve tried using CSS Table Formatting and even the HTML Table Border Attribute, however neither of these work, so I assume HipChat does not support borders (it does state that only basic HTML is supported and anything unsupported is stripped out). Possibly in future HipChat may support these.

2 thoughts on “Using Taskcentre to Post HTML Tables to HipChat

  1. JRevell Post author

    I agree fully. But there is the technicalities of supporting multiple clients and browsers which could get messy.

    The API says it supports “basic tags: a, b, i, strong, em, br, img, pre, code, lists, tables.”

    Looking at the API, it’s been extended since I wrote this initial article, so will at some point revisit this article and update.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.