Can you Procedurally Export to Excel?

wdeer · 2025-02-10T22:38:18+00:00

There was an error rendering this rich post.

We have a budget cube that is loaded to via the smart import object. After a bit of transformation on our end, we want our users to have the ability to export this data in xlsx format (same format they initially loaded to in the smart import object) for their own purposes. We would like for there to be an interactive selection on version so they can choose which budget version to export.

Is there a way to do this via procedure? I've down this before with extracting cubes but it generates a txt file and drops it in Azure. While it's close to what we want to do, we need the data in xlsx format and downloaded directly to the user's desktop.

Find more posts tagged with

Procedures

Accepted answers

Helmut Heimann

Hi @wdeer ,

there once was an option to update an existing XLS file on the server (not the desktop). This doesn't exist anymore.

The only way to achieve that by procedure would be to export the data to a text file and let it be converted into XLS by a script. But that would mean that said script would have to sit on the server in a cloud environment, which probably is not possible—and, the file would still be on the server; you'd need a broadcasting action to send it to a user.

My advice is to let the user apply their selections and export the data to an XLS file manually via the context menu

I know that's not the solution you were hoping for but it's the most pragmatic way.

Another possibility might be using the Microsoft API. We have a how-to paper that describes how to use the API to interact with MS Teams. Adapting that to convert a text file to XLS would require some effort and I'm not sure if it can be done—just a hint to explore. You can find the paper here

https://community.board.com/discussion/16636/how-to-send-notifications-integration-with-ms-teams

You can, of course, create a new idea regarding this enhancement ;-)

Best,
Helmut

Lorenzo Aiani

Hi @wdeer ,

We faced a similar challenge in one of our projects: providing users with a pre-filled template (using the "smart import structure") containing data and making it available for download in Excel format directly on their PCs.

While we acknowledged the maintenance limitations of this approach, we opted to use the capsule procedure step "Export screen data to xlsx." Here’s a summary of how we implemented it:

Create a dedicated screen: We created a separate screen specifically for this purpose, to be used in the extract screen step. This screen is not accessible through standard navigation for users.
Layout setup: The export screen contained only the necessary layout for extraction. Within this layout, we included:
1. A copy of the cube used in smart import, with the additional "user" entity (applying the custom security script Select user=@user).
2. A design structure that mirrors the smart import format.
Data Extraction Procedure: We developed a procedure that follows these steps:
1. Extracts data from the planning cubes.
2. Reloads the data into the copy cubes. (it can be a dataflow, depends on your needs and cubes structures)
3. Runs the "Export screen data to xlsx" step, pointing to the export screen.

We included the User entity in our cubes because the "Export screen data to xlsx" step is executed without actually opening the screen (since we do not use a "Go to screen"). As a result, there is no way to apply a selection to that screen before extraction. So in the extract and reload we are sure to fill the cubes and then the layout with only desired data and we the User entity we can have concurrent users running the procedures. We configured dynamic selections within the screen based on cubes content, without any selection it won't work . If additional fixed or dynamic selections are available, they can be applied as well.

We knew, and I think you can easily understand, that this solution has some limitations, such as handling entities by rows, and it may not be efficient for large datasets. Ideally, I would have preferred to use Flex Grid to structure the view for smart import, but at the time, we were working with version 12.6, where this was not an option. So this, combined with the explicit requirement to have one clickable button that trigger the export in users' download folder we had only this possibility.

I hope this was clear (and hopefully not too late for your project). If you need any further details or clarification, feel free to reach out.

Regards,

Lorenzo

All comments

Helmut Heimann

Hi @wdeer ,

there once was an option to update an existing XLS file on the server (not the desktop). This doesn't exist anymore.

My advice is to let the user apply their selections and export the data to an XLS file manually via the context menu

I know that's not the solution you were hoping for but it's the most pragmatic way.

https://community.board.com/discussion/16636/how-to-send-notifications-integration-with-ms-teams

You can, of course, create a new idea regarding this enhancement ;-)

Best,
Helmut

wdeer

Thank you, Helmut. The closest I was able to get is export dataset procedure and it's exactly how I want the file (except for being csv instead of xls but close enough), but it still stores it in Azure. I like your suggestions but agree it's probably not best to over engineer it. I'll proceed with the context menu idea. Have a good one!

Lorenzo Aiani

Hi @wdeer ,

While we acknowledged the maintenance limitations of this approach, we opted to use the capsule procedure step "Export screen data to xlsx." Here’s a summary of how we implemented it:

Create a dedicated screen: We created a separate screen specifically for this purpose, to be used in the extract screen step. This screen is not accessible through standard navigation for users.
Layout setup: The export screen contained only the necessary layout for extraction. Within this layout, we included:
1. A copy of the cube used in smart import, with the additional "user" entity (applying the custom security script Select user=@user).
2. A design structure that mirrors the smart import format.
Data Extraction Procedure: We developed a procedure that follows these steps:
1. Extracts data from the planning cubes.
2. Reloads the data into the copy cubes. (it can be a dataflow, depends on your needs and cubes structures)
3. Runs the "Export screen data to xlsx" step, pointing to the export screen.

I hope this was clear (and hopefully not too late for your project). If you need any further details or clarification, feel free to reach out.

Regards,

Lorenzo

wdeer

Thank you, Lorenzo. This is very helpful.