1.877.833.1202

Emailed dashboards when you need quick summary

Friday, April 24, 2020

It goes without saying that a lot of us are working from home these days. For many, you are working a lot more as your business sees a shift to an economy where personal safety, cleanliness, eating at home, etc. have taken control. It may not be as easy or you may not have as much time to monitor your operations from your normal document management system reports.

Your SQL Server includes some technology that could help you with a little configuration. The key idea is to get a snapshot of your operations to your email inbox on your phone or at home so you can keep abreast important metrics in your business without a lot of overhead.

Your FileHold system contains a great deal of information about your operations that can be leveraged including your custom metadata and document schemas. It also has some data which is equally available to all customers and useful to getting an understanding of your business activity. For example, the top ten document activities your users are doing in FileHold, the current status of your workflows or the top 10 users with immediately due or overdue workflows. The best part is your SQL server can automatically email this information to you.

For the remainder of this article, you will need to put your technical hat on. This stuff is not hard if you know a bit of SQL and a bit of HTML, but as always backup your data and test on your test server. You have been warned. On the off chance you cannot find your technical hat, maybe the dog hid it or maybe you can just give our professional services team a call to help.

There are three main SQL Server technologies involved to make this work.

  • Database Mail,
  • The sp_send_dbmail stored procedure and
  • The SQL Server Agent for running jobs on a schedule

With this technology in place, we just need some queries that return useful information related to your documents in FileHold. A key table in FileHold is the UsageLog in the library manager database. This table contains all the touch points between users and documents and the following query will give the top 10 activities from the last week.

SELECT TOP 10 Choose(actionid + 1, 
              'Checked out', 'Downloaded', 'Edit metadata', 
              'Checked in', 'Checked in, no changes', 'Added', 
              'Scheduled for deletion', 'Linked', 'Moved',
              'Copied', 'Emailed', 'Document recovered', 
              'Permanently deleted', 'Transferred from', 
              'Transferred to', 'Archived', 'Created by copy',
              'Workflow initiated', 'Workflow cancelled', 
              'Viewed', 'Printed', 'Published', 'Owner changed',
              'Convert to electronic document', 'Convert to record',
              'Convert to offline', 'Unliked',
              'WF, removed as main', 'WF, added as supporting', 
              'WF, removed as supporting', 'Notification', 
              'Courier transmission initiated', 
              'Courier transmission cancelled', 
              'OCR' + Char(39) + 'd', 'Build document', 
              'Workflow approved', 'Workflow reviewed', 'Workflow not approved', 
              'Workflow approval postponed', 'Markup saved') Metric, 
              Count(*)                                       Count 
FROM   [ch_librarymanager].[dbo].[usagelog] 
WHERE  Datediff(week, actiondate, Getdate()) <= 1 
GROUP  BY actionid 
ORDER  BY count DESC 

Here is an example of the output with a little formatting applied. As always, the descriptions behind the id codes in the database columns can be resolved in our API Reference Guide such as the ActionType enumeration used here.

Metric

Count

Downloaded

7623

WF, added as supporting

3420

Added

3356

Edit metadata

1276

Workflow initiated

1184

Checked in

833

Checked out

796

Workflow approved

719

Convert to record

280

Workflow cancelled

147

 

Another area of regular interest for managers is the status of the workflows that drive their business. Workflows are kept in another library manager table, quite unexpectedly called Workflows. Key fields are DateCreated, DateCompleted, DueDate, ApprovalStatus and WorkflowStatus. As with the action id, the status code meanings are in the API reference guide.

The following query provides a nice overview.

 

SELECT 'Workflows not approved last week' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where datediff( week, getdate(), DueDate ) = -1
       and ApprovalStatus = 3
union all
SELECT 'Workflows approved last week' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where datediff( week, getdate(), DateCompleted ) = -1
       and ApprovalStatus = 2
union all

SELECT 'Workflows due this week' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where datediff( week, getdate(), DueDate ) = 1
       and WorkflowStatus = 0
union all

SELECT 'Workflows initated last week' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where datediff( week, getdate(), DateCreated ) = -1
       and WorkflowStatus = 0
union all

SELECT 'Workflows postponed' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where DueDate < getdate()
       and ApprovalStatus = 5
union all

SELECT 'Workflows overdue' as Metric,
       count(*) as Count
  FROM [ch_librarymanager].[dbo].[Workflows]
where DueDate < getdate()
       and WorkflowStatus = 0
A cleaned-up example might look like this.

Metric

Count

Workflows not approved last week

6

Workflows approved last week

305

Workflows due this week

152

Workflows initiated last week

217

Workflows postponed

86

Workflows overdue

732

Finally, we can drill further into our workflow activity by looking at the top 10 users that may have challenges to complete their tasks with the following query.

Since we are looking at real people, we should probably get real people names from the AdamObjects table in the library manager database. This is an easy-to-get-to copy of some important data from the user role manager database and where we will get peoples’ names from.

In this case, we still want information from the workflows table, but our key data will come from WorkflowTasks. Each totaled column in our result table will be a subquery using the TaskStatus column to make sure the task is a work in progress. We will use the CompletedStepsCount from the workflows table along with the ActivityDescriptor from the workflow tasks to know that this is the current task. The activity descriptor increases by one for each activity in the workflow template and the “steps” in the completed steps count plus one is the next set of tasks to complete.

As a reminder, for each activity there is one or more tasks depending on how many users are assigned to the activity. And, for some activities there may be many users assigned but only one must complete the task.

SELECT TOP 10 ao.objectname                             'User', 
              COALESCE(OverDue.tasksoverdue, 0)         Overdue, 
              COALESCE(DueThisWeek.tasksduethisweek, 0) DueThisWeek, 
              AllDue.tasksoverdueorduethisweek 
FROM   (SELECT wt.userid, 
               Count(*) AS TasksOverdueOrDueThisWeek 
        FROM   [ch_librarymanager].[dbo].[workflowtasks] wt 
               INNER JOIN workflows w 
                       ON w.workflowinstanceguid = wt.workflowinstanceguid 
        WHERE  taskstatus IN ( 0, 6 ) 
               AND w.completedstepscount + 1 = activitydescriptor 
               AND wt.userid <> 0 
               AND ( Datediff(week, Getdate(), wt.duedate) = 1 
                      OR wt.duedate < Getdate() ) 
        GROUP  BY wt.userid) AllDue 
       LEFT JOIN (SELECT wt2.userid, 
                         Count(*) AS TasksDueThisWeek 
                  FROM   workflowtasks wt2 
                         INNER JOIN workflows w2 
                                 ON w2.workflowinstanceguid = 
                                    wt2.workflowinstanceguid 
                  WHERE  taskstatus IN ( 0, 6 ) 
                         AND w2.completedstepscount + 1 = activitydescriptor 
                         AND wt2.userid <> 0 
                         AND Datediff(week, Getdate(), wt2.duedate) = 1 
                  GROUP  BY wt2.userid) DueThisWeek 
              ON DueThisWeek.userid = AllDue.userid 
       LEFT JOIN (SELECT wt3.userid, 
                         Count(*) AS TasksOverdue 
                  FROM   workflowtasks wt3 
                         INNER JOIN workflows w3 
                                 ON w3.workflowinstanceguid = 
                                    wt3.workflowinstanceguid 
                  WHERE  taskstatus IN ( 0, 6 ) 
                         AND w3.completedstepscount + 1 = activitydescriptor 
                         AND wt3.userid <> 0 
                         AND wt3.duedate < Getdate() 
                  GROUP  BY wt3.userid) Overdue 
              ON Overdue.userid = AllDue.userid 
       INNER JOIN adamobjects ao 
               ON ao.objectid = AllDue.userid 
ORDER  BY tasksoverdueorduethisweek DESC 

Prettied up a bit, an example might look something like the following.

User

Overdue

Due this week

Totals

Lisa Crispin

285

28

313

Cem Kaner

0

36

36

Bret Pettichord

21

14

35

Mark Fewster

0

32

32

Gerald Weinberg

4

18

22

Elfriede Dusting

0

21

21

Boris Beizer

7

11

18

Hung Nguyen

3

12

15

Jeff Offutt

0

13

13

Dorothy Graham

0

13

13

We have some useful queries, so now we need to setup the technology. The first piece is database mail. There is a nice article at https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/. Basically, you setup a profile, one or more email accounts and configure some system parameters. You will use the profile name later with the stored procedure so make it one that makes sense. The wizard is a bit more complicated than needed for most cases, but the hard part here will likely be finding or correctly communicating with an SMTP server. You might need to enable SMTP in your email host if that has not already been done.

The big chunk of work that needs to be done is to incorporate your useful queries into an email message that will be sent using the system stored procedure sp_send_dbmail. If you type this name into an internet search you will see a comprehensive Microsoft KB article on the parameters needed to use this. In our example we will just be formatting the body of the message, so you can ignore the parameters related to attachment.

Most of the parameters have obvious meanings well known from the general email world. The @profile_name is important as that will need to line up with the profile you created in Database Mail. The important one we will break down here is @body. This is where we will turn our queries into a nicely formatted HTML document using the FOR XML PATH feature of T-SQL.

The goal is to wind up with something like this where we prepare the @htmlBody email in advance:

exec msdb.dbo.sp_send_dbmail
        @profile_name = 'Scheduled reports'
       ,@recipients = '[email protected]'
       ,@from_address = '[email protected]'
       ,@subject = 'My dashboard'
       ,@body = @htmlBody
       ,@body_format = 'HTML'

For the html body we want to package a combination of static and dynamically generated HTML. The static bit will help make it look nice and the dynamic bit will be the meat for our dashboard. As you develop this you will want to use your favorite HTML editor (or notepad and a browser if you must) because the round trip between T-SQL and your inbox is a little long for HTML tweaks.

The following HTML will give us a nice heading for the dashboard and override the default styles for the tables we will create in a minute.

<style>
BODY { font-family: Verdana, Geneva, sans-serif;}
TABLE {padding: 2px;font-size: 12px}
TH {text-align: left;padding: 3px;background-color: #4F81BD;color: #FFFFFF}
TR:Nth-Child(Even) {Background-Color: #DCE6F1;}
TR:Nth-Child(Odd) {Background-Color: #B8CCE47;}
TR:Hover TD {Background-Color:rgb(139,202,121);}
</style>
<H1 style="font-size: 24px;color: #606060">My Dashboard</H1>

We will place our data results into three tables. For simplicity and control we will encapsulate the from SQL rows in the necessary tags using some static tags. Something like this:

<table><tr><th>Metric</th><th>Count</th></tr>
… put the data here …
</table>

We will add an elements name in FOR XML PATH to set our <tr> row tags instead of the default <row> tags and we will use some trickery to create our <td> tags where the blank columns serve to split our real columns into separate <td> tags. We can avoid messing up our original query by making it a subquery of our formatting query.

SELECT
        td = [Metric]
       ,''
       ,td = [Count]
       ,''
FROM
( … put the main query in here … )
FOR XML PATH('tr')

As an added bonus we can add our logo to the end with an IMG tag like <img src=”https://www.filehold.com/sites/default/files/FileHold_logo_427.png”>

When we stick together all the parts from above the resulting code is a bit long for the article, so I have attached it as a download-able link at the end.

The job is the last step and it is quite simple. Create a new job in the SQL server agent section of the SQL management application. You will need to give some basic details about the job, like the name. Then at the steps (there is only one), you can simply open the file below or copy and paste its contents. Finally, set a schedule when you want it to run. There are many more options, but that is enough for the basics. You can have a look here https://www.c-sharpcorner.com/UploadFile/raj1979/create-and-schedule-a-job-in-sql-server-2008/ if you want some step by step instructions.

The result as it arrives in your inbox can be seen at the top of the article. The rendering of table is a little dependent on how the CSS is interpreted by your mail viewer.

Blog-20200424-Dashboard-Example.sql

Russ Beinder portrait Russ Beinder is the Chief Technology Officer at FileHold. He is an entrepreneur, a seasoned business analyst, computer technologist and a certified Project Management Professional (PMP). For over 30 years he has used computer technology to help organizations solve business problems.