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
- 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:
- 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.
- 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.
- 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.