Charting case trends
Recently I was looking to put a report together that would track the total number of cases that are open on a daily basis, and graph that as a trend.
This is fairly tricky to do with baseline data. It would be easier if I had a query that ran every night and simply recorded the total number of open cases at that point in time. All I need is the count, not the actual data. Then I would have a set of records that I could build a report with.
My first thought was to create a simple script that would run the query, then set this up as a Scheduled Task to run nightly. Then I could store the results somewhere in the database.
Then I realized that this all sounded familiar – this is Trend Tracker.
Trends and Trend Tracker
From the Clarify Docs:
Queries give a snapshot of your database; trends show query information over time, so that you can determine changes and detect tendencies. For example, perhaps every day you run a query that lists the cases in a particular queue, and you’d also like a graph showing the number of cases in the queue. Create a trend which will run that query every day at a specified time. The trend runs the query and writes a timestamp and the number of rows retrieved by the query to the trend file.
Trend Tracker is a program that is invoked by Rule Manager to gather statistical information from your database, and store results in the trend tables so users can study data trends. When a trend is started on the client system, a recurring time bomb is created in the database. Rule Manager has a handle for the trend object, and executes Trend Tracker at the specified interval. Trend Tracker executes the query and stores the result in a specific object. You do not need to start any processes for Trend Tracker. It is invoked automatically by Rule Manager when a time bomb for a trend (set by the trend date or frequency) expires.
Lets see how to set this up and make it all work.
Create your query
First, within the Clarify Client, setup the query that you want to be executed. In my scenario, I just wanted a count of the open cases, so my case query was simple:
Query for all cases where the condition starts with Open. Save this query with a title of open support cases.
Create your trend
Next, create your trend. A trend is simply a name, one or more queries to be executed, a start date, and a frequency.
Here I’m telling it to run this trend starting on 3/14/2014 at 11:59 PM, and to run it again every 1 day (24 hours).
then add one or more queries to your trend:
You can actually add up to 12 queries to one trend.
Once your trend is setup, and has at least one query attached to it, click the Start Trend button.
That’s it. Now your trend is setup and ready to go.
Query Results
The results of the query are stored in table_trnd_rslt. It simply stores the count of results for each query. If you only have one query in your trend, it will be in the column rslt_query_1.
A simple SQL query will show you the data:
select objid, time_stamp, rslt_query_1 from table_trnd_rslt
From within the Clarify Client, you can click the Display Graph button on the Trend, and it would actually open the raw data result set in Excel. It’s then up to you to turn that into a graph/chart.
Report Chart
Now that we have the data, we can use our favorite reporting tool to query for the data and chart it.
We’ll chart the number of open cases (rslt_query_1 column) vs time (short date format of the time_stamp column).
That chart could be part of your Dashboard, allowing you to keep an eye on any drastic changes in open cases.
I’m not going into detail here about the reporting/charting tool, as it seems like almost everyone has a different tool that they use.
The point of this post is more how to gather the data, then you can use your favorite tool to query for the data and chart it.
Clarify Trend Tracker and Dovetail Rulemanager
As explained above, when it’s time for a trend to execute its queries, a time_bomb expires, which is picked up by Rulemanager. Rulemanager then calls the Trend Tracker executable (tndtrk.exe).
Even though we’re using Clarify Client and Trend Tracker, we can still use Dovetail Rulemanager. All we have to do is tell Rulemanager where to look for the trend tracker executable. This is done in the FChoice.RuleManager.WindowsService.exe.config file:
<!– Directory where Clarify TrendTracker application resides –>
<add key=”TrendTrackerActionConfig.WorkingDirectory” value=”C:\Clarify\9\Server\RuleMgr” />
Trend Tracker – really?
Isn’t this old-skool shiz? Aren’t we in 2014 now?
Yep. It’s old school. But you know what – it works. And it allowed me to get what I needed to get done quickly and easily.
Within a couple minutes, I had a query and trend setup and going. I waited for a few days for it to collect at least a few days worth of data, then I added the simple report chart.
Just ‘cause it’s old doesn’t mean that you can’t still reap value out of the tools that you have on hand.
Go forth and wow your boss
Pointy-hair bosses love shiny graphs and charts. So use the tools you have at your disposal, and go forth and wow them!
Above chart from http://www.marktaw.com/blog/PointyHairedBoss.html
Rock on.
