Skip to main content


Setup Default Column Set for Guided Reporting

A group of diverse coworkers working on the computer together

The Guided Reporting solution is used to provide business users with an intuitive and easy-to-use report viewing experience. This solution provides a framework for integrating customer data content into a report viewer workspace that is organized around the hierarchical Entity structure of an organization. In addition, reports are tailored to the data available for the specific Entity selected by the business user.

Keywords used in this post and their definition:

  • Row Set: Unique set of rows for each report is termed a Row Set
  • Column Set: Unique set of columns for each report is termed a Column Set
  • Report: A combination of Row and Column sets generates a report
  • Guided Reporting: Promotes the creation of independent Row and Column sets, which can then be cherry-picked to showcase different variations of a report

With a Marketplace solution as dynamic as the Guided Reporting, the possibilities to render different versions of the same report are endless, with the Total number of potential reports = Total number of Row Sets x Total number of Column Sets. It was during its implementation for one of Perficient’s clients that we realized the following:

  • User needs to remember which column set helps generate their standard management reporting view
  • It can be time-consuming having to navigate through these column sets to attain that report

While the increase in variations leads to better reporting capabilities, it also exacerbates the problems mentioned above.

As a solution, we decided to introduce a feature that helps set a default column set for each report. This default can either be the FP&A team’s preference or be driven by internal standard reporting guidelines. When Default is selected for any report, the pre-set report design gets executed immediately, saving time and improving productivity. Alternatively, the user can always toggle through the other Column sets to generate a reporting view that best suits their needs. Let’s dive into this with the following design details:

Note: This content assumes that Guided Reporting is already installed in your environment and operational

1. Creating a Cube View:

Default Cube View

Cols_Default_IMGN_GRTT2 is a column set that can have basic configurations such as T#WF. It’s a dummy whose only purpose is to redirect Default requests to another set. In case a Default hasn’t been set up for any report, it will showcase the Workflow Year in this case based on our T#WF value

2. Setting up the Variable:

Once a reporting content cube view has been prepared, there is a variable setup to reference the dynamic Column templates and a fixed value that points to the report’s row template.

Variable Setup

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

The ColumnSet_GRT2 variable is a bound list prepared from the stored column template (cube view) values in the Guided Reporting Administration table. Upon user selection, the Column Template name is passed to this variable, which then prepares the cube view/report based on it.

Dashboard Config

Column definition in GRT Administration Table

Column definition in GRT Administration Table

This is the original solution design for Guided Reporting.

To enable setting Defaults, we introduced another variable called ColumnSet_Dyn_GRT2 which consumes the ColumnSet_GRT2 variable, processes it using XFBR and returns the Default for the selected report

Proposed Variable Setup

and updated the Report Content Cube View with the new variable

Proposed Cube View Update

3. Code:

XFBR is used to read the user selection (passed to variable Columnset_GRT2), check which Row/Report dashboard has been executed, check if the Default column template (cube view) has been selected, and accordingly return the preferred Default or the user-selected one (Columnset_GRT2) back to the ColumnSet_Dyn_GRT2 variable in the report cube view

Code Snippet

4. Execution:

Revised GRT Columns

The description field in the Guided Reporting Administration table represents the label for every column template that would be visible to the end user for selection. In the above screenshot, Default has been added as the first available selection, with Cols_Default_IMGN_GRTT2 created in step 1 as its tagged cube view. With that, when the Guided Reporting dashboard is opened, the following is the view available to the user when the column drop-down is clicked upon:

Default Column Set

If the configurations have been done so far as per this post, selecting Default in combination with the Row set will generate different reports based on the XFBR code evaluation.

Row Set Selection

Alternate Solution Approaches

  • Custom Table: One can potentially maintain the Report to Default Column set mapping in a custom table and refer to it upon report execution. This idea can be further extended to customize it by the user if the username field is added to that table.
  • Use the transformation rule to set up the mapping between the report and its target default column (thanks to Werner Nindl for the idea)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Karan Paresh Shah

Karan has 8+ years of experience in Corporate Performance Management across various industries. Responsible for application designs, and building and coordinating activities across project teams, Karan has both the industry and consulting insight to deliver successful OneStream technical and business improvement solutions.

More from this Author

Follow Us