Assign Data Source

[Former Member]

I want to create WebI report based on universe but getting error even after trying couple of things. Details as below:

XML:

<dataprovider>

    <id>DP2</id>

    <name>Query 1</name>

    <dataSourceId>12015</dataSourceId>

    <dictionary>

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

            <id>DP2.DO66</id>

            <name>City</name>

        </expression>

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

            <id>DP2.DO68</id>

            <name>Country</name>

        </expression>

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

            <id>DP2.DO5e</id>

            <name>Customer Name</name>

        </expression>

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

            <id>DP2.DOa3</id>

            <name>Order Date</name>

        </expression>

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

            <id>DP2.DO107</id>

            <name>Total Sales</name>

        </expression>

    </dictionary>

</dataprovider>

Code:

addDataProv = requests.post(url=rayLightUrl + '/documents/' + docId + '/dataproviders', data=dataProvXML, headers=headers1)

Error:

Can not build query or generate report

Any points will help.

[daniel_paulsen · Accepted solution]

Hi Anu,

try just adding the dataprovider first without specifying any of the objects

ie:

POST:  .../raylight/v1/documents/<docId>/dataproviders

request body:

<dataprovider>

      <name>Query 1</name>

      <dataSourceId>12015</dataSourceId>

</dataprovider>

then save the report:   PUT:  .../raylight/v1/documents/<docId>

and open the report in Launchpad to see that the dataprovider was added correctly.

The next step is to add resultObjects to the report page, using the query specification:

PUT  .../raylight/v1/documents/<docId>/dataproviders/<dataproviderID>/specification

use Content-Type: text/xml

request body:

<queryspec......

To get an idea of what the query specification should look like, GET it from a report that has a similar structure to what you are trying to create.  Note this workflow is the same as creating a report in the designer.  You add a dataprovider in one step, then add the reportObjects to the report canvas in the next step.

I hope this helps,

Dan

[Former Member · Accepted solution]

Hi Dan

Thanks for your inputs. I followed the steps and getting below success message:

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

<success>

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

    <id>16481</id>

</success>

But when I open the document in WebI, it gives error "Unknown Error in SL Service(WIS 00000)".

There is something wrong in query spec but I am unable to identify it. Below is the XML

<?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="0"/>

    <maxRowsRetrievedProperty activated="true" value="200"/>

    <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:QueryDataNode">

    <bOQuery name="Query">

      <resultObjects name="Customer Name"/>

      <resultObjects name="City"/>

      <resultObjects name="Country"/>

      <resultObjects name="Order Date"/>

      <resultObjects name="Total Sales"/>

      <conditionPart/>

    </bOQuery>

  </queriesTree>

</queryspec:QuerySpec>

[daniel_paulsen · Accepted solution]

I believe what's missing is the identifier of each result object.  you can get this info by retrieving the details of the dataprovider.

eg:

<resultObjects identifier=DP0.D05e name="Customer Name"/>

Dan

[Former Member · Accepted solution]

I tried reading another report data provider with similar result objects. Identifier looks diff what you mention. I tried using XML below and it gives me success message. But when I refresh and open report in WebI, it shows me data provider without any result objects.

Any pointers?

XML:

<?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" version="17" dataProviderId="DP2">

  <queryParameters>

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

    <maxRetrievalTimeInSecondsProperty value="0"/>

    <maxRowsRetrievedProperty activated="true" value="200"/>

    <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:QueryDataNode">

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

      <resultObjects identifier="_OlDDsBAUEeWGYc2RKrX5Ig" name="Customer Name"/>

      <resultObjects identifier="_OlMNoBAUEeWGYc2RKrX5Ig" name="City"/>

      <resultObjects identifier="_OlOC0BAUEeWGYc2RKrX5Ig" name="Country"/>

      <resultObjects identifier="_OmRLsBAUEeWGYc2RKrX5Ig" name="Order Date"/>

      <resultObjects identifier="_TT9NYBAVEeWGYc2RKrX5Ig" name="Total Sales"/>

      <conditionPart/>

    </bOQuery>

  </queriesTree>

</queryspec:QuerySpec>

[eric_festinger · Accepted solution]

hi Anu,

Result objects identifiers look differently depending on the type of data source they come from. Yours seem ok (even if apparently not from the same kind of data source as your original post).

"it shows me data provider without any result objects": do you mean the query panel is empty?

Regards,

eric

[Former Member · Accepted solution]

Yes. Query Panel is empty

[eric_festinger · Accepted solution]

Just to be sure: did you save the document?

[Former Member · Accepted solution]

Yes I did saved and it gave me success message after saving. But still Query Panel is empty.

[eric_festinger · Accepted solution]

May you:

1. Add an extra call "GET text/xml queryspec" after saving the document.
2. Cut/paste the calls and output of the whole workflow?

Thanks

[Former Member · Accepted solution]

Hi Eric,

Here it is....as you can see....query spec doesnt return any result objects although I am passing them in the request.

=> STEP 1: Create New Document

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

<success>

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

    <id>17230</id>

</success>

=> STEP  2: Add Data Provider - Universe

=> URL

http://IP:6405/biprws/raylight/v1/documents/17230/dataproviders

=> OUTPUT

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

<success>

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

    <id>DP0</id>

</success>

=> STEP 3: Add Query Specification

=> INPUT XML USED:

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

  <queryParameters>

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

    <maxRetrievalTimeInSecondsProperty value="0" />

    <maxRowsRetrievedProperty activated="true" value="200" />

    <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:QueryDataNode">

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

      <resultObjects identifier="_OlDDsBAUEeWGYc2RKrX5Ig" name="Customer Name" />

      <resultObjects identifier="_OlMNoBAUEeWGYc2RKrX5Ig" name="City" />

      <resultObjects identifier="_OlOC0BAUEeWGYc2RKrX5Ig" name="Country" />

      <resultObjects identifier="_OmRLsBAUEeWGYc2RKrX5Ig" name="Order Date" />

      <resultObjects identifier="_TT9NYBAVEeWGYc2RKrX5Ig" name="Total Sales" />

      <conditionPart />

    </bOQuery>

  </queriesTree>

</ns0:QuerySpec>

=> URL:

http://IP:6405/biprws/raylight/v1/documents/17230/dataproviders/DP0/specification

=> OUTPUT:

<?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>

=> STEP 4: Save Document

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

<success>

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

    <id>17230</id>

</success>

=> STEP 5: Get Query Specification

URL:

http://IP:6405/biprws/raylight/v1/documents/17230/dataproviders/DP0/specification

OUTPUT:

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

<queryspec:QuerySpec xmlns:queryspec="http://com.sap.sl.queryspec" dataProviderId="DP0">

  <queryParameters>

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

    <maxRetrievalTimeInSecondsProperty value="0"/>

    <maxRowsRetrievedProperty activated="true" value="200"/>

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

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

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

    <stripQueryProperty/>

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

  </queryParameters>

</queryspec:QuerySpec>

Call Made for Query Spec.txt.zip

[daniel_paulsen · Accepted solution]

Hi Anu,

That's odd that it came back empty.   Can you open the report in Launchpad, add the 5 resultObjects and save the report.  Then, with REST return the Query Specification to see what's different between the XML you're using to add the resultObjects and the XML returned from the manually updated report?

Dan

[eric_festinger · Accepted solution]

Anu,

There is a namespace mismatch in your query spec XML:

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

  <queryParameters>

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

    <maxRetrievalTimeInSecondsProperty value="0" />

    <maxRowsRetrievedProperty activated="true" value="200" />

    <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:QueryDataNode">

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

      <resultObjects identifier="_OlDDsBAUEeWGYc2RKrX5Ig" name="Customer Name" />

      <resultObjects identifier="_OlMNoBAUEeWGYc2RKrX5Ig" name="City" />

      <resultObjects identifier="_OlOC0BAUEeWGYc2RKrX5Ig" name="Country" />

      <resultObjects identifier="_OmRLsBAUEeWGYc2RKrX5Ig" name="Order Date" />

      <resultObjects identifier="_TT9NYBAVEeWGYc2RKrX5Ig" name="Total Sales" />

      <conditionPart />

    </bOQuery>

  </queriesTree>

</ns0:QuerySpec>

Can you try again... with the same everywhere?

eric

[Former Member · Accepted solution]

Hi Eric

You were spot on. The problem was with elementtree package I was using with Python. For some weird reason, it was replacing "queryspec:QuerySpec" with "ns0: queryspec:QuerySpec" when I read from XML.

I fixed that and now objects are visible in Query Panel.

Do I have to refresh the report(using web service) for the objects to be also visible under "Available Objects" ? OR I can directly proceed with creating report structure?

Regards

Anu

[eric_festinger · Accepted solution]

hi Anu,

... and great!

No need to refresh the report to see the objects in the 'available objects' panel of BILP or in the <dictionary> element of the "GET application/xml (or json) .../documents/{docId}/dataproviders/{dpId}'" call.

Regards,

eric