Excel/VBA Sample Application

Introduction

The Webtrends REST API offers a broad range of functionality that enables the use of Webtrends data in new application forms and presentation.  One of the most often used applications outside of the actual Webtrends user interface for the presentation and analysis of information is Microsoft Excel.  It is fairly easy and straightforward to use Excel for this purpose once the data is made available.  REST URLs, once they have been defined, are simple to use for the extraction of the desired data and the population of that data in an Excel worksheet.  You can even automatically update that data once the original data query has been completed.  We'll review that process a little later in the document.

 

VBA (Visual Basic for Applications) offers additional opportunities for controlling the creation of the REST URLs and the access and maintenance of the data in an Excel workbook.  This document describes how this can be accomplished in the form of a sample application.  The application itself is embedded as an object in the document.  The idea that this application presents is that a user will need to somehow create a valid REST URL in order to extract the data desired within the workbook.  This REST URL generation process can easily be accomplished though either the Webtrends REST Generator application (https://generator.webtrends.com/) or via the SHARE function within the Webtrends Analytics 9 Insight interface.  Either methodology will allow you to create valid REST URLs that can be used for populating Excel worksheets with Analytics data.  The only drawback to this is that it takes one tool to create the REST URL and then another to use it.  It also assumes that you will use the Web Data Access process in Excel to extract your data and populate the desired workbook.

 

Please note: This document describes a process for populating an Excel Workbook with report data that has been queried and returned using REST URLs.  This is not intended in any way to be proffered as an end solution.  Rather this is an example of how to use these tools together.

 

Using the Web Data Access Process

Let's start the review process here by looking at how we might generate a REST URL and then carry that URL over to Excel and populate a worksheet.  We'll use the REST Generator mentioned above and do this step by step.

 

1.    So, the first step in the process is to create the REST URL that extracts the data we need.  Launch into the REST Generator using your favorite browser.

8-24-2009 4-56-25 PM.png

Log in using your own account credentials.  This will not be a complete tutorial on the REST Generator.  We will now walk through the process to create a REST URL to extract a single report.  From the screen below we will select the profile where our data resides, the report we are interested in, and the time period for our data.  As we do this the REST URL is built for us.

8-24-2009 4-57-17 PM.png

Once our REST URL has been generated we can select it and copy it (CTL-c) from the area where it shows in the browser.

8-24-2009 4-59-29 PM.png

 

2.    The next step in the process is to use the REST URL in Excel that has been generated above.  Launch the Excel spreadsheet where you want to put your data.  In our example here we are using a new workbook.

8-24-2009 4-59-42 PM.png

In order to utilize our REST URL to populate this worksheet, we need to use the Data ribbon.  From the ribbon we will select the "Get External Data - From Web" option.

8-24-2009 5-00-09 PM.png

Paste the REST URL we copied from the browser into the highlighted area named "Address" and click on the "Go" button.

8-24-2009 5-02-33 PM.png

You will be challenged for authentication credentials.  Enter them in the format shown below.  Note that the Username field is a combination of your Account Name and User ID as you would use to log into your Webtrends account.  Your password is your normal password used for that same purpose.

8-24-2009 5-02-44 PM.png

When your data is first returned, it will appear as in the figure below along with the message about secure and non-secure items.  Go ahead and click on "Yes".  Then click on the "Import" button at the bottom of the "New Web Query" dialogue window.

8-24-2009 5-02-57 PM.png

The next window you will see looks like the one below.  This dialogue is now the last step in the process.  It allows you to select the cell (top left) where you want your extracted data to go. If the default is OK (this is the cell that was active when you started the query), simply click "OK" and your data will be populated on your worksheet.  If you want to change the cell, go ahead and enter a new value and then click "OK".

8-24-2009 5-03-18 PM.png

Your result will look something like the figure below.  Of course, the report data you extracted will determine how many rows and columns you actually get.

8-24-2009 5-03-31 PM.png

There is one thing to note about the actual result that has occurred here.  Not only have we extracted data and populated our Excel worksheet with it, but we have also created a "Connection" that ties the query (REST URL) to the data.  You can use the "Refresh" option in Excel at any time to repopulate the worksheet with new data.  This is especially helpful when you are using relative time periods.

 

For many purposes the process described above will be quite sufficient.  This is especially true for creating data to respond to ad hoc requests for information.  The drawback to this process is that it does take multiple tools to make it happen and it does result in creating the various "connections" within the workbook for each query.  This can make it difficult to move data around after you extract it.  It's also not so simple to remove unwanted "connections" unless you are very familiar with Excel and these functions.

The Sample Application Using VBA

The solution/process above presented some interesting issues.  First, you need more than one application to generate the REST URL that you want.  Second, you need to walk through the process of the web access method to get your data and then populate the desired worksheet.  Third, you end up creating these "connections" that tie your extracted data to specific locations.  Granted you can remove these, but it means extra steps.  And, finally, you need to repeat this process for each data extract you want to do.  So, four individual reports would mean four trips through this process.

 

Using VBA within Excel can help to automate this process.  The sample application presented here does just that.  It is, however, meant only to be an example of what you might do and is not intended to be the end-all solution for building complex scorecards or reporting mechanisms.  That being the case the entire application with access to the VBA code is attached here.  Simply open this file and save it to your local system.  From there you can utilize all of its functionality.

 

The Sample Application builds in the process to generate REST URLs and to submit those for data extraction.  Further, it allows you to create as many requests as you would need and to indicate where you want the results to populate.  Each request should be set to populate its own worksheet.

 

First Use of the Sample Application

When you open the Sample Application for the first time you will see a workbook with a single worksheet named "Control" as shown in the figure below.

8-24-2009 5-03-47 PM.png

The first row in our worksheet contains the titles representing data that will be entered on this worksheet.  The individual columns are as follows:

 

Report NameThe name that you want to see for the extracted data.
Target SheetThe name for the worksheet to populate with the extracted results.
Target Title CellThe cell on the worksheet that will be populated with the Report Name.
Target Data CellThe top left cell that will be populated with the extracted data.
Rest URLThe full REST URL generated by the application and used to extract your   report data.

 

 

Using the application takes very little planning and you can easily adapt its usage as you continue to use it.  There is no particular sequence in which you do things, but it is recommended that you begin by deciding for each report what values will go in the first four (4) columns.  These can be adjusted even after data has been extracted.  This allows you to change things like worksheet names, reports titles and the location of data.

 

Building our First Report

So, let's begin by creating a simple report based on browsers.  In our "Control" we will enter the first four (4) columns of data for this report.  It might look like something in the figure below.
8-24-2009 5-04-50 PM.png

The next step in the process will be to generate the REST URL that will extract the data that gives us our report.  We do this by right-clicking anywhere in our worksheet.  Any cell will do.  When you do, you will get a popup menu that looks like the figure below.

8-24-2009 5-05-03 PM.png

Note the two (2) menu items at the bottom.  These two items will allow us to generate all of the REST URLs we need and then to populate our workbook with the result.  Since we don't have any Rest URLs defined as yet, we need to select the item "Generate a REST URL".  When you do this for the first time you will get a dialogue like the one in the figure below.

8-24-2009 5-05-15 PM.png

This is where we will enter our authentication credentials needed to access anything in the Webtrends Analytics environment.  Go ahead and enter all of the required fields.  This is just like logging into Webtrends and you use those same credentials.  After entering the required information, press the "OK" button.  The application will then connect to the Webtrends environment for the designated account and will request a list of the profiles found there.  The application will then display the following dialogue from which we can continue the process of creating our report data REST URL.

8-24-2009 5-05-29 PM.png

Our first task is to select the profile from which we will select our report data.  Once you select a profile the application will then update this dialogue with a complete list of the reports that are available to you and the time periods for which those reports should exist.  An updated dialogue will look like the following figure.

8-24-2009 5-07-05 PM.png

At the very bottom of the Time Periods list you will also find relative time periods labeled as: Today, This Month, and This Year.  These can be used to generate REST URLs that can be used over and over to update data without regenerating the URL with a new specific date.  From this dialogue we can now begin to make all of the selections and enter all of the information needed to define our report extraction.  The following will help you understand all of the options available to you.

 

ItemHow Used
List of ReportsFrom this list select the one report that contains the data you wish to   extract.
Options   for Time PeriodsThere are three options labeled:
" Single Time Period
" Range of Periods
" Trend
These options allow you to create a URL to match your specific time period   requirements.

Single Time Period:
This selection will allow the creation of a REST URL that selects a single   time period. The time period parameter generated will be either absolute or   relative based on what is selected from the Periods Available.

Range of Periods:
This selection will allow the creation of a REST URL that has a start and   end period.  It only uses relative   periods.  You must select an OffSet   number of days from "Today" and the number of days to include in   the range.  The range is entered in the   "Number of Periods" text input box.
Periods AvailableSelect the one time period that defines the base period for which you   wish to select data.  This can be   offset and trended as described above and listed below.
OffsetThis is a numeric offset value used in conjunction with the selected   period.  For example, if you select   “Today” with an offset of “1”, it will create a request for yesterday’s data.
Number of PeriodsThis is a numeric value that indicates the number of periods to select   when accessing data for a range of days or trend data.
Dimension Search CriteriaEnter here the values to limit data returned where the dimension contains   the string value.
Limits for Rows ReturnedEnter either one or two values depending on usage.  If only one value is entered (in “To” box),   this indicates a desire to return the first “n” rows where “n” is the value   entered.  If two values are entered   (one in each box), this indicates the starting and ending row numbers to   return.
Available MeasuresBy default all measures are returned for any report that is   selected.  If you wish to limit the   measures returned then select each measure desired.  You can select multiple measures by holding   down the “control” key while making your selections.

 

Once you have completed all of you selections you can click the "Create REST URL" button and the value created will appear in the text box at the bottom of the dialogue as shown below.

8-24-2009 5-07-21 PM.png

Assuming this URL is what you want you can now place it on the worksheet.  This is done by clicking on the "Place in Workbook" button.  The application will then copy the URL text and insert into the next row where the REST URL column has a blank.  It does not care whether or not you have yet entered any information in the other columns to describe how you want to deal with the report.  Once you have done this, the worksheet will look something like the one below.

8-24-2009 5-07-35 PM.png

Now that we have defined everything we need for a report, we can go ahead and get our data and populate the desired worksheet.  To do this, we again right-click on any cell on the "Control" worksheet.  This time we will select "Run Queries" from the bottom of the popup menu.  The application will proceed to process each REST URL starting in row 2 of the "Control" worksheet and will continue until it reaches a row where there is an empty cell where a REST URL is expected.  So, don't leave any blank lines between REST URL report definitions.  The result will look something like the figure below.  Note the name of the worksheet, the title at the top and the placement of the data.  All of this corresponds to the information that was placed in the first four columns in the row containing the REST URL.

8-24-2009 5-07-48 PM.png

 

Also take note that there was no need to define our new worksheet ahead of time.  The application took care of that for us.  In fact, the application will create any worksheet that is needed that does not yet exist and will clear any existing worksheet that does exist and that it will place data on.

 

Updating the Workbook

Once the Sample Application workbook has been created you are free to modify it in any way you wish.  You can add worksheets to include data from other sources as well as to consolidate data from the individual REST queries into a single view using all of the normal Excel functionality.  You may format the data including creating charts and pivot tables in any way desired.  Just remember that when you "run" the queries to update the worksheets containing query results, those worksheets will be cleared prior to any new data population.

Adding a New REST URL

The addition of a new REST URL is almost identical to the process of creating the first REST URL as described above.  The only difference is that it needs to be added to the next available row in the "Control" worksheet.  Also, when you select the menu option to "Run Queries", all queries will be executed.  There is no option in this version to selectively execute single queries.

Removing REST URLs

You may remove any REST URL from the "Control" worksheet at any time.  When doing so you must take into consideration two points.  First, any worksheet that was created using the REST URL definition will remain unless you also manually delete it.  Second, you must not leave a blank row separating two REST URLs that you want active.  The "Run Queries" functionality starts at the REST URL listed in row 2 and will continue down the list until it finds a blank cell where the URLs are populated.

Manual Addition of a REST URL

The process described above for creating REST URLs utilizes the functionality built into the application to generate the desired URL and populate the resulting URL into the "Control" worksheet.  This does not need to be the only source of this data.  At the beginning of this document mention was made of URLs being created in both the REST Generator application and the Analytics 9 Insight interface via the "Share" option from a report view.  These are valid REST URLs and they can be copied and manually inserted in the "Control" worksheet along with the other definition data about where report result data is to be populated.  When using REST URLs that are created using the REST Generator, be sure that these are requests for report data and they request the Excel (HTML) format as the output format.  Any other type of request or format will produce unspecified results.

Manual Edits to Existing REST URLs

The only purpose of the REST URL generation process in the Sample Application is to make it easy to generate a validly formatted REST URL that you can use to extract the desired data.  This does not preclude you from manually changing that URL using the normal cell editing functions within Excel.  This is really nothing more than a text entry in a cell, until, of course, the application uses it to request data from the account.  So, be sure any change you make results in a valid REST URL and that it will do what you want.

Regenerating an Existing REST URL

If you want to programmatically change a REST URL that is already on the "Control" worksheet, the process is simple and straightforward.  First clear the cell on the "Control" worksheet" that contains the REST URL that you want to regenerate.  This will leave an empty cell where the REST generation process can place the new REST URL.  Then go through the same process that was used to create the REST URL in the first place.  When you select the button to "Place in Workbook", it will place the new URL in the first empty REST URL cell it finds.  So, be sure that this first empty cell corresponds to the REST URL that you are creating.  If you make a mistake, remember, this is just an Excel worksheet.  You can cut and paste data from cell to cell as needed.

Testing REST URLs One at a Time

The process by which the application works may seem like it makes it difficult to test individual REST URLs without also processing every other defined REST URL as well.  There is a way to do this, however.  Remember, when you select the menu option to "Run Queries" it will begin submitting the REST URLs based on the entry in row 2 and then continuing until it finds an empty cell where it expects to find a REST URL.  To test a single REST URL simply place the information for the report on row 2 of the "Control" worksheet.  Then leave row 3 blank.  When you select the menu option to "Run Queries" the application will then submit only the REST URL that is on row 2 and will stop when it finds the blank on row 3.