Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
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
Answers
-
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.
2 -
Hello Nathan, thank you for that suggestion, will try it :)
0 -
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 N1 -
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.
1 -
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:
Thanks.
0 -
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?
0 -
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.
0 -
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 N1 -
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.
0 -
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 Support1
