OTBI Analysis result via SOAP request/response with proper column names

Question

Hi, I am using OTBI and I have created an Analysis. This works great. Now I want fetch the result via a SOAP call. This also works fine. I use the XmlViewService and the executeXMLQuery operation and I get the result I expect. So far so good. This is the result I am getting. 100004 John Doe ]]> RSXS10_1 true The first column is the Person Number, the next is the First Name and the last column is the Last Name. Now what bugs me is that the columns are named Column0, Column1 and Column2. Is there a way to have this service output proper column names?

Riyaz Ali-Oracle · Answer

Try the below: * Remove Explicit Aliases from SQL Formula Since OTBI applies its own aliases, avoid using "Worker"."Person Number" AS "Person_Number"—instead, just use: sql "Worker"."Person Number" Then, rely on post-processing in your application to rename the columns. 2. Use BI Publisher Data Model Instead a) If you need explicit column names in the SOAP response, consider creating a BI Publisher Data Model rather than relying on OTBI’s default behavior. b) BI Publisher allows full control over XML output column names. 3. Modify XML SOAP Request (If Possible) xml This depends on whether OTBI respects such parameters. Post-Processing in Your Application If OTBI forces column names like saw_0, saw_1, etc., one workaround is handling renaming in your application code after retrieving the SOAP response. Note: Pleae try below if the above steps does not work: Try removing explicit aliases first and rerunning the query. If SOAP still returns generic column names, we can explore the BI Publisher route or XML modifications.

marcelhoffs · Answer

The aliasses approach does not work for me. So I changed the formula for one of the columns to this: "Worker"."Person Number" AS "Person_Number" It seems to already apply an alias to the column, namely saw_0. Now with this modification you essentially generate an invalid SQL and I can't run the report anymore. Error getting drill information: SELECT "Worker"."Person Number" AS "Person_Number" saw_0, "Worker"."Employee First Name" saw_1, "Worker"."Employee Last Name" saw_2 FROM "Workforce Management - Person Real Time" WHERE "Worker"."Person Number" IN ('P_PERSON_NUMBER')

Riyaz Ali-Oracle · Answer

Steps to Set Column Aliases:

Navigate to the Criteria Tab

Open your OTBI Analysis and go to the Criteria section.

Modify the Column Formula

Click on the fx (Formula) button for the column you want to rename.

Use the AS keyword to define an alias.

Example:

sql

"Person"."Person Number" AS "Person_Number"

Save and Run the Report

Ensure the alias names appear correctly before executing the SOAP request.

Alternative Approaches

If SOAP still returns generic column names, you may need to adjust the BI Publisher Data Model, explicitly defining alias names in the XML template.

Another workaround is mapping the column names within your application after retrieving the SOAP response.