mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

How to retrieve data from TSQLRestserver from Excel Power Query?

We are investigating the possibilities to use Excel 2013 Power Query (and Pivot, Map etc) as the reporting and dashboard tool for our applications data.
Main aspect of course is how to retrieve the data made available by our server application via a TSQLRest service and store it in the Excel data model, maybe even share it via Office365. Another challenge would be to find a way to write changing parameters from the Excel sheet PowerQueries into our

Using TestProject04 we have been able to retrieve data from the HTTP server using power queries "From Web" function and normal URLs like http://localhost:8080/root/SampleRecord but this seem to only work when authentication is disabled. Can this method be used when authentication is enabled?

Using ODATA would be another possibility, but reading the posts on this subject on this forum I understand that AB is not a great supporter of that "standard"

Anybody any tips on how to accomplish this?

Thanks
Bas

Last edited by Bascy (2014-01-07 10:58:41)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: How to retrieve data from TSQLRestserver from Excel Power Query?

I suppose you have to ways of accessing Excel:
- Via Ole Automation;
- Using its OleDB connector.

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: How to retrieve data from TSQLRestserver from Excel Power Query?

Guess you misunderstood my question. We are trying to get the data from a Mormot TSQLRestServer into Excel 2013, and then use Excel PowerQuery and powerView to analyze the data report on it