Creating Drillable Reports¶
Creating Drillable Reports is basically a way of providing similar functionality to the Data Explorer, within a report. A drillable report, therefore, shows dynamically drillable data, while the report refreshes and updates accordingly.
A drillable report can be customised to prevent drilling on certain cells. Drilling behavior can also be customised. For more advanced reporting techniques, there are some technical details to be aware of, such as dealing with blank rows (see below).
Demonstration¶
To demonstrate this functionality, we start by creating a simple view in the Cube Browser and send it to Excel.
The Basics¶
The Excel sheet created contains CMEMBER formulas on the axes. Double-clicking on Corporate reveals a number of child rows (as shown in the figure below).
This is also how the drilling will work when published as a report, provided the CMEMBER formula argument shown below, is RowDrill. The argument RowDrill specifies that the row is to be drillable in a report. If the argument is Row, it will not be drillable in a report, only in Excel. Use Row to control drillability in reports.
In Excel, drilling is generally always ON, because both RowDrill and Row arguments have the same effect. Using Send To Excel from the Cube Browser, as we did here, will create RowDrill arguments, by default.
Report Appearance¶
We published the above simple report.
We published the same report after Corporate (RowDrill) and Net Income (ColumnDrill) are both drilled/expanded (within the report). The report grows to accomodate the additional data.
Detailed Customisations¶
We now reveal some hidden cells, to further understand what’s going on, and to allow for more advanced customisation.
In the sheet below, we modified the sheet by using the InsertBefore argument of CMEMBER on Net Income and then “drilled” to push it to the bottom - and left other rows with the InsertAfter behaviour. Then to ensure the bottom row (Net Income) couldn’t be drilled on, we changed RowDrill to Row (or even “” to prevent Excel from drilling as well).
Blank Rows¶
The blank row was added for visual neatness, but note that drilling requires contiguous data to form what is known as the “Working Area” or “Drillable Range”. So, if we leave this row blank and do nothing, then when the Corporate column is drilled, it won’t know that the Net Income row needs to expand along with other columns because it considers it “out of limits” - and so columns after C on the Net Income row, will not be populated. As shown in the case below, D is populated for all columns, but then stops at Row 11.
This is because drilling has to assume that there is a limited “working area” and defines that limit by stopping at any blank or merged cell (starting from the drilled/clicked cell). So, if blank cells are needed within the working area, start from the cell to be clicked, and populate any blank cells with any value (an apostrophe is a good, invisible choice, if no particular value is required). In the case below, place an apostrophe in the circled cells (B11:C11).
The previously hidden columns, starting at C6, and A8 are simply satisfying the MemberUID arguments of the CMEMBER functions in column A and on row 7.
With some further customiation of data and colors, we publish a drillable report, shown below:
Usage Notes¶
Because RowDrill pushes cells down and across, it can be incompatible with formulas that rely on remaining in one place.
One example is CREPORTLIST - it is not valid to place a CREPORTLIST formula underneath a group of rows that are RowDrilled as the base cells will be relocated by drilling, while the CREPORTLIST forrmula cannot act upon that knowledge and so will cause errors.
If inserting other content below a RowDrill is a problem, you can use a CReportFrame to enclose the content below the RowDrill.