An Automated Weekly History Report Email for #ActCRM Using PowerShell

I just want to apologise firstly that I haven’t been updating my blog for a little while and this could be my first post this month, but I’ve been in hospital for a little while and then on medication which has left me a little…incapacitated!

Today’s post brings in a couple of technologies and techniques together to produce an email that contains  a simple table showing the Histories created by all Users during the past week. This is actually a very simplified request that we received a little while ago from a client who was going away on holiday for a couple of weeks but still wanted to keep a tab on how their team were performing.

We’re going to use Microsoft’s PowerShell to run a SQL statement against the Act! SQL instance and then use a SMTP connection to the internal SMTP server (Exchange Server in our client’s case) to them email it out on a schedule set by nothing more exciting than the Windows Scheduler!

Now there are add-ons that will do this in a much more WYSIWYG and point-‘n’-click manner, but my view on this is; where’s the fun in that and isn’t it cheaper for the client to use the native skills of their Act! Consultant and existing free technology already present within their architecture?

Below is the script for doing this, I’ve added some comments to explain some of the parts and have tried to segment the scripts into blocks. In the current state, the script would need to be run on the server hosting the Act! dB and the scheduler to be running logged in as either a local server administrator or as a Domain administrator since it uses that to log into the SQL instance.

One word of caution though, if you’re not too comfortable with PowerShell or SQL, I would recommend that you don’t try this on your own and try to liaise with someone who is more comfortable with the technologies before using it on a production dB.

I’ve noticed that the image is a little small so here is the script itself that you could copy and paste if you’re feeling especially lazy!

clear #clears the screen of any other commands etc.
#-----------This is the PS connector to the native SQLCMD of SQL Server
if((Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null){
add-PSSnapin sqlserverprovidersnapin100
add-PSSnapin sqlservercmdletsnapin100
}
#--------------------------------------------------------------------------------------------

$dbInstance = ".\act7" #The machine hosting the Act! dB SQL Instance
$actDb = "Act2013Demo" #The Act! dB we want to report on

#----------------SQL Statement----------------------------------------------------------------
$myExp = invoke-Sqlcmd -ServerInstance $dbInstance -Database $actDb -Query "
            SELECT     
                    Count(TBL_HISTORY.HISTORYID)[Count], 
                    TBL_HISTORYTYPE.NAME AS [History Type], 
                    TBL_USER.USERLOGIN AS [User]
                FROM         
                    TBL_HISTORY INNER JOIN
                    TBL_HISTORYTYPE ON TBL_HISTORY.HISTORYTYPEID = TBL_HISTORYTYPE.HISTORYTYPEID INNER JOIN
                    TBL_USER ON TBL_HISTORY.CREATEUSERID = TBL_USER.USERID
                WHERE
                    TBL_HISTORY.CREATEDATE >= DATEADD(WW,DateDiff(WW,0,GetDate())-1,0)
                AND
                    TBL_HISTORY.CREATEDATE <= DATEADD(WW,DateDiff(WW,0,GetDate()),0)            
                GROUP BY
                    TBL_HISTORYTYPE.NAME, TBL_USER.USERLOGIN
            " | Select-Object Count, 'History Type', User
#-----------------------------------------------------------------------------------------------
#---------------Send out Email------------------------------------------------------------------
$emailFrom = 'actAdmin@caldere.com'
$emailTo = 'SalesManager@caldere.com'
$subject = "History Activity Report for week: {0}" -f((Get-Date -UFormat %V)-1)
Send-MailMessage -SmtpServer 'MyMailServer' -From $emailFrom -To $emailTo -Subject $subject -Body $myExp -BodyAsHtml
#------------------------------------------------------------------------------------------------

I’d be genuinely interested to read any comments anyone would like to make on this or if they can find ways to improve this. The original script created for the client was a little more complicated to include CSS styling and further SQL queries specific to their request.

ACT! Reporting, OLEDB & SQL

This is something we get asked to do for clients a lot…History Summary Reports. ACT!, has a built in report using its native reporting engine called the History Summary Classic Report. This report is a very cool report but it highlights one of the greatest weaknesses of ACT!, namely Reporting and BI. In short it’s terrible! Almost every client (if not all) of ours use customised Activities and to modify the History Summary Classic report means that we have to go into many sections of the report and do VBScript…all in all its nasty, ugly, cumbersome and very time intensive.

We actually stopped doing that a very long time ago and now we approach it in a different way.

• Do you want the report to be viewed within ACT! as a dashboard without any further interaction?
• Would you prefer to have the report viewable within Excel and interact with the report?
• Would you like it to be published using SQL Server Reporting Services (SSRS)?

Each of these requires the use of SQL to varying levels of complexity. The first can initially feel a little involved and so can be intimidating, but it’s fairly simple surprisingly. Sometime back, Allen Duet (the Product Management Director for ACT!) developed a free plugin that allowed us to drop in custom OLEDB reports into the ACT!, Dashboard. It surprised me on a couple of levels but firstly was that a Product Director from Sage actually knew what OLEDB was, secondly a Product Director could code (shock horror!) and thirdly why on earth hadn’t I thought about this! This free plugin has now been incorporated (and a little diminished truth be told from the original) into the core product.

**********EDIT**********

This post has been changed, originally I had gone into all the steps to do this with pictures etc. I’ve now made a YouTube video called “Create a History Summary Classic Report in Sage ACT! Within the Dashboard” that can be watched instead.

**********EDIT**********

Excel, Pivot Charts & OLEDB

I have to be honest and say that this one of my more preferred methods since it’s so simple and powerful for reporting! If you’re going to only report from a single table/view then you do not require any SQL input at all, it’s just a simple case of selecting your table and creating a pivot table/chart.

Since Excel 2003 pivot charting has become so easy anyone can do it. I remember that Pivot Tables used to be really complex things to create in earlier versions and in fact I used to avoid them like the plague! But now it’s all point-&-click and you get some very good looking and powerful reports as a result. You can also save these xlsx files and just click refresh later to get the most up-to-date data.

**********EDIT**********

This post has been changed, originally I had gone into all the steps to do this with pictures etc. I’ve now made a YouTube video that can be watched instead.

**********EDIT**********

SSRS & BI

Ok so this is unquestionably the most complex route taken but results in publicly shared and published reports that auto refresh themselves from live data.

I’ve found varying levels of performance of SSRS reports and the quality of the SQL you write can have a direct influence over the performance of these reports as well. To use SSRS you’ll need three components:

1. SQL data and queries
2. IIS (Webserver)
3. Microsoft BI Designer

There is also an optional 4^th component SharePoint. One of the very cool things with SSRS when you have SQL Standard or Enterprise Edition is that you can have these reports emailed out to your users on a schedule! Unfortunately this facility is not available in the EX edition which comes with ACT!, but if you publish the reports to SharePoint it is possible to get SharePoint to do this kind of thing I am led to believe.

The steps involved in creating an SSRS report really are too long and involved for me to create a simple video here and, besides, I have gotten too bored of this post now!