Skip to content

Instantly share code, notes, and snippets.

@rmoff
Created August 19, 2015 10:00
Show Gist options
  • Save rmoff/ff76fb737185a88f3eb5 to your computer and use it in GitHub Desktop.
Save rmoff/ff76fb737185a88f3eb5 to your computer and use it in GitHub Desktop.
OBIEE executeXMLQuery web service example
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v8="urn://oracle.bi.webservices/v8">
<soapenv:Header/>
<soapenv:Body>
<v8:executeXMLQuery>
<v8:report>
<v8:reportPath>/users/prodney/foo/test_table</v8:reportPath>
</v8:report>
<v8:outputFormat>SAWRowsetSchemaAndData</v8:outputFormat>
<v8:executionOptions>
<v8:async>true</v8:async>
<v8:maxRowsPerPage>2</v8:maxRowsPerPage>
<v8:refresh>false</v8:refresh>
<v8:presentationInfo>false</v8:presentationInfo>
<v8:type>1</v8:type>
</v8:executionOptions>
<v8:sessionID>uns4s9pl0iu4cqi15o35limnt6gls6sislei4k2</v8:sessionID>
</v8:executeXMLQuery>
</soapenv:Body>
</soapenv:Envelope>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v8">
<soap:Body>
<sawsoap:executeXMLQueryResult>
<sawsoap:return xsi:type="sawsoap:QueryResults">
<sawsoap:rowset xsi:type="xsd:string"><![CDATA[<rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:complexType name="Row">
<xsd:sequence>
<xsd:element name="Column0" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Products&quot;.&quot;P4 Brand&quot;" saw-sql:displayFormula="&quot;Products&quot;.&quot;P4 Brand&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Products" saw-sql:columnHeading="P4 Brand" saw-sql:isDoubleColumn="false" saw-sql:columnID="c82a06bf2538cf599"/>
<xsd:element name="Column1" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Products&quot;.&quot;P3 LOB&quot;" saw-sql:displayFormula="&quot;Products&quot;.&quot;P3 LOB&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Products" saw-sql:columnHeading="P3 LOB" saw-sql:isDoubleColumn="false" saw-sql:columnID="ca06843a04126ce0f"/>
<xsd:element name="Column2" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Products&quot;.&quot;P2 Product Type&quot;" saw-sql:displayFormula="&quot;Products&quot;.&quot;P2 Product Type&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Products" saw-sql:columnHeading="P2 Product Type" saw-sql:isDoubleColumn="false" saw-sql:columnID="cd088fe0320f0511f"/>
<xsd:element name="Column3" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Base Facts&quot;.&quot;1- Revenue&quot;" saw-sql:displayFormula="&quot;Base Facts&quot;.&quot;1- Revenue&quot;" saw-sql:aggregationRule="sum" saw-sql:aggregationType="agg" saw-sql:tableHeading="Base Facts" saw-sql:columnHeading="1- Revenue" saw-sql:isDoubleColumn="false" saw-sql:columnID="c562e34b2159a5abb"/>
<xsd:element name="Column4" type="xsd:int" minOccurs="0" maxOccurs="1" saw-sql:type="integer" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Base Facts&quot;.&quot;2- Billed Quantity&quot;" saw-sql:displayFormula="&quot;Base Facts&quot;.&quot;2- Billed Quantity&quot;" saw-sql:aggregationRule="sum" saw-sql:aggregationType="agg" saw-sql:tableHeading="Base Facts" saw-sql:columnHeading="2- Billed Quantity" saw-sql:isDoubleColumn="false" saw-sql:columnID="c4f585740c6a30ee8"/>
<xsd:element name="Column5" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Base Facts&quot;.&quot;3- Discount Amount&quot;" saw-sql:displayFormula="&quot;Base Facts&quot;.&quot;3- Discount Amount&quot;" saw-sql:aggregationRule="sum" saw-sql:aggregationType="agg" saw-sql:tableHeading="Base Facts" saw-sql:columnHeading="3- Discount Amount" saw-sql:isDoubleColumn="false" saw-sql:columnID="ce982f2e5f5be0d83"/>
<xsd:element name="Column6" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Simple Calculations&quot;.&quot;17 Net Costs&quot;" saw-sql:displayFormula="&quot;Simple Calculations&quot;.&quot;17 Net Costs&quot;" saw-sql:aggregationRule="complex" saw-sql:aggregationType="agg" saw-sql:tableHeading="Simple Calculations" saw-sql:columnHeading="17 Net Costs" saw-sql:isDoubleColumn="false" saw-sql:columnID="c8b37ec10dac1fff9"/>
<xsd:element name="Column7" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Simple Calculations&quot;.&quot;20 Actual Unit Price&quot;" saw-sql:displayFormula="&quot;Simple Calculations&quot;.&quot;20 Actual Unit Price&quot;" saw-sql:aggregationRule="complex" saw-sql:aggregationType="agg" saw-sql:tableHeading="Simple Calculations" saw-sql:columnHeading="20 Actual Unit Price" saw-sql:isDoubleColumn="false" saw-sql:columnID="c17a0ae2d2c067768"/>
<xsd:element name="Column8" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;A - Sample Sales&quot;.&quot;Simple Calculations&quot;.&quot;21 Target Unit Price&quot;" saw-sql:displayFormula="&quot;Simple Calculations&quot;.&quot;21 Target Unit Price&quot;" saw-sql:aggregationRule="complex" saw-sql:aggregationType="agg" saw-sql:tableHeading="Simple Calculations" saw-sql:columnHeading="21 Target Unit Price" saw-sql:isDoubleColumn="false" saw-sql:columnID="ccb1d77554f5779d3"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<Row>
<Column0>BizTech</Column0>
<Column1>Communication</Column1>
<Column2>Cell Phones</Column2>
<Column3>6327819.47</Column3>
<Column4>478975</Column4>
<Column5>577852</Column5>
<Column6>6401339.77</Column6>
<Column7>13.2111685787358</Column7>
<Column8>4.13215339165108</Column8>
</Row>
<Row>
<Column0>BizTech</Column0>
<Column1>Communication</Column1>
<Column2>Smart Phones</Column2>
<Column3>6773120.36</Column3>
<Column4>633211</Column4>
<Column5>554515</Column5>
<Column6>6186827.76</Column6>
<Column7>10.6964666754052</Column7>
<Column8>3.5900343423311</Column8>
</Row>
</rowset>]]></sawsoap:rowset>
<sawsoap:queryID xsi:type="xsd:string">RSXS9_1</sawsoap:queryID>
<sawsoap:finished xsi:type="xsd:boolean">false</sawsoap:finished>
</sawsoap:return>
</sawsoap:executeXMLQueryResult>
</soap:Body>
</soap:Envelope>
@joaorion
Copy link

joaorion commented Sep 8, 2017

Hi, Ive seen a lot of examples in different forums but nobody explains how to use parameters to execute an analysis. For example if i need to pass the T05 Per Name Year of Sales Subject and a value of 2014. How can we do this? Thanks in advance.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment