Build OneStream Books with Entity Loops

OneStream Books entity loop for multi-entity reporting

As noted in the previous blog, OneStream books are an excellent way to deliver reporting packages to your users.

Also, in our previous blog, we looked at the basics of creating a book, including organizing books into folders, and created a book that ran several reports for one entity. We will now review how to create a book with one report repeating for several entities.

In our example today, our regional controllers would like to be able to pull the Income Statement Trend report (which is the cube view name Income_Statement Trend_Select) for their region parent and include all the children entities comprising their region. This is done by ensuring the parameter ParamEntity, is used in the cube view and the book we create.

To do this, we will create our book using the cube view Income_Statement Trend_Select). For the Output Name, we will use the parameter ParamEntity to include the entity’s description on the tab. Next, the first Excel Export Item is set up for the top parent entity:

OneStream Books step 1: entity loop configuration

As you know from my previous blog, I like to save the book before continuing to develop it. If you are interrupted or need to walk away, you haven’t lost all your work should the application time out.

OneStream Books step 2: entity loop configuration

We will save this as an Excel book:

OneStream Books step 3: entity loop configuration

As before, once I have saved the book, I like to update the Description and Access/Maintenance Groups as needed:
OneStream Books step 4: entity loop configuration
OneStream Books step 5: entity loop configuration
OneStream Books step 6: entity loop configuration

Now that the book is saved, the next step is to create a Loop. This is necessary for the book to Loop through all the children entities under the parent entity we selected.

To add a loop:

OneStream Books step 7: entity loop configuration

The Loop Type, Loop Definition, and Dimension must all be populated in the Loop. In this example, I am using the parameter ParamEntity and running the cube view for all the children below the parent entity selected:

OneStream Books step 8: entity loop configuration

After adding the Loop, you have several options. In this example, we will use Add Item to Change Parameters:
OneStream Books step 9: entity loop configuration

Make sure to update the Parameter Values to use your parameter:
OneStream Books step 10: entity loop configuration

Now that you have created the Loop and defined that you are changing parameters, you must add the type of file you wish to export. In this case, we will again export an Excel file:

OneStream Books step 11: entity loop configuration

We will define this Excel Export Item the same as the initial Excel Export Item we added to the book, but this time it is used for the children entities of the parent:

OneStream Books step 12: entity loop configuration

After adding this last piece and saving the book, it is ready to run:

OneStream Books step 13: entity loop configuration

In our example, we will select the parent entity Houston:

OneStream Books step 14: entity loop configuration
OneStream Books step 15: entity loop configuration

There are no more parameters to select, so we receive the following message:

OneStream Books step 16: entity loop configuration

The Excel file will contain the parent entity, Houston, and its two children as three separate tabs in the same file:

OneStream Books step 17: entity loop configuration
OneStream Books step 18: entity loop configuration

If you wish to run this report but need to have each entity export as a separate file, you can do this by saving the book as a Zip book:

OneStream Books step 19: entity loop configuration

The zip file will automatically open after the execution completes:

OneStream Books step 20: entity loop configuration

All spreadsheets are contained in a zip file:

OneStream Books step 21: entity loop configuration

OneStream Books Resources and Next Steps

OneStream Books can repeat one trusted cube view across a parent and its child entities. Keep the loop definition, dimension, and ParamEntity values aligned, then use descriptive output names so every Excel tab is easy to identify. Test OneStream Books with a small hierarchy first, confirm security, and save during development to reduce rework.

For more report book guidance, explore OneStream Books Part 1, what OneStream is, OneStream financial reporting, OneStream implementation, managed services for OneStream. Also review the official OneStream report books documentation.