This is the final installment of the postings on the sample application. Up to this point we have dealt with the process of getting information about profiles and report definitions. We have yet to get any real report data. So, this post will deal with that process and what we do with the report results once we get them.
The first step in the process is, of course that of creating a REST URL that requests report data. The application takes care of all of this based on the selections made by the user for such things as time periods to cover, search criteria, limiting rows, how to deal with totals, and the selection of specific measures. There is a fair amount of work involved in making sure everything is valid, but the end result is a REST URL that looks something the one shown below.
https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/95df19b6d9f2/?totals=all&period=2009m01d16&format=html&suppress_error_codes=true
The major difference between this URL and those we have seen in the past is that there are multiple parameters specified which control what and how much data we get back. A second difference here is that we are also using a different data type for our results. In this case we have chosen the “html” format rather than JSON. When getting report results for Excel the “html” format makes it extremely easy to populate the result data into Excel. The result data is returned as a table definition which when pasted into a spreadsheet automatically places our data into rows and columns in a tight report fashion. In fact the result data from the REST URL above produces the results shown below.
<table border=1>
<tr>
<th>Time</th>
<th>Browsers</th>
<th>Views</th>
<th>Visits</th>
</tr>
<tr>
<td>1/16/2009</td>
<td> </td>
<td>83752</td>
<td>8941</td>
</tr>
<tr>
<td>1/16/2009</td>
<td>Microsoft Internet Explorer</td>
<td>48488</td>
<td>5193</td>
</tr>
<tr>
<td>1/16/2009</td>
<td>Mozilla</td>
<td>13547</td>
<td>1445</td>
</tr>
<tr>
<td>1/16/2009</td>
<td>Netscape</td>
<td>5942</td>
<td>636</td>
</tr>
</table>
If you look closely at this result, you will see that we have a single table with a border (width of 1 pixel). The table is divided into several table rows. Each table row has a number of columns. These hold the dimension and measure data for the report. Also important to note are the first two rows in the table data. The first row holds the names of the report columns. In every report I have seen the first column has always been time. The second row holds the “total” data. Even when a report has no totals this row of data will be present and will contain the date value. The remaining rows contain the report detail as specified in the REST URL request.
It is these two top rows that may need some special handling. Suppose I have a series of queries that return report data for individual periods, say one week’s worth of data but one day at a time. What I really want to get to here is a single report that shows each result for each day, but I only want one set of header data and I don’t want any totals since I can create those myself. If I were to paste in the results from two successive days without making any changes to the results, I would get a spreadsheet that looks like the following.
Time | Browsers | Views | Visits |
1/16/2009 | | 83752 | 8941 |
1/16/2009 | Microsoft Internet Explorer | 48488 | 5193 |
1/16/2009 | Mozilla | 13547 | 1445 |
1/16/2009 | Netscape | 5942 | 636 |
Time | Browsers | Views | Visits |
1/15/2009 | | 93520 | 9992 |
1/15/2009 | Microsoft Internet Explorer | 54057 | 5821 |
1/15/2009 | Mozilla | 15601 | 1666 |
1/15/2009 | Netscape | 6120 | 635 |
Note that this table contains two sets of headers and two total columns. What we really want is a result that has only one header row and no totals. It should look like the following.
Time | Browsers | Views | Visits |
1/16/2009 | Microsoft Internet Explorer | 48488 | 5193 |
1/16/2009 | Mozilla | 13547 | 1445 |
1/16/2009 | Netscape | 5942 | 636 |
1/15/2009 | Microsoft Internet Explorer | 54057 | 5821 |
1/15/2009 | Mozilla | 15601 | 1666 |
1/15/2009 | Netscape | 6120 | 635 |
This gives me a table that I can work with better for creating pivot tables, charts and appropriate totals.
So, how do we do this? Well, first of all, the application allows you to define that you want this data, headers or totals, stripped off before inserting into the spreadsheet of choice. Since we know exactly where these are in the result set, we really only need to identify which row of data to remove: headers are always in row one and totals are in row two. The code in the application that does this is shown below.
' Check to see if we need to strip off the Date/Totals row
If UCase(m_strIgnoreTotalDate) = "Y" Then
m_intCheck = InStr(1, g_strResult, "</tr>") ' Find the first end of table row - that's the header row
If m_intCheck > 1 Then
m_intStart = m_intCheck + 1 ' Start of date/total row
m_intEnd = InStr(m_intStart, g_strResult, "</tr>", vbTextCompare) + 4 ' End of date/total row
' rebuild the result without the date/total stuff
g_strResulta = Left(g_strResult, m_intStart + 3)
g_strResultb = Right(g_strResult, Len(g_strResult) - m_intEnd)
g_strResult = g_strResulta & g_strResultb
End If
End If
' Get our data and put it on the Clipboard
' Check to see if we need to strip off the header row
If UCase(m_strIgnoreHeader) = "Y" Then
m_intCheck = InStr(1, g_strResult, "<table border=1>")
If m_intCheck = 1 Then
m_intStart = InStr(1, g_strResult, "<tr>") ' Start of the header row
m_intEnd = InStr(1, g_strResult, "</tr>", vbTextCompare) + 4 ' End of the header row
' Rebuild our result without that stuff
g_strResulta = Left(g_strResult, 16)
g_strResultb = Right(g_strResult, Len(g_strResult) - m_intEnd)
g_strResult = g_strResulta & g_strResultb
End If
End If
The right approach is to first look to see if we want to remove the “Total” row and do that. The reason for this is that we know this data is in row two. If we were to delete the header row first, the total row would move to the first row and would complicate the logic. So, the easy way to do this once we have decided that removing it is the thing to do, we simply look for where that row starts and ends. The start position is simply the first character after the end of the first row and the end is the last position of the “</tr>” string. Once we calculate those two values we simply recreate our result to copy the data up to the start and after the end of that row.
The same basic approach is taken for the header row. We know exactly where the start of that row is because we know the number of characters leading up to it. We then calculate the end in the same way we did for the totals row except that we are only looking for the end of this first row. Once we have these values calculated, we can again recreate the result set to include only the data prior to and after the first row of data.
Once we have the result set that is needed for our spreadsheet the only real task left is to decide where to put it. By that I mean what worksheet and which cell. The cell we need is the top left cell. Then we paste our data in there and we are done.
I hope these posts have provided useful information. Whether you choose to use this sample application or to write your own my goal here has been to provide some insight into how one can approach using the REST URLs with Excel and VBA. I will continue to work on this application with bug fixes (I am sure there are a few) and new functionality. Eventually I will add the trend reports and will support future versions of the API.
As always, I welcome your comments.
Michael Love
Senior Solutions Engineer
