IntroductionThe 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 ProcessLet'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.
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.
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.
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.
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.
Paste the REST URL we copied from the browser into the highlighted area named "Address" and click on the "Go" button.
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.
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.
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".
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.
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 VBAThe 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 ApplicationWhen 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.
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 Name||The name that you want to see for the extracted data.|
|Target Sheet||The name for the worksheet to populate with the extracted results.|
|Target Title Cell||The cell on the worksheet that will be populated with the Report Name.|
|Target Data Cell||The top left cell that will be populated with the extracted data.|
|Rest URL||The 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 ReportSo, 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.
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.
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.
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.
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.
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.
|List of Reports||From this list select the one report that contains the data you wish to extract.|
|Options for Time Periods||There are three options labeled:|
" Single Time Period
" Range of Periods
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 Available||Select 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.|
|Offset||This 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 Periods||This 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 Criteria||Enter here the values to limit data returned where the dimension contains the string value.|
|Limits for Rows Returned||Enter 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 Measures||By 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.
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.
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.
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.