Connect to Business Object Webi report through RESTful request

Hi,

you need to post the logon string as a POST call (not GET).

To do that you need to use the Web.Content() with the content option (adding a content option to webcontent transforms the WebContent call into a POST call).

Also you need to have the logon string as a binary using the Text.ToBinary() function.

Username and password need to be hard coded in the xml logon string. Just replace the red text of the below xml code with your username, password and authentication type.

"<attrs>
<attr name=""userName"" type=""string""><username></attr>
<attr name=""password"" type=""string""><password></attr>
<attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secWinAD</attr>
</attrs>"

Look at my first post to see the complete query I used to get the logon token back from logon API.

To export the report content I used the following function:

Export latest instance as CSV = Web.Contents(baseUrl,[ Headers =[#"Content-type"="application/xml",#"X-SAP-LogonToken"=token,#"Accept"="text/csv"], RelativePath="raylight/v1/documents/"&#"Latest Instance ID"&"/reports/"&#"pageID"&"?]"])

with

baseUrl = http://server:6405/biprws

token = the result of the logon query (see my first post)

LatestInstanceID= ID of the document

pageID=ID of the report

And before that query is made I have another query browsing the document repository to find the latest instance of the report (scheduled to run every our on BO).

Hi,

My query is structured as follow (with critical variables in red) :

1) authentication phase:

body = Text.ToBinary("<attrs>
<attr name=""userName"" type=""string""><username></attr>
<attr name=""password"" type=""string""><password></attr>
<attr name=""auth"" type=""string"" possibilities=""secEnterprise,secLDAP,secWinAD,secSAPR3"">secWinAD</attr>
</attrs>"),
baseUrl= http://server:6405/biprws,
options = [
Headers =[#"Content-type"="application/xml",#"Accept"="application/json"],
Content=body
],
result = Web.Contents(baseUrl, options),
#"Imported JSON" = Json.Document(result,1252),
token = #"Imported JSON"[logonToken]

Now that we have retrieved a token we can get access SAP BO and retrieve the content of a Webi report

2) export report content as csv:

LatestInstanceID= ID of the document

pageID=ID of the report

Export latest instance as CSV = Web.Contents(baseUrl,[ Headers =[#"Content-type"="application/xml",#"X-SAP-LogonToken"=token,#"Accept"="text/csv"], RelativePath="raylight/v1/documents/"&#"Latest Instance ID"&"/reports/"&#"pageID"&"?]"])

The 2 issues I mentioned are solved as follow:

- unable to refresh on Power BI service when the logon token is retrieved through a function => simply integrate the authentication part of the query at the beginning of the query used to get the content of the report (no use of a separate function)

- Power BI services not accepting anonymous connection to web data source => used RevalivePath trick. Basically when Power BI services stores credentials for data sources it makes a test connection to the url provided in the query with the credentials given by the user. When Power BI service tries to access http://server:6405/biprws/raylight/v1/documents/etc. as anonymous the server will return an error => Power BI service will interpret this as incorrect credentials.

But if you use the RelativePath trick Power BI service will only test the baseurl http://server:6405/biprws and fortunately the server will return a different response to that url which will be accepted by Power BI service.

Hope this answers your questions !