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:

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.

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.

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

and updated the Report Content Cube View with the new variable

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

4. Execution:

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:

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.

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)