Getting Data Out

Love ODK Central. Great for gathering data, but how do folks get data out?

I managed to get some basic dashboards going using Redash but I haven't managed to find a tool that's free and connects to the OData feed to produce paginated, printable, traditional reports.

Ideally I'd like to be able to design a report and let other members of my team enter parameters and retrieve/print formatted reports as and when needed.

Anyone else done this? I gather I can do it with Word/Excel and VBA, but it seems a clunky way to do it.

Thanks!

hi @mrrodge

did you look at this post?

1 Like

I hadn't seen that - guessing my search terms are off. Will take a look, thanks!

OK had a look - totally, completely and utterly lost.

Don't understand where or how it's installed, let alone if it's possible to build a customised report.

I'm looking for some way of designing a PDF output for a given form, with a query parameter to select which submission (based on a certain field) so that a team member can open the report, select a submission and print/email the resulting PDF.

Is that what this does? I'm not looking for BI type graphs/numerical analysis of multiple submissions (I think I have that with Redash), just a friendly way of presenting what was entered on a particular submission.

Thanks again!

If you carefully read the post you see that there's a link to the github repository:

There you have the instructions on how to install and how it works.
Through the Shiny app you connect with OData to your submissions.
You can filter these graphically, then you choose the kind of reports you need and you get an html file with the report that can be opened on any browser and print as well.

In the post there's as well a link to a demo on how it works.

https://drive.switch.ch/index.php/s/9z47qC2PoN82KEX

Hi @mrrodge

Excel could indeed be a good starting point. Its Power Query features wouldn't even require you to use VBA and are relatively easy to use; most things can be done with it already. Plus it can read the OData feed directly. You may then try to develop a spreadsheet that works well with the print layout. Using filters, you may change what the spreadsheet shows in order to generate individual PDFs.

2 Likes

I like to use an intermediate database to store Central's data in a classical relational database and expose it to our other tools (redash for web dashboards, jasper studio for static pdf, QGIS to print map atlases...)
If opensource tools like redash/metabase/superset could have a real ODATA connector which is not only use by Central it would be a great thing.

1 Like

Thanks all. I've had the same frustrations with metabase etc not having Odata connectors.

Redash has a 'details view' which comes fairly close but it doesn't have any configuration options so isn't flexible enough at the moment (at least I don't think, anyway).

Hadn't considered Jasper Studio - will take a look at that, thanks.

I started to use ireport years ago to generate static pdf. It became JasperStudio wich is powerful but now a bit too complex for our simple needs (here static pdf reports).

So if JasperStudio is too complex now, are you using something else or sticking with it? Can it do the JSON/API connection to Central or do I have to have the intermediate database?

Also why are folks doing it that way i.e. two databases, rather than just configuring Central to use a custom database and accessing that directly?

Thanks!

No I still use Jasper because I have old report templates that still work with it and it is simplier than redevelop.

It depends on your needs, the other tools you use, the workflows that already occur in your organization and your skills (r, python... mine is SQL)
I exposed our use case here : SQL first try to get Central data into internal PostGIS database - #5 by mathieubossaert
I do need to consolidate the data we gather with ODK in a larger database, which contains a lot of other data to "join" to.
I could manage to directly query Central's database, but logically, the "guaranteed" interface to Central is its API.
So I started to get data out from Central into our PostGIS database schemas through the API.

And now, as other users, I see and use ODK Central as a whole independent tool (even if it uses an internal PostgreSQL database) I query through its great API :wink:

OK so I've gone down the Jasper Studio route and am stuck at the first hurdle - creating a data adapter for the JSON in the feed. Any tips? I just keep getting a 403 yet with Redash it works.

Seeing we're after a no-code/low-code solution here, I just wanted to mention the Google Data Studio for ODK Central. It's pretty new but looks very promising!

1 Like

Update for anyone interested - it's taken me a VERY long time but I've been able to use the Central API to get whatever I want from Central, with the exception of images/attachments.

I had to use the API to start a session, obtain a token, use the token to access the data and place in my report. I haven't done anything as sophisticated as writing a proper query yet or used parameters (except for the token itself) but it appears to work reliably.

I've hit a wall though with importing images - I can construct the URL to an image pretty easily but Jasper doesn't have the ability to enter HTTP headers when requesting the image, so I can't give it the token. Any ideas?!

Thanks!

Not an expert in using Jasper, I simply use ruODK::odata_submission_get and that does all the work including downloading of attachments.

Does Jasper support R?

That sounds really promising but I think is probably waaaay over my experience level. I looked at R when trying to find a decent reporting tool but read lots of scary stories of R being one of the toughest computer languages to learn. Am I wrong? If so, any good places to start?

I've never done any SQL, java or anything and literally just know the basics of what a database is, what tables are etc.

Edit: I don't think Jasper supports R.

Thanks!

@mrrodge Writing in a hurry, apologies for brevity!

From all the available options, my personal bias is towards R and ruODK, as it does a lot of heavy lifting that otherwise you'd have to do yourself. Parsing form definitions from XML was heinous.

R is a really useful language and has come a long way since the early days. (It took me three separate tries to get warm with it, I feel your pain)

Happy to give you a few pointers!

To get started immediately, the ruODK README has a ton of "getting started" resources:

In brief:

  • Install R
  • Install RStudio Desktop
  • Install ruODK from the r-universe
  • Run through this vignette (with your own credentials, so you're using your own data) to get a feel of how ruODK works
  • In RStudio, create a new RMarkdown document > From template > "ODK Central via OData", and follow instructions
  • If you get stuck, ask for help here on the forum and tag ruODK so I get a ping

Further resources: e.g. https://education.rstudio.com/learn/beginner/ (has links to other resources)

Lastly, have you tried the ODK connector for Google Data Studio? https://getodk.org/google-data-studio/

1 Like

Just had a look at the bits and bobs you refer to - ouch, my head is hurting already!

I'm not seeing the bit I need though - an easy way to publish reports so my team can go to an intranet address, choose a report, input any parameters and run the report - this is what Jasper offers but it looks like R will only give me RStudio Connect, which is costly.

Am I missing something?

What you describe here is a dashboard with user inputs as parameters for analysis and visualisation. In R, the prime technology for that is https://shiny.rstudio.com/

You are not limited to hosting RShiny apps on RStudio Connect, you can host it yourself using Shiny Server, or deploy via Docker images.

https://github.com/SwissTPH/repvisforODK is a nice generic RShiny dashboard that lets your users build and export their own reports. Ping to the developer @lucidviews and his colleague @Thalie for more help. Following the README will get the repvisforODK app running on your own machine, which should be enough to evaluate.

Google Data Studio allows controls to capture user input, so that might be another approach.
Ping to the developer @samlevy for more help.

Let us know how these two options work for you.

1 Like

Update for anyone interested - I still haven't managed what I'm trying to do. With Aggregate/the old setup there was a plethora of tools available but the API implementation and subsequent locking out of the database has made it totally impossible to have a low-code/no-code reporting tool that allows effective sharing to non-techie individuals.