Using OData queries to calculate simple statistics

Steve Lay HeadshotPosted by Steve Lay

In previous blog posts we’ve looked at how to use OData clients, like the PowerPivot plugin for Excel, to create sophisticated reports based on the data exported from your Questionmark Analytics results warehouse. In this post, I’ll show you that your web developers don’t need a complex tool like Excel to harness the power of OData.

There are several third party libraries available that make it easy for your developers to incorporate support for OData in their applications, but OData itself contains a powerful query language and developers need nothing more than the ability to fetch a URL to take advantage of it. For example, suppose I want to know what percentage of my participants have passed one of my exams.

Step 1: find out the <id> of your assessment.OnDemand

To start with, I’ll build a URL that returns the complete list of assessments in my repository. For example, if your customer number is 123456 then a URL like the following will do the trick: (This and the following URLS are examples, not live links.)

The resulting output is an XML file containing one record for each assessment. Open up the result in a text editor or ‘view source’ in your browser, scan down for the Assessment you are interested in, and make a note of the entry’s <id>, that’s the URL of the assessment you are interested in. If you’ve got lots of assessments you might like to filter the results using an OData query. In my case, I know the assessment name starts with the word “Chemistry”, so the following URL makes it easier to find the right one:$filter=startswith(Name,’Chem’)

All I’ve done is add a $filter parameter to the URL! The resulting document contains a single assessment! I can see that the <id> of my assessment is actually the following URL:

Step 2: count the results

I’m not interested in the information about the assessment but I am interested in the results. OData makes it easy to navigate from one data item to another. I just add “/Results” to the URL:

This URL gives me a similar list to the assessment list I had earlier but this time I have one entry for each result of this assessment. Of course, there could be thousands, but for my application I only want to know how many. Again, OData has a way of finding this information out just by manipulating the URL:$count

By adding /$count to the URL I’m asking OData not to send me all the data, but just to send me a count of the number of items that it would have sent back. The result is a tiny plain text document containing just the number. If you view this URL in your web browser you’ll see the number appear as the only thing on the page.

I’ve now calculated the total number of results for my assessment without having to do anything more sophisticated than fetch a URL. But what I really want is the percentage of these results that represent a pass. It turns out I can use the same technique as before to filter the results and include only those that have passed. My assessment has Pass/Fail information represented using the ScorebandName.


the $count option just sends a count of the number of items that it would have sent back without it$count?$filter=ScorebandName eq ‘Pass’

Notice that by combining $count and $filter I can count how many passing results there are without having to view the results themselves. It is now trivial to combine the two values that have been returned to your application by these URLs to display a percentage passed or to display some other graphic representation such as a pie chart or part filled bar.

As you can see, your developers don’t need a sophisticated library to write powerful client applications with OData. And these HTTP documents are just a few bytes in size so they won’t use much bandwidth (or memory) in your application either. For additional resources and definitions of all the OData filters, you can visit the OData URI conventions page at

Questionmark Users Conferences offer many opportunities to learn more about Questionmark Analytics and OnDemand. Registration is already open for the 2014 Users Conference March 4 – 7 in San Antonio, Texas. Plan to be there!


Embedding Questionmark Assessments in PmWiki

Embed a Questionmark Perception survey or quiz inside PmWiki.

  • To see how this would look, see a snapshot of an assessment embedded within a PmWiki page.
  • Check out this How-to on our developer Web site.
  • PmWiki is a wiki-based system for collaborative creation and maintenance of Web sites. PmWiki pages look and act like normal web pages, except they have an “Edit” link that makes it easy for administrators to customize and add new markups such as an IFrame that will house Questionmark assessments. Hundreds of features are already available via extensions (called “recipes”) that are available from the PmWiki “Cookbook.”

Embedding Questionmark Assessments in Google Wave

Screenshot of an embedded assessment in Google WaveEmbed a Questionmark Perception assessment, survey or quiz inside your Google Wave profile.

  • To see how this would look, see a snapshot of an assessment embedded into Google Wave.
  • Check out this How-to on our developer Web site.
  • Google Wave is an online tool for real-time communication and collaboration.  Embedding an assessment into Google Wave may be useful if you want to ask the members of your Wave to complete a quiz or  simply fill in a survey. The results can then be analyzed and reported on from Perception.

Embedding Questionmark Assessments in Live Meeting

Embedding Questionmark Assessments in Live MeetingConduct a survey or ask questions during or after an online presentation in Live Meeting.

  • Microsoft Office Live Meeting is a hosted Web conferencing service that connects people in online meetings, training, and events. Incorporating a Questionmark Perception assessment or survey into a Live Meeting can help keep your colleagues engaged and provide an easy way to gather feedback about your presentation in real time.