Are books easy to build?
A common question I get asked is how easy is it to make a Book in OneStream? A joke among consultants is the answer is always: it depends. The answer is no different here.
But let me back up for a minute for anyone less familiar with the platform who is wondering what is a book? A book is one way of combining multiple Cube Views or Chart/Report Components and dimension combinations into a distributable reporting package. You can create this as a PDF or Excel export. There are many avenues you can go with this. You could have the same report export multiple times with different pages for different entities/UD, you could have several different reports with the same point of view on different pages, or some combination of the two.
I have seen very simple requests where a client wanted a Balance Sheet report produced for each legal entity. I have also seen complex requests where a client wanted a package to run for a list of cost centers within an entity and then have each page saved within a drive as a separate file.
When would I build a book?
The building blocks of a book like so many other things in OneStream are Cube Views, Dashboard Reports or Charts. [BT1] Typically, by the time you go to build your book you will have already created and nicely formatted the reports that you want to include. The book can be seen as the packaging with a bow that wraps it up nicely for end users. [BT2]
A Simple Example
If you are unfamiliar with where to access the Book Designer screen, it is going to be within the Application tab under Presentation.
To create a new book click the Create New Book button, then use the Save button to ‘Save As File in FX File System’. If you want other users to be able to access save within a Public folder; if you want to keep it private while developing you can save in your User folder.
When saving you will notice the default is xfDoc.pdfBook
Sometimes you will want both, I suggest always building in the PDFbook format and then changing to an xlbook [BT4] at the end because the PDF view gives you the option to preview within the app as you build. If you use the xlbook extension you do not have this preview ability and will have to export the book every time to see changes.
One other option when it comes to saving is a zipbook. This will result in each page of your file being saved as a separate file within a larger zipped file. This method is compatible with both Excel Export and Report items (described more below).
Most books will have at least one of each of the following item components: a Loop, an item to Change Parameters, and a Report. There are other items allowing If, Else If, and Else statements that we won’t use in our example today, but you should be aware of. A ‘If Statement’ sets a condition under which an action takes place, ‘Else If’ sets a new if statement if the first condition fails, and ‘Else’ contains an action to take place in all other conditions.
When building a PDF you will select Add Report. If you were building an Excel file you would use the Excel Export Item. In this example we are going to look at a PDF build. At the end we will talk about how to quickly convert to an Excel book.
A Book in its Simplest Form
In its most basic form a Book will need a report. To add this item, use the dropdown next to the add item button. For this we will select Add Report.
You have the option to add a Cube View, Dashboard Chart, or Dashboard Report. For this example we are going to stick to the Cube View default.
On the ‘Cube View or Component Name’ line use the ellipsis to navigate to report options and select your Cube View.
In order to have the Preview window not grayed out save your changes. When you hit Preview you will be prompted for any Parameters you have in your report and after making selections you will see a preview of your report.
You can see my header reflects the selections from my prompts and that my document is only one page long. This is the same effect as running from a Cube View and choosing the PDF export option so likely not the end result we are looking for.
My First Loop
Now that we know our report is rendering in the document we probably want to add some additional functionality. In order to run this report for a list of entities rather than just one we will add a ‘Loop’ and an ‘Item To Change Parameters’.
Use the arrow keys to arrange them as shown below with the Change Parameters and Report being below and indented to the right of the Loop. This is important because if you add additional loops or reports later it will determine which reports will run under this loop.
For our Loop we will want to input a list or member filter that the report will run for. Since I know I want to pull for a list of entities I am going to select Member Filter and MainEntityDim, select whatever Dimensions you will be running for.
For my loop definition I have selected E#TotalCompany.TreeDescendantsInclusive to create[BT5] a page for each entity. You can use your member filter builder to create a definition that suits[BT6] your needs.
The second step here is on the Change Parameters item to update Change Parameters to True and set the Parameter Value equal to the Loop 1 Variable. Make sure your syntax is exactly as shown below. Note, the parameter does not get pipes here.
Essentially this is telling the Book that anywhere the Parameter Param_E_All appears replace it with the Loop1 Variable we defined above.
Another setting to take note of is the Change POV. If your Cube View doesn’t have a Parameter already set up for the item you want to Loop through you can set Change POV to true instead and set your dimension filter equal to the Loop as seen below:
Both options above will result in the same output. The only difference is if a Parameter is used in your Cube View or not. Now when you run a preview you will notice we have 11 pages of book content, one for each entity within our entity tree.
Converting to Excel
If I want to be able to run this report in Excel as well as a PDF, I can use the save as function to save this as a version with an xl[BT7] book extension. Once I do that, I am going to add an ‘Excel Export Item’.
Once I have this item, I just need to copy the Cube View name from the Report item.
As mentioned before the preview button is now grayed out so to run this I need to navigate to my File Explorer, find the folder it is saved in, and double click on the report to run.
This will open an Excel file. You will notice the tabs are named with the report name repeated.
If you want to give your tabs a more meaningful name go [BT8] back to your book designer and we can add something to the ‘Output Name’ line. I have chosen to add the Loop1DisplayVariable. This will put the name of the dimension for the relevant item on the tab.
Now when I run you can see my entity names have replaced the names above.
Had you wanted your entity names instead of descriptions you could have used Loop1Variable instead.
This is just a starter example of what you can do with OneStream books. You can add as many reports and filters as you want to create a comprehensive reporting package. Once this is built there are many distribution options. You can attach to a button within a Dashboard or use Parcel Service and Task Scheduler to send out to end users creating a dynamic month end reporting package and cutting out the hours wasted each month pulling together and sending out the same reports!