When building reports in Jedox, we’re referencing to data and metadata stored in the Jedox OLAP database. In order to get the desired data in your report, either tabular or graphical, we have to define the server/database, the cube and the coordinates. In order to make your report understandable for customers or fellow designers you need to structure your data accordingly. This blog post defines a collection of best practices (or suggestions) on how to structure the data and metadata in your Jedox Websheets.

Structuring (meta)data for tables

For the representation of tabular data we mostly use Dynaranges or static rows/columns in combination with PALO.DATA or PALO.DATAC formulas. The exact data shown in the cells is then determined by the defined coordinates that represent the elements from the respective dimensions of a cube. Subsequently, these coordinates can be static or dynamic.

Preferred approach for tables

In order to make your report as transparent as possible, the coordinates of each tabular cell should be irrefutably clear to the designer upon consulting the report. Therefore, our first suggestion is to define the coordinates as much as possible in the cells of the sheet. This means that all the coordinates of the PALO.DATA or PALO.DATAC formulas are referencing to cells or dynamic cells (dynarange) and never to directly entered text or variable references (variables or named ranges). See the example below:

UNSTRUCTURED: PALO.DATAC(DB,”Orders2”,nrYear,”3”,$C18,@varCustomer,”All Channels”,”Actual”,”Units”)

STRUCTURED: PALO.DATAC(D$9,D$10,D$11,D$12,$C18,D$14,D$15,D$16,D$17)

As you can see, this already provides a lot of structure in the PALO.DATAC formula. Instead of referring to directly entered elements (i.e. “Mar” for the Month dimension), through variables (i.e. @varCustomer) or named ranges (i.e. nrYear) we use an area of rows directly above the table to represent the coordinates for those table cells. See an example in the screenshot below:

blog-post-structured-data-1

blog-post-structured-data-2

The PALO.DATAC formula refers to the cells above the table while those cells subsequently refer directly to the dynamic or static values for those coordinates. By adding the corresponding names and a denominator of the dimensions in the preceding column (column C in the example), it is immediately clear what coordinates the designer is dealing with. This all looks good for the designer, but the regular user of the report does not want to see the coordinates. So for this reason we suggest to build in a so-called edit mode for these developers in combination with the HIDEROW() and/or HIDECOLUMN() functions. By simply entering a 0 or a 1 in cell A1 of the report a designer can hide or unhide the rows or columns with the coordinates for the cells in the table(s). See an example in the screenshot below:

blog-post-structured-data-3

blog-post-structured-data-4

An additional advantage of this approach is that you can easily copy-and-paste the PALO.DATAC formula down or to the right without having to alter the coordinates in the PALO.DATA formulas. See an example in the screenshot below:

blog-post-structured-data-5

Color coding for tables

In order to make the design of tables even more understandable and transparent one can also apply different background fills to the cells that are shown once the edit-mode has been turned on by the designer. Generally we can distinguish between the following types of cells:

  • Cells depicting connection/cube/coordinate determined by a written textual or numerical value;
  • Cells depicting connection/cube/coordinate determined by a named range;
  • Cells depicting connection/cube/coordinate determined by a variable;
  • Cells depicting connection/cube/coordinate determined by a formula (i.e. IF(), OR(), VLOOKUP(), etc.);
  • Cells depicting connection/cube/coordinate determined by a Dynarange;
  • Cells with denominators depicting what the adjacent cells are referring to (i.e. Connection, Cube, Dimension).

By just applying a different background fill for each of these cell types and adding a small legend we can identify the purpose and the content of a referenced table cell at a glance. See an example in the screenshot below:

blog-post-structured-data-6

blog-post-structured-data-7

Structuring (meta)data for graphs

Other than tabular data some reports (also) require a graphical representation of the data. In order to represent your data accurately, the inserted graph needs a data range on which to base the graph. This data, just like a table, could potentially be dynamically manipulated by altering the coordinates. Therefore, we are in a similar situation as the tables with the only exception that this table does not necessarily need to be visible for the user and both the series and category labels need to be mentioned on the rows and columns adjacent to the data represented in the graph.

Preferred approach for graphs

For these graphs we take a similar approach as for the tables, but since we’re assuming a situation where the table should not be visible the placement of the table in the screen is different. We want the table to be invisible for the user but visible (on request) for the designer. For this reason we need to use the HIDEROW() or HIDECOLUMN() functions to hide the table. However, since the graph is placed in the middle of the report the table should be placed outside the area of the graph. The reason why we do this is depicted in the steps and screenshots below:

Step 1. Graph defined based on data in same columns

blog-post-structured-data-8

Step 2. Come out of edit-mode by putting 1 in cell A1 & open User Mode

blog-post-structured-data-9

Step 3. Close user mode and return to edit-mode by putting 0 in cell A1

blog-post-structured-data-10

As you can see in the screenshots above, as soon as we return to the edit mode the graph is shown on top of the table. This makes the table with both the coordinates and the data invisible for the editor. However, when you place the table outside of the graph area of the report instead, you can prevent this from happening while the data is still highly-structured. Check the screenshots below for an example:

Step 1. Graph defined based on data in columns outside graph area

blog-post-structured-data-11

Step 2. Come out of edit-mode by putting 1 in cell A1 & open User Mode

blog-post-structured-data-12

Step 3. Close user mode and return to edit-mode by putting 0 in cell A1

blog-post-structured-data-13

Color coding for graphs

Similar to the tables, we can also apply the color coding to the source tables for the graphs in a report. Again, we can distinguish between the earlier mentioned types of cells, but in case of graphs with hidden source tables there’s more:

  • Cells depicting labels used in the graph;
  • Cells depicting labels not used in the graph;
  • Cells depicting data shown in the graph;
  • Cells depicting data not shown in the graph.

By again just applying a different background fill for each of these cell types and expanding the legend we can identify the purpose and the content of a referenced table cell at a glance. See an example in the screenshot below:

blog-post-structured-data-14

blog-post-structured-data-15

blog-post-structured-data-16