Creating report from scratch using RESTful sdk

[former_member204024]

Hi,

I am trying to create report from scratch and add a dataprovider based on universe with query filter.  Can you please guide me to acheive this.  I am on 4.1.

Regards,

Subramanian S.

[Former Member · Accepted solution]

Hi Subramanium,

You have to follow a sequential steps for creating Web intelligence document from scratch through RESTful.

Following steps are example for creating Webi document using eFashion universe & are very elementary. You have to understand the XML manipulation for defining reports element as well to create complex webi document using REST SDKs.

Creating a blank Webi document :-

URL : http://<servername>:6405/biprws/raylight/v1/documents

Method: POST

Request Body :

<document>

<name>SampleTestREST</name>

<folderId>298933</folderId>

</document>

Response Body should be like below:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<success>

    <message>The resource of type 'Document' with identifier '331905' has been successfully created.</message>

    <id>331905</id>

</success>

Adding Data Provider to your newly created webi document :-

URL: http://<servername>:6405/biprws/raylight/v1/documents/331905/dataproviders

Method: POST

Request Body:

<dataprovider>

  <name>TesteFashion</name>

<dataSourceId>5101</dataSourceId>

</dataprovider>

Response Body should be like this:

<success>

<message>The resource of type "Data provider" with identifier "DP0" has been successfully created.</message>

<id>DP0</id>

</success>

Updating query specification (Adding Objects to your Data Provider) :-

URL : http://<servername>:6405/biprws/raylight/v1/documents/331905/dataproviders/DP0/specification

Method: PUT

Request Body:

<?xml version="1.0" encoding="UTF-8"?>

<queryspec:QuerySpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:queryspec="http://com.sap.sl.queryspec" dataProviderId="DP0">

  <queryParameters>

    <duplicatedRowsProperty activated="true" value="true"/>

    <maxRetrievalTimeInSecondsProperty value="300"/>

    <maxRowsRetrievedProperty value="90000"/>

    <removeEmptyRowsProperty activated="true" value="true"/>

    <allowOtherUserToEditQueryProperty activated="true" value="true"/>

    <resetContextOnRefreshProperty activated="true" value="true"/>

    <stripQueryProperty/>

    <useBexQueryDefaultValue activated="true" value="true"/>

  </queryParameters>

  <queriesTree xsi:type="queryspec:QueryOperatorNode" queryOperator="Union">

    <children xsi:type="queryspec:QueryDataNode">

      <bOQuery name="Query" identifier="_MRZYYDklEeaHfaYJlHKz4Q">

        <resultObjects identifier="DS0.DObc" name="Year"/>

        <conditionPart/>

      </bOQuery>

    </children>

  </queriesTree>

  <propertyBag key="DUPLICATED_ROWS_UNDEFINED_VALUE" value="-1"/>

  <propertyBag key="RESET_CONTEXT_ON_REFRESH_UNDEFINED_VALUE" value="-1"/>

  <propertyBag key="ALLOW_THE_USER_TO_EDIT_QUERY_UNDEFINED_VALUE" value="-1"/>

</queryspec:QuerySpec>

Response should be like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<success>

    <message>The resource of type 'Data provider' with identifier 'DP0' has been successfully updated.</message>

    <id>DP0</id>

</success>

Saving document state :-

URL : http://<servername>:6405/biprws/raylight/v1/documents/331905

Method: PUT

Request Body:   <EMPTY>

Response Body should be like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<success>

    <message>The resource of type 'Document' with identifier '331905' has been successfully updated.</message>

    <id>331905</id>

</success>

I have tried the above steps on REST Client. For detailed documentation, you can refer the RESTful Web services Developer guide from SAP's Help portal. You also have to understand the XML structure for creating Webi Report Elements (i.e. Cell (only free cells, not cells in a table), Section, Table (VTable, HTable, XTable, and Form), Visualization (charts) from Section: 8.4 Managing Report Elements of the linked developer guide.

Hope above steps helps you to give a kick start for your requirement.

Thanks,

Shailendra

[Former Member]

I am able to get the issue resolved. Thanks for your help.

[Former Member]

Sorry, I think I mislead from URL as I pasted for the post.

I post the payload without DP0. It was

Post : documents/9508/dataproviders

I tried fresh, but getting the same issue.

Any clue if this is related to parameters I am sending or might be some server thing ?

Any idea how I can debug that ?

<error>

<error_code>

WSR 00102

</error_code>

<message>

Illegal argument ("while trying to invoke the method com.sap.sl.queryspec.QuerySpec.getQueries() of a null object loaded from local variable 'querySpec'")

</message>

</error>

[Former Member]

Hi Santhanam,

this is really great, but when i try to replicate the same steps I am getting following error.

<error>

<error_code>

WSR 00102</error_code>

<message>

Illegal argument ("while trying to invoke the method com.sap.sl.queryspec.QuerySpec.getQueries() of a null object loaded from local variable 'querySpec'")

</message>

</error>

So what I did was,

I create a document, then added a data provider and put on specification with :

URL : biprws/raylight/v1/documents/9486/dataproviders/DP1/specification

Payload :

<queryspec:QuerySpec dataProviderId="DP1">

<queryParameters>

<duplicatedRowsProperty activated="true" value="true" />

<maxRetrievalTimeInSecondsProperty value="300" />

<maxRowsRetrievedProperty value="90000" />

<removeEmptyRowsProperty activated="true" value="true" />

<allowOtherUserToEditQueryProperty activated="true" value="true" />

<resetContextOnRefreshProperty activated="true" value="true" />

<stripQueryProperty />

<useBexQueryDefaultValue activated="true" value="true" />

</queryParameters>

<queriesTree xsi:type="queryspec:QueryOperatorNode" queryOperator="Union">

<children xsi:type="queryspec:QueryDataNode">

<bOQuery name="Query" identifier="_Y_xTkEqsEea-vo0a6XgtoA">

<resultObjects identifier="DS0.DObc" name="Year" />

<resultObjects identifier="DS0.DOb9" name="Month" />

<conditionPart />

</bOQuery>

</children>

</queriesTree>

<propertyBag key="DUPLICATED_ROWS_UNDEFINED_VALUE" value="-1" />

<propertyBag key="RESET_CONTEXT_ON_REFRESH_UNDEFINED_VALUE" value="-1" />

<propertyBag key="ALLOW_THE_USER_TO_EDIT_QUERY_UNDEFINED_VALUE" value="-1" />

</queryspec:QuerySpec>

I am badly stuck there. and not able to identify how to generate

<bOQuery name="Query" identifier="_Y_xTkEqsEea-vo0a6XgtoA">

Urgent reply will be appreciated.

[daniel_paulsen]

Hi Vishal,

I'll try to give a quick reply (ie: suggestions without knowing for sure).
I think if you specify an identifier for the bOQuery, it will be used.  I'm not convinced this is required and its possible that if you do not specify it (it may be optional), then one will be generated (the idea being that the identifier is unique within the document).

I'm not sure this is the cause of your error unless you are adding multiple DP's and using the same Identifier.  If this is a new report created with the RESTful APIs, your new dataprovider should be DP0 and not DP1.

Hopefully this helps in guiding what is causing the error.  I don't see any glaring problems with your queryspec, but at least try using DP0 if this is a newly created document.

Dan

[Former Member]

Thanks Danial.

DP0 is my manually created data provider. I am trying to create DP1 as similar query but automatically. I tried doing the same with fresh report also. but no luck.

I am creating the data provider with full payload with data dictionary and Query tags. BUt when I do get I get the following only, I am assuming this might be the issue as it is not able to find the SQL I am specifying in the below payload.

I tried this with new report with fresh data provider. :

Payload I send for creating a data provider :

Post : documents/9508/dataproviders/DP0

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<dataprovider>

<name>Query 1</name>

<dataSourceId>5291</dataSourceId>

<dataSourcePrefix>DS0</dataSourcePrefix>

<dataSourceType>unv</dataSourceType>

<duration>2</duration>

<isPartial>false</isPartial>

<rowCount>1982</rowCount>

<flowCount>1</flowCount>

<dictionary>

<expression dataType="Numeric" qualification="Dimension">

<id>DP0.DOb9</id>

<name>Month</name>

<description>Month number in year, 1-12.</description>

<dataSourceObjectId>DS0.DOb9</dataSourceObjectId>

<formulaLanguageId>[Month]</formulaLanguageId>

</expression>

<expression dataType="String" qualification="Dimension">

<id>DP0.DObc</id>

<name>Year</name>

<description>Year 1999 - 2001.</description>

<dataSourceObjectId>DS0.DObc</dataSourceObjectId>

<formulaLanguageId>[Year]</formulaLanguageId>

</expression>

</dictionary>

<query>SELECT Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Yr, Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Mth FROM Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma</query>

</dataprovider>

what I get on doing GET with : documents/9508/dataproviders/DP0:

<dataprovider>

<id>DP1</id>

<name>Query 1</name>

<dataSourceId>5291</dataSourceId>

<dataSourcePrefix>DS0</dataSourcePrefix>

<dataSourceType>unv</dataSourceType>

<duration>0</duration>

<isPartial>false</isPartial>

<rowCount>0</rowCount>

</dataprovider>

[Former Member]

Much appreciate any response on this.

[daniel_paulsen]

when adding a dataprovider, don't specify the dataprovider ID on the URL.  simply use:

POST  /documents/9508/dataproviders

The response will have the new DP Id.  Then send the Specification to this DP

Once you've updated the specification, the query should show when calling:

GET  /documents/9508/dataproviders/DP0  and the correct DP ID should show up as well

Dan

[former_member197386]

Hi Subramanian,

Basically, you have to follow the same scenario as the one you would follow doing this through regular Webi UI.

I mean:

- create an empty document

- add a dataprovider (based on the datasource you wish)

- refresh data

- create a report

- add report elements...

- save it to CMS

Consider using the latest SP of 4.1 codeline (or even swith to 4.2 with a lot of new features )

Regards,

Anthony