Usage Tracking Spreadsheet

How to run it

• Download the file: uses.rsl (right-click and select "Save link as..."). Save it somewhere appropriate on your system.
• Download the free ZedGraph library. The package you want is called "zedgraph dll only".
• The file you get will be called something like zedgraph_dll_v514_464.zip. This means that it contains two versions of the zedgraph library, 5.14 (for .NET 2.0) and 4.64 (for .NET 1.1). Resolver One is a .NET 2.0 application, so you should extract the file ZedGraph.dll from the appropriate subdirectory within the ZIP file (eg. from the subdirectory zedgraph_dll_v5.1.4) and place it in the same directory as the RSL file.
• Load the RSL file with Resolver One. You will see two worksheets, called "Raw Data" and "Chart". The first contains some numbers, the second (unsurprisingly) a chart.

What it's for

You may remember that when you you started Resolver One for the first time, it asked you if you were willing to allow us to track your usage of the software. This is because we want to know how many people are using it - and in particular, we would like to know how many people are using it regularly and how many just once. (We assume that a certain fairly constant percentage of people aren't willing to have their usage tracked; this is OK, as we care about the trends over time more than the absolute numbers.)

When people who are willing to have their usage sent to us start Resolver One, a message is sent to one of our servers. This data is all aggregated on a daily basis into a file, which we can download via a password-protected link. The file contains, for each day, the total number of times Resolver One was started, and the number of those that were "new users" - that is, people who'd never started the program before.

This example is the worksheet we use internally to visualise these numbers. The only difference is that the data it fetches is made up :-)

How it works

All of the interesting stuff in this file is in the User Code.

In the pre-constants user code, we load up the usage data from a Web server, using basic HTTP authentication to confirm with the server that we have the right to access it:

from System.IO import StreamReader
from System.Net import NetworkCredential, WebRequest

uri = 'http://www.resolversystems.com' +
      '/documentation/examples/usages/usages.csv'
request = WebRequest.Create(uri)
request.Credentials = NetworkCredential("demouser", "demopassword")

response = request.GetResponse()
try:
    reader = StreamReader(response.GetResponseStream())
    usageData = reader.ReadToEnd()
finally:
    response.Close()

Next, we split the usage data up into separate lines, and for each non-blank line we split it again into tab-separated values and put these into the "Raw data" sheet, converting them to numbers if we can.

for rowIndex, line in enumerate(usageData.split("\n")):
    if not line.strip():
        continue
    for colIndex, value in enumerate(line.split("\t")):
        try:
            value = float(value)
        except:
            pass
        workbook["Raw data"][colIndex + 1, rowIndex + 1] = value

These two steps load the basic data in from the Web server. By doing it here, before the constants are applied, we allow someone using the sheet to manually override the values by typing in adjusted values.

The next interesting bit is in the pre-formulae user code. In here, we run through all of the non-header rows in the raw data, and fill in a new "Existing user runs" column with the difference between the total number of runs and the runs by new users. Note the user of headers to make the formula easier to understand.

for row in workbook["Raw data"].Rows:
    if row is workbook["Raw data"].HeaderRow:
        continue
    row["Existing user runs"] = row["All runs"] - row["New user runs"]

We do this in this user code section so that it can take account of any values that were manually adjusted.

The final bit of code, in the post-formulae user code section, is the code to draw the graph. Firstly, it loads the ZedGraph assembly:

import clr
clr.AddReference("ZedGraph")

Next, it imports various things it will use later:

from System import Array, Math, String
from System.Drawing import Bitmap, Graphics, RectangleF
from ZedGraph import AxisType, BarType, GraphPane, PointPairList, SymbolType

It creates a GraphPane, which will do the graphing, with an approropriate size, title, and X and Y axis labels:

chart = GraphPane(
    RectangleF(0, 0, 640, 480),
    "Resolver One Daily Usage",
    "Date",
    "Uses" )

It generates a list of dates for the X axis, and two lists of values to make up the two halves of each bar:

dates = []
existingRunsList = PointPairList()
newRunsList = PointPairList()
for row in workbook["Raw data"].Rows:
    if row is workbook["Raw data"].HeaderRow:
        continue
    dates.append(row["Date"])
    existingRunsList.Add(row.Index, row["Existing user runs"])
    newRunsList.Add(row.Index, row["New user runs"])

It passes the values, along with a couple of other configuration parameters, to the GraphPane.

chart.AddBar("Existing runs", existingRunsList, Color.Blue)
chart.AddBar("New runs", newRunsList, Color.Red)
chart.XAxis.Type = AxisType.Text
chart.XAxis.Scale.TextLabels = Array[String](dates)
chart.BarSettings.Type = BarType.Stack
g = Graphics.FromImage(Bitmap(1, 1))
chart.AxisChange(g)

And finally, it gets the image of the graph from the GraphPane, and creates an image worksheet to display it.

That's it!