Adhoc AI Reporting & Downloads

June 11, 2013 Lonnie Haire

I wanted to share the results of a poc (proof of concept) effort i recently found success with using the api for some custom adhoc reporting via ms excel. as i get started i would like to make a few assumptions:

    • some technical background or aptitude is helpful
  • xml, xsl, really any kind of scripting
    • knowledge of the VersionOne (data) api
  • 3 api’s (data, meta, and localization)
  • experience with the data tab in ms excel

Background: the customer’s were business users (1 product owner + 1 stakeholder) and were able to use and extend usage fairly simply after the initial poc. it is also worth mentioning that the customer had already been using VersionOne configurable grid views, manually exporting the data, and manually manipulating some of the roll-up data that wasn’t readily available out of the box. the primary goal of the poc was achieve the same reporting value more quickly and minimize the amount of manual data gathering. this is a typical pattern for leveraging the api to accomplish adhoc reports, although there are certainly others. lastly, note that api custom reports are point in time (current status). for trending or data analysis consider VersionOne Ultimate edition – Analytics.

There are 3 essential tips i can offer to those interested in developing adhoc reports using the VersionOne api + ms excel. they are:

  1. keep the end in mind – know what the goal is before getting started. if its a graph, consider manually gathering the data required to generate the graph prior to automation, at the very least a lo-fi wire frame.
  2. master the ask – ensure you can gather the data you need via an api query. there are examples available online or reach out for some help from me, support, or your account director… someone will help get you moving in the right direction.
  3. contribute to community – share your efforts. there are many other users out there trying to solve the same problem you are and have no idea how to get started.

Artifacts: here is a fairly polished version of our poc.

  • Epic.By.Report – [Info] helps to explain the doc, [Data Sheet] hosts all the reportable data, [all other tabs] are cross tables from the “Data Sheet” to generate some fairly simple adhoc reports. feel free to experiment with the data and generate some meaningful graphs.
  • parameterized.query.iqy – offered to share how to pass parameters into your excel web connections (your VersionOne rest-ful api query). parameters are passed from the [Data Sheet]; B2 through B6.

I hope you find value from this. please come back and let us know what you have been able to achieve through the VersionOne API.


About the Author

Lonnie Haire

As a seeker of simplicity, Lonnie provides solutions for pragmatic enterprise agility. He has held a wide range of roles including project manager, software engineer, solution architect, team lead, coach, and consultant to round out his real-world agile experience. Since 1991, Lonnie has been providing solutions and services to organizations large and small. Introduction to eXtreme Programming in 2000 has been the catalyst for stewardship of sustainable and disciplined lean-agile practices.

More Content by Lonnie Haire
Previous Article
TeamForge: An Essential Cog Driving CloudForge
TeamForge: An Essential Cog Driving CloudForge

Recently, we opened up our CloudForge platform to allow unlimited free TeamForge projects; previously only ...

Next Article
TeamRoom = Ease of Navigation, Ease of Switching Projects, Ease of Use
TeamRoom = Ease of Navigation, Ease of Switching Projects, Ease of Use

In the Fall 12 Release we introduced the revolutionary TeamRoom that has been generating tons of positive f...