[Progresso] - Friday challenge! Integrate Google document in to SSRS reports

[Ditto]

If you like a challenge.... here's the idea - to give the access control we need on pastoral notes, we are looking at storing a Google a document (sheet or doc) link as a pastoral behaviour note. It's slightly clunky opening a tab from the UI, but OK. The big downside of course is reporting. Anyone on thoughts as you would approach SSRS coding to pull the google document info in to the SSRS report?

[matt40k]

No chance.

 

If you use use Office 365 Forms you could hook up Power BI. If you use Access web thing you could point SSRS at it (as the data is stored in a Azure SQL DB).

[djrscally]

If you like a challenge.... here's the idea - to give the access control we need on pastoral notes, we are looking at storing a Google a document (sheet or doc) link as a pastoral behaviour note. It's slightly clunky opening a tab from the UI, but OK. The big downside of course is reporting. Anyone on thoughts as you would approach SSRS coding to pull the google document info in to the SSRS report?

 

https://developers.google.com/sheets/

 

I doubt that you can do it with Progresso directly. My expectation would be you'll have to set up your own SSRS server onsite (super easy), and populate a database pulling info from the Progresso API (super easy) to get students data and also via the Google Sheets API (never tried but how hard can it be right) to get the stuff from there, and then write your reports against that database.

 

Frankly though, I'd try to think of something else if I were you

 

EDIT: Like maybe this: https://www.google.com/analytics/data-studio/features/

 

Obviously will hook into the Google Docs anyway, and you could still do the Progresso API thing to get it to pull student info.

 

2nd Edit: What's your problem with the access rights?

Edited March 16, 2018 by djrscally

[Ditto]

No chance.

That's upped the challenge

If you use use Office 365 Forms you could hook up Power BI. If you use Access web thing you could point SSRS at it (as the data is stored in a Azure SQL DB).

We're fairly tied to google. We have rolled out Office Desktop to staff though, but I don't think we're likely to go Office 365, but I still appreciate the input.

Edited March 16, 2018 by Ditto

[Ditto]

https://developers.google.com/sheets/

I guess I was more thinking a call within SSRS rdl to Google API, but perhaps the other way is worth some thought.

My expectation would be you'll have to set up your own SSRS server onsite (super easy), and populate a database pulling info from the Progresso API (super easy) to get students data

I might look at this at another time - it throws up all sorts of ideas - I guess I can create a simplified data model and use a variety of third party apps - or SQL directly.

Frankly though, I'd try to think of something else if I were you

You're probably right. Maybe CPOMS, (or different MIS - do other MIS support what I want - see below) or wait; Progresso are aware of the requirement and report other schools have asked for the same. It's high on the list of requirements for Behaviour Management.

2nd Edit: What's your problem with the access rights?

This is the requirement of storing pastoral comments, but only allowing access on a 'need to know' basis. This is covered in another thread of mine which you've already contributed/http://www.edugeek.net/forums/mis-systems/192493-progresso-keeping-behaviour-notes-confidential.html

[matt40k]

Hey, it's do-able, its just not gonna be nice or pretty. Ideally, you'd use SSIS to load the data into SQL. I know you could use stored procedure - like: https://www.brentozar.com/archive/2009/02/getting-your-twitter-feed-into-sql-server-with-tweet-sql/

 

Again, not nice, pretty or fun. Wouldn't be something I'd want to do. I'd be more tempted to use MS Flow (or something) to load the data into PowerBI \ Excel \ SQL.

 

Anyway, good luck with you're madness

[djrscally]

I guess I was more thinking a call within SSRS rdl to Google API, but perhaps the other way is worth some thought.

 

Yeah I think that will probably be a nightmare. I think it would "work" in that if the call happened perfectly you could probably get some json data into the report or something, but parsing it would be horrendous and you won't be able to do error handling properly and stuff. Honestly I think matt is right and if you're determined to do this, using SSIS to dump the data to SQL from Google and Progresso's API, and munging it there is probably your only hope.

 

I might look at this at another time - it throws up all sorts of ideas - I guess I can create a simplified data model and use a variety of third party apps - or SQL directly.

 

Yeah, I was doing this at one point. It works pretty well and helps get past some of the limitations of the report model in Progresso, and lets you hook up stuff like PowerBI.

 

You're probably right. Maybe CPOMS, (or different MIS - do other MIS support what I want - see below) or wait; Progresso are aware of the requirement and report other schools have asked for the same. It's high on the list of requirements for Behaviour Management.

 

This is the requirement of storing pastoral comments, but only allowing access on a 'need to know' basis. This is covered in another thread of mine which you've already contributed/http://www.edugeek.net/forums/mis-systems/192493-progresso-keeping-behaviour-notes-confidential.html

 

Oh yeah I forgot about all this...well I thought for 20 minutes and I still don't have a bright idea on this one. I'll ask around a bit...

[dhicks]

Anyone on thoughts as you would approach SSRS coding to pull the google document info in to the SSRS report?

 

Google Sheets has (of course) a REST API:

 

https://developers.google.com/sheets/api/reference/rest/

 

As, seemingly, does progresson:

 

https://progresso.docs.apiary.io/#reference/employees-collection/employee-basic-records/create-learner

 

If you can write something to sit in the middle and pass data between the two then that might do what you're aiming to. Sorry, I don't have any experience with Progresso, so I can't picture what you're aiming for, although in my experience Google Sheets' API is easy enough to use (I have a Python script sycning data from Spiceworks' API to a Google Sheets spreadsheet).

[Ditto]

This was more in line with the sort of thing I was thinking. Whether I get time to pursue this (perceived madness), is another matter.