ADHOC Report Builder

Paul Wyatt

The Idea.  

I would like to ask for the community to vote for the idea of an adhoc report builder for the daily user.  I am aware that this idea, in various guises, has already been suggested, most recently by swathi mp in his question here.  And here are some others:

Adam Elvin 05 Nov 2017 Conditional Display of Columns - This solution works for the specific scenario presented but is narrow in scope.

Brendan Broughton 02 Feb 2018 - "Hide Based On" in Layout Block - This is a filter and operates like my suggested No.2 solution below. 

Paul Wyatt - Avison Young UK 16 Jan 2019 - Active Selection View/Hide Panel

 

The Issue.  

using the configuration tool, BOARD allows developers to create fixed format reports which can be re-configured by users.

 

Image 1: Current BOARD Dataview Config screen (v10.1.4)

 

The trouble is that the configuration tool, a version as seen in image 1, scares all but the bravest of users for whom the models were built.  This tool also requires training in its effective use, as well as basic training into the fundamental structure of the model and I do not know any accountant, surveyor, manager or director wanting to undertake any of that.    Simply put, the config tool is only suited to analysts and developers.

 

General Objectives Of the Adhoc Report Interface

As a guide, and in my opinion, an intuitive interface should be able to include:

 

1. Simple 'field' menu to select from.
2. Ability to save and modify configuration.
3. Independent column ordering, width amendment and cell formatting.
4. Multiple datatype cells; Text, values, dates, BLOBs.
5. Exportable for use in third party systems, such as Excel.
6. Report formatting; Columns, Headings, Title, Pages, etc.

 

Suggested Solution - No.1: The Desirable One.

BOARD to provide an adhoc report builder and allow the user to assemble an adhoc report for themselves.  There are a number of rival tools which allow for this, including Power BI, Qliksense, Web terrier - property specific, and many more.  Usually, the report construction is also as simple as drag and drop which compounds the matter.  

 

Suggested Solution - No.2: The Radical One.

BOARD to provide the ability to the end user to hide dataview blocks from outside of a dataview.  As an extension to this idea, allow saving of various configs?

 

I think we often get too focused on how we believe something should function and forget what we want to achieve. 

 

In this case, this approach would meet that criteria.  Granted, this is a filter, not a selection and so comes with a larger power overhead but it achieves everything, and more, that I would want to achieve in the interim period and allows the use of all the power of a dataview with formatting, filtering and, most important of all, exporting to Excel for immediate local analysis. 

 

My users only want to add and take away from a master general report.  A dynamic menu object available for call-up from every dataview would be fantastic, and I'm guessing it would be far less work than providing any adhoc report building tool.

 

More about 'Why' and Current Workarounds.  

All of our external clients want to export everything to excel for further scrutiny and report construction by their own analysts, managers and accountants.  In spite of BOARD's modelling and analytical abilities, it is a fact that users want to export the data and they want to create reports that meet their needs, not have a general, inflexible, output. 

 

Other BI tools offer this ability and do it very well and I am regularly asked why BOARD should be used for a given solution when a cheaper and more effective tool 'appears' to exist which can create adhoc reports. 

 

Those reading this know how good BOARD is but we're the developers and analysts.  Surveyors just want to aggregate lease details, accountants want to calculate the income over expenditure and they all want to report their way, not one way, and they don't care what tool it is, so long as it gives them what they want - and that is what they tell their managers and director making the purchasing decisions.

 

Workaround 1.  

BOARD is capable of creating adhoc 'self-service' type reports.  Once the construction of a 'report layer' is understood, native selectors, pagers and dataview objects can all be used to make an (almost) passable self-service report building tool or screen.  Done properly, the usual security layer can function equally well to restrict data to users.

 

One can save report configurations and build up a collection of common configurations for simple and immediate application by all users.  But, there are some major shortcomings which I will go through using the six points above to identify what can and can't currently be achieved with the approach of using a cube and single entity to capture all the datafields required.

 

 

Workaround 1 - usage and issues.  

Before considering the ability of this method to create horizontal reports, it is important to outline that this method is exceptional in creating vertical records, enhanced with the use of a pager object.  Using this method, efficient information panels (sub-groups of the single record) can be easily created.

 

Image 2 shows two dataviews.  The left table is a conventional record, constructed with 32 blocks.  The right table was constructed with one block and has the added advantage of being able to hide empty fields after de-selecting [Show All].

 

Image 2: Left - conventional dataview constructed from 32 cubes, entities and algorithms.  Right - single cube approach (Note, gaps can be hidden by deselecting [Show All]).

 

Six Objectives Applied To Workaround 1

 

Obj 1.   Simple column menu to select from.  

Image 3 shows a simple, segregated menu to choose report columns from.  Whilst not 'drag 'n' drop', it is intuitive.  One can even include a second layer to group the members.  All users can create adhoc reports using native selectors.  

 

Image 3: Current report selection menu using a data entry enabled dataview and single entity.

 

Obj 2.  Ability to save and modify configuration.  

Once configured, reports can be modified and the config saved.  Using a dataview with data entry, litep licence holders can construct configurations and save them for future use and sharing (NB: I have not tested lite licences as I do not have any).

 

Obj 3.  Independent column ordering, width amendment and cell formatting.  

Using this particular approach of pre-loading a dedicated cube with data and having only one entity to hold the field members, it is not possible to re-order the columns, as can be seen in Image 4.  Instead, grouping data can help by selecting only the relevant groups.  Varied column widths are not possible as only one entity is used making the setting global for the report.  Appropriate column formatting and justification is also not possible for the same reason. 

 

Image 4: An adhoc report able to display various data formats (values, text, dates).  As columns cannot be re-ordered, altered individually or easily and data cannot be formatted or justified individually, the resulting report is suitable for limited exporting purposes.

 

 

Obj 4. Multiple Datatype Cells; Text, Values, dates, BLOBs.  

The above approach can accept multiple datatypes but the data must be converted during the loading process to text and there are some serious issues which must be understood, such as:

 

1. All the data is pushed into a text cube
2. Financial values must be re-formatted to include commas and 2 decimal points.
3. Dates gained from a process, such as an algorithm like if(a>b,a,b), appear as a five digit serial number (text) and so must be re-formatted to a date.  Dates flow directly into the cube, as text, so long as they are a direct transfer.  
4. No totals or calculations can be carried out within the report as the values are now actually text; one must calculated pre-dataload which will limit the type of value shown to fixed row values; no dynamic totals or percentages of sub-totals, etc.

 

Obj 5.  Exportable for use in third party systems, such as Excel.  

The subsequent report cannot readily be exported to Excel where values are required to be summed or calculated on.  Ordering by date is not possible as they are text strings.  It is, therefore, not possible to derive any totals from the report either within BOARD or immediately after export to excel unless each column is first converted to the appropriate datatype.

 

Obj 6.  Report formatting; Columns, Headings, Title, Pages, etc.  

The above method cannot be formatted adequately to use as a standalone 'fire & forget' report and so is of limited use.

 

Workaround 2.  

The only real current solution, as I see it, is to create a dedicated model but to achieve what is required, one would have to manipulate several versions of the same entities and cubes and perform multiple *.txt export/imports so as to render any solution difficult to scale, administer and hand over to new staff. 

Monisha Jain

Thank you Paul for sharing your Idea. We appreciate the time and effort you put into crafting your suggestion.

We have released features and enhancements which solve the general objectives mentioned. Below you can find more information with respect to the objectives.

Objective 1 and 2: Quick Layout Edit can be found in the Board 10.6 Release notes which has the functionality of simple field menu to select from and ability to save and modify configuration.

Objective 4 and 5: Export in excel now support different datatypes (Not exported in Text anymore). For machine to machine integration we have introduced a set of new features like Export dataset, Bulk-insert to different RDBMS, REST APIs.

Objective 6: Custom Style Templates was released in the latest 2023 Spring release - this has the functionality to set default formatting properties and more.

We encourage you to continue submitting your ideas and suggestions in the future, as we are always open to hearing new perspectives and feedback.