Using the “Export to Spreadsheet” function to get merged data into individual columns
In this blog post I will show you a simple way to separate merged data before you even download the spreadsheet. This way you don’t need to use formulas to split the cells anymore.
By default, the “Export to Spreadsheet” button is enabled in list report tables. In the object page, the feature is available by default if the copy/paste feature is also available. Otherwise, the feature is disabled in the object page. When enabled, the ‘Export to Spreadsheet’ button is displayed in the table toolbar. It triggers the export of the values in the table.
This topic is currently only applicable to SAP Fiori elements for OData V4.
Sometimes, when exporting to excel requires formulas to separate data to be able to use it. This is because the standard export to excel does not split cells with multiple values. However there’s an option that allows you to split these mutiple values before downloading it. This is very useful, once that saves time and automates the process.
In order to get the merged data into the individual columns, please follow the steps below:
1. Click on the arrow down button next to the “Export to Spreadsheet” button.
2. Select “Export As…” from the drop-down menu.
3. Tick the “Split cells with multiple values” checkbox (when selecting this option, the exported spreadsheet has one column per value. If one column contains multiple values in a cell (for example, amount and currency/unit), this results in two separate columns in the spreadsheet.).
4. Confirm your selection by clicking on “Export” button.
The data from the app will be exported to the spreadsheet and the columns will be separated. You should be able to apply the filter on any individual column.
Note that, once you click in “Split cells with multiple values”, it will apply to all columns available for viewing in the app and some columns can be duplicated. In case you don’t want to have the description column duplicated in the spreadsheet, you can deactivate it in your personalized settings. You can do this by clicking on the “Settings” icon/button and deselecting corresponding check-box.
- The columns containing a
DataFieldForAnnotationthat points to
DataFieldForActionare not exported.
Hope this information is useful for you! I would greatly appreciate if you could share your feedbacks and thoughts in the comments.
Also, I encourage you to browse for other Community Topics that may be useful for you.
Thank you so much!
Can we default (Marked as X) checkbox for 'split cells with multiple values' while executing 'Export As'.
Similar to the screen shot mentioned in the blog.
Currently, the field "Split cells with multiple values" is available as an option that always need to be checked and it's not possible to set it as default.
However, this is really a valid request and you can register an enhancement request using the Customer Influence and Adopt site. You can check the following documentation:
How to: Enhancement Request Process using the Customer Influence and Adopt site
Do you know if something has been done regarding the default value 'X' for the 'split cells with multiple value'? I cannot find anyhing.
I believe that we don't have this feature request yet.
would the Influencing Opportunity SAP Application Development - Digital Experience the right place to file this request? I don't find any better fitting Opportunity. Also our customers are asking for the split being the default.
In my opinion, SAP S/4HANA Cloud for User Experience and Cross-Topics, public edition would fit better for this request, as we are talking about S/4HANA Cloud and UX.