Oracle Communities

Oracle Analytics and AI

Child Item

How to retain column order when exporting a report in OTBI?

Hi,

Is there a way that I can retain the report's column order when exporting in CSV file? I have been researching and all I can see was it is not supported by the OTBI application, and they are suggesting to just export it via Excel, but I have a requirement that it should be in CSV format because it will be used in Power BI application.

I hope that you can help me.

Sample scenario:

Exported via Excel

Account Number Record Number Account Name

Exported via CSV

Record Number Account Name Account Number

Find more posts tagged with

Report

Export

OTBI

Accepted answers

All comments

Nathan CCC

Hi, In CSV and XML the order of the columns is the order in your data model. You can drag and drop up and down on the diagram. There is also an option in data model properties to include exclude empty columns.

Ivony Ann Paula Salango

Hello Nathan, thank you for that suggestion, will try it :)

Dimple-Nagesh-Support-Oracle

Hi Ivony Ann Paula Salango,

Excel takes the column order from the report layout, which was modified.

CSV takes the column order from SQL statement order in the data model.

Edit the Data Model > Properties > Select Include Null Value Tags > Click OK and view the data

Test and let me know if that helps.

Thanks,
Dimple N

Nathan CCC

Hi, No CSV does not take the order from the SQL. It starts out at first in that order by default when you first create the data set from the sql but then you can change the order in the data model in diagram without updating the order in the SQL. And when you update the sql any new cols go to the bottom of diagram (far right of CSV) even if you put them in the middle in the sql.

MandeepGupta

Hi @Ivony Ann Paula Salango ,

Is your question around OTBI analysis or a data model based BIP?

If this is related to OTBI, please check the response on below thread:

OTBI Analysis rearranging Column Order different in CVS and EXCEL — Cloud Customer Connect (oracle.com)

Thanks.

Ivony Ann Paula Salango

Hello @MandeepGupta, this is actually for data model.

I tried the suggestion from @Nathan CCC, but I encountered an issue. The scenario is, for this data model, we have 4 groups. Now, the other columns are from the other group of data model, and I am not able to rearrange it based on the preferred output. Is there any workaround for this?

Ivony Ann Paula Salango

Hello @Dimple-Nagesh-Support-Oracle,

Could you please expand on this?

"Excel takes the column order from the report layout, which was modified."

I actually can't find any documentation that explains the behavior of excel output from data model.

Dimple-Nagesh-Support-Oracle

Hi Ivony Ann Paula Salango,

Below is the doc : Why BIP columns export order in CSV is different than export to Excel? (Doc ID 2630644.1)
Four datasets should not be an issue.

For eg : if your query is select col1, col2, col3, col4, col5, col6 from table and col3 is a clob
In the above check for the datatype, issue happens only when the column is CLOB, in that case try
CAST(col3 as VARCHAR(2000)) and save it > test the flow.

This should correct the alignment in CSV(if there are no NULL values in the column).
If there are NULL values → It is expected to shift the column to the end.
If issue still persists, I recommend to raise the SR.

Thanks,
Dimple N

Ivony Ann Paula Salango

Hello All,

Thank you for your help. I managed to solve my issues. I just have 1 more problem, is there any property that can exclude a field to be exported via CSV?

The thing is, there are IDs that cannot be unselected because they connect the groups of data model.

Dimple-Nagesh-Support-Oracle

Hi Ivony Ann Paula Salango,

Are you saying you want to hide few columns having ID's displayed in the output values only while exporting to .CSV file but the column exists in the report output?
If my understanding is correct, there is no such option right now, request you to raise an IDEA in IDEA labs.

Hope this clarifies.
Thanks,
Dimple N
Oracle Support