The previous posts on this application have provided some insight on how to get general profile and report data.  We have also parsed the results that were returned assuming that we used the JSON data format.  So now we are just about ready to create a REST URL that will allow us to fetch some actual report data.  But before we do there is one last task to be performed and that is getting a list of measures that are available for our desired report.  We need to do that just in case we want to limit the measures return to specific measures rather than all.  One other element we are interested in knowing is whether or not we can use the “search” functionality to limit the results based on contents in the dimension data.  Whether or not we can do that is determined by whether or not the report is multi-dimensional or hierarchical.

 

 

The REST URL to make this request to get what is referred to as the report meta data is shown below.  This URL clearly defines both the Profile and the Report desired via their ID values.  The “info” field defines this as a request for the report meta data.

 

 

     https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/95df19b6d9f2/info/?format=json

 

 

This request is made in the same way that previous requests are made and were discussed in my prior posts.  The resulting data in JSON format will look something like the data below.  Remember, however, that there are no spaces or other extraneous characters returned.  These are shown here to make this easy to read.

 

 

{
    "accountID" : 18079,
    "profileID" : "YcL4a5dufF6",
    "ID" : "95df19b6d9f2",
    "name" : "Browsers",
    "language" : null,
    "type" : "dimensional",
    "properties" : {
        "isHierarchy" : false,
        "intervalsEnabled" : false,
        "internalID" : "topbrowsers_v",
        "IsRealTimeCompatible" : true,
        "ProfileCategory" : null
    },
    "dimension" : {
        "ID" : "Desc",
        "name" : "Browsers",
        "type" : "data",
        "Range" : null,
        "Properties" : null,
        "SubDimension" : null
    },
    "measures" : [
        {
            "name" : "Views",
            "accumulationType" : null,
            "ID" : "Hits-0",
            "columnID" : 0,
            "measureFormatType" : "numeric",
            "AllowTotals" : true
        },
        {
            "name" : "Visits",
            "accumulationType" : null,
            "ID" : "Users-0",
            "columnID" : 0,
            "measureFormatType" : "numeric",
            "AllowTotals" : true
        }
    ]
}

 

 

The entire result set is enclosed in curly brackets – “{  }”.  There are within this set of data three (3) main segments of data.  The first segment is general report information.  Here we have such information as the Profile ID, the Report ID, the Report Name, and other general properties.  It is here that we find a couple of critical pieces of data. These are both in the properties area: isHierarchy”, and "intervalsEnabled".  The “isHierarchy” value tells us whether or not we can use the search functionality.  A value of “false” indicates this is a single dimension report and that we can do this.  On the other hand, a value of “true” indicates that the report contains more than a single dimension and that we cannot use the search functionality.  The second property item that may be of interest to us is the "intervalsEnabled" item.  The value of this item tells whether or not trends data is available and we can use those time periods to retrieve data. Again, a value of “false” means interval/trend data is not available and “true” means that interval/trend data is available.  This sample application has not implemented or enabled this feature, so for our discussion here the point is moot.

 

 

The second segment of data in our result is a list of dimensions.  In a single dimension report this list is obviously short and appears as shown above.  In a multi-dimensional report the sub dimensions are listed as shown below.

 

 

"dimension" : {

        "ID" : "contentgroup",

        "name" : "Content Group",

        "type" : "data",

        "Range" : null,

        "Properties" : null,

        "SubDimension" : {

            "ID" : "xIfaV44n7l5",

            "name" : "Content Sub-Group",

            "type" : "data",

            "Range" : null,

            "Properties" : null,

            "SubDimension" : null

        }

    },

 

 

Note that each sub dimension is listed as a hierarchical item of the dimension above it.

 

The key set of data that we want out of this result data is the list of measures.  The measures are all in a third segment bounded by square brackets – “[  ]”.  Each individual measure is defined within its own space bounded by curly brackets – “{  }”.  For each measure there are a number of definition items including, "name", "accumulationType", "ID","columnID", "measureFormatType", and "AllowTotals".  For purposes of our application we only need two of these values: “name” and “ID”.  The name filed is used for display to the user in the interface and the ID is used when we create the REST URL to request our data.  In the application we store this data in a custom data type.  This data type is shown below.

 

 
Public Type MEASUREDATA
    name As String
    id As String
    accumulationType As String
    columnID As String
    measureFormatType As String
    allowTotals As String
End Type
Public g_Measures() As MEASUREDATA
 

The function below is used to extract the data we need from the JSON result.  Once extracted the list of measures is available for the user to select. A check is also made to either enable or disable the use of the “search” functionality based on this being a single or multi-dimensional report.

 

 

Public Sub GetReportMeta()

   

    ' Get Report Meta so we can have list of measures

    Dim m_intWhere As Integer

    Dim m_strIsHier As String

   

    frmProfileList.lstMeasures.Clear                       ' Clear form we will display list on

    g_intMeasureCnt = 0                                    ' Initialize the profile count to 0

       

    ' Check to see if the user has selected the Profile Summary

    If frmProfileList.lstReports.ListIndex = frmProfileList.lstReports.ListCount - 1 Then

        frmProfileList.optMultiple.Enabled = False

        frmProfileList.optSingle.Enabled = False

        frmProfileList.optSEPeriods.Value = True

        Exit Sub                ' Exit this sub because there are no choices for measures

    Else

        frmProfileList.optMultiple.Enabled = True

        frmProfileList.optSingle.Enabled = True

        frmProfileList.optSEPeriods.Enabled = True

    End If

   

    ' Set up URL for fetching Report Meta

    g_strURL = g_strBaseREST & g_strSelectedProfileID & g_strReportsREST & g_Reports(frmProfileList.lstReports.ListIndex).id & g_strReportsMetaREST

   

    ' Call function to execute REST URL

    If Not RunREST(g_strURL, g_strUserName, g_strPassword) Then

        Exit Sub

    End If

       

    ' Check to see if the report is hierarchical - no search on those

    m_intWhere = InStr(1, g_strResult, "isHierarchy")                ' Find start point for measures list

    m_strIsHier = Mid(g_strResult, m_intWhere + 13, 4)

    If m_strIsHier = "true" Then

        frmProfileList.txtSearch.Enabled = False            ' disable text entry in search box

    Else

        frmProfileList.txtSearch.Enabled = True             ' enable text entry in search box

    End If

   

    ' Display the JSON URL Result

    m_intWhere = InStr(1, g_strResult, "[")                ' Find start point for measures list

    g_strResulta = Mid(g_strResult, m_intWhere + 1, Len(g_strResult) - (m_intWhere + 2))

   

    g_blnMore = True

    Do While g_blnMore

        g_intSplit = InStr(1, g_strResulta, "},")                 ' See if there is more than one entry

        If g_intSplit = 0 Then

            g_strResultb = Mid(g_strResulta, 2, Len(g_strResulta) - 2)

        Else

            g_strResultb = Mid(g_strResulta, 2, g_intSplit - 2)   ' Grab the first set of data

        End If

        g_strResultb = Replace(g_strResultb, """", "")            ' Get rid of the extra " characters

        g_strArr = Split(g_strResultb, ",")                       ' Create an array with this set of data

        ReDim Preserve g_Measures(g_intMeasureCnt)                ' Make room for new measure

        g_Measures(g_intMeasureCnt).name = Mid(g_strArr(0), 6)           ' Store the measure name

        frmProfileList.lstMeasures.AddItem g_Measures(g_intMeasureCnt).name    ' Add name for selection from form

        g_Measures(g_intMeasureCnt).id = Mid(g_strArr(2), 4)             ' Store the Measure ID

        g_Measures(g_intMeasureCnt).accumulationType = Mid(g_strArr(1), 18) ' Store the Accumulation Type

        g_Measures(g_intMeasureCnt).allowTotals = Mid(g_strArr(5), 13) ' Store the Allow Totals value

        g_Measures(g_intMeasureCnt).columnID = Mid(g_strArr(3), 10) ' Store the Column ID

               

       

        ' Check to see if we are done parsing

        If g_intSplit = 0 Then

            g_blnMore = False                             ' We're done

        End If

        g_strResulta = Right(g_strResulta, Len(g_strResulta) - g_intSplit - 1)   ' Set up for next profile

        g_intMeasureCnt = g_intMeasureCnt + 1               ' Bump the profile count

    Loop

   

    Application.Cursor = xlNorthwestArrow           ' Set the application cursor back to normal (arrow)

   

End Sub

 

 

So, now we have all of our data available in the user interface for the user to select what data will be included in the requested report.  The three keys areas of selection are the time periods, the search data (if available), and the selected measures.  Other selections include limiting what rows to return and how to deal with report totals.  I won’t go into all of the logic on these selections and the interactions, but there are certain checks that are made to ensure consistency and validity of the selected items.  But once these selections are made the user can create the URL and then copy it to the worksheet for eventual execution.

 

 

That’s all for this post.  The next and last post on this application will deal with processing the results of the report data requests.

 

Michael Love

Senior Solutions Engineer