REST stands for Representational State Transfer.  It is a protocol to pull (or push) data between databases and applications like Excel.

It saves you having to run reports whch once downloaded are stic and soon out of date. Once you have set up your REST data services you can refresh data from within Excel, pulling the latest data and then manipulate in the tool of choice, in this case one familiar to most users, Excel.  The example below if for CiviCRM, but a REST service can be set up against any REST enabled application. Depending on the level of documentation available it may take some jiggery pokery to formulate the correct url, but once you have the url you can easily pull the data into Excel.

The full application (in this case CiviCRM) REST url looks like:

http://[your application server name]/wp-content/plugins/civicrm/civicrm/extern/rest.php?entity=ReportTemplate&action=getrows&instance_id=110&api_key=hs876GFHryhUTgbatLTuFoP&key=085cb1607db3fb826f43458ed6c8h311&json=1

Breaking the url down into it component parts:

 application server name and rest service http://[your application server name]/wp-content/plugins/civicrm/civicrm/extern/rest.php
entity (part of the database) ?entity=ReportTemplate
the action (is being carried out on) &action=getrows
for the specifc report id &instance_id=110
stating the number of records to return (default if not set is 25) &options[limit]=12000
user api key* &api_key=hs876GFHryhUTgbatLTuFoP     (dummy keys – example only)
site key* &key=085cb1607db3fb826f43458ed6c8h311   (dummy keys – example only)
json &json=1

*note the user and api keys act as passwprd/credentials

To test if the construction is correct before creating you Excel web query, paste it into a browser window.

If it returns data … it’s working. If not re-check the full url.  Does the report ID exist? Are you using the correct user and site API keys?

 

You can use REST urls in Excel to query CiviCRM data live, enabling you to directly create reports in Excel which can be refreshed easily without the need to export data through the Civi export function. The ensures the report always has the very latest data.

 

Follow the steps below:

1. Create a report in Civi as per usual and then copy report instance id from url in the web browser.

https://[application server name]/wp-admin/admin.php?page=CiviCRM&q=civicrm%2Freport%2Finstance%2F94&reset=1

 

 

 

 

 

 

 

2. Paste the report id into REST url  – note this is not the same as the url in the above screen.  In addition to the specific report ID, you will also require your own user api key and the CiviCRM site api key. The below api keys are examples only.

http://civi.portal.rd.trc.nihr.ac.uk/bioresource/wp-content/plugins/civicrm/civicrm/extern/rest.php?entity=ReportTemplate&action=getrows&instance_id=94&options[limit]=500&api_key=hs876GFHryhUTgbatLTuFoP&key=085cb1607db3fb826f43458ed6c8h311&json=1

 

3. In Excel on the data tab,  click “new query” >  “from other sources”  >  “from web” to bring up the below url dialog

 

 

 

 

4. Paste in REST url and click OK which loads the below query editor

5. Click “List”

 

 

 

 

 

 

 

 

6. Click “To table”

 

 

 

 

 

 

 

 

 

 

7. Click highlighted left/right arrows, which show a list of all the report fields you have defined in your Civi report.

 

 

 

 

 

 

 

 

 

 

 

8. Transform any columns (duplicate, rename, filter etc)

9. Click “Close & Load to…”

10. Set to only create connection if you are going to pull everything off data model  or table to view data directly in worksheet

11. Click Load

 

 

 

 

 

 

 

 

15.  Once you have created your report you can refresh the data by clicking the the refresh icon on the query or tab. You can either “refresh all” or if you have multiple queries you may want to refresh them individually. If refreshing individually select the query in the queries pane and click the small refresh icon.