Tag Archives: Web Reporting

Macros : Cell References

The Jedox Macro Engine implements PHP to interact with the Jedox Spreadsheet. One of the most used and basic functions is referring to a cell reference in the PHP code.  You can put data into a cell on the spreadsheet, or you can read from that cell.

Download the example spreadsheet here

Reading Cells


// read a cell value:

$value = ActiveSheet()->Range('E7')->value;

return __msgbox($value, 'Returning a Cell Value', 'Info');

// or you can use

return __msgbox(ActiveSheet()->Range('E7')->value, 'Returning a Cell Value', 'Info');

Writing Cells


// Write a value into a cell:

ActiveSheet()->Range('E10')->value = "hello again";

Note, the warning in my previous post. Depending on the complexity of the calculation and the type of cell you are referring to, the calculation timing may be out when referring to cells.

For instance, if you have a =PALO.DATA in the cell that is volatile and you are using a Macro to read the cell value, depending on the order of calculation you can pick up the wrong result. If you are trying to read a value from a cell that contains a PALO.DATA formula, sometimes it is easier to just refer to the cell in the cube using the application-> command in the Macro.

Returning Cube Values


// Get a value from a cube :

$value = application()->palo_data("localhost/Demo","Sales", "All Products", "Europe", "Jan", "2011", "Actual", "Units");

Note here that the formula palo_data, not palo.data. This is because the Palo/Jedox functions implemented in the Macro engine are directly from the Jedox PHP API.  Also, the point/full stop/period is a reserved character in PHP. It is the concatenation character between strings.

Finally, an interesting/semi -advanced concept for you. Sometimes you want to retrieve a cell value that is not necessarily an intersection in the cube, but the sum of certain subsets across one or more dimensions. For example, want to retrieve the Total Unit value for a collection of top 10 Regions. Unfortunately, you cannot refer to a subset as a substitute for an element name in a PALO.DATA formula (yet). But, we can use a macro to achieve the same effect.

Returning a Subset


// Return data cell based on a subset on the Regions Dimension.

// NOTE: The PHP subset is different to the spreadsheet function in 2 ways:

// 1. Replace all . with _ (eg palo.subset becomes palo_subset)

// 2. all blank arguments need to be replaced with NULL (eg PALO_HFILTER("West",FALSE,FALSE,,,,,,) becomes PALO_HFILTER("West",FALSE,FALSE,NULL,NULL,NULL,NULL,NULL,NULL))

$ArraySS = application()->PALO_SUBSET("localhost/Demo","Regions",1,NULL,PALO_HFILTER("West",FALSE,FALSE,2,NULL,NULL,NULL,NULL,NULL),NULL,NULL,NULL,NULL,PALO_SORT(1,0,NULL,0,NULL,0,1));

// Get array size for looping

$ArraySize = count($ArraySS);

for ($i=0; $i<$ArraySize; $i++)

{

$value = application()->PALO_DATA("localhost/Demo","Sales", "All Products", $ArraySS[$i][1], "Jan", "2010" ,"Actual" ,"Units");

$ValueTotal = $ValueTotal + $value;

}

ActiveSheet()->Range('E22')->value = $ValueTotal;

Subsets : Hierarchy Filters

The Hierarchy filter is a major subset type that  is usually used in Jedox Web. The Hierarchy filters elements in a dimension based upon definitions around a hierarchy.

h-filter3

As the meta-data (dimension structures) change in your system, the changes will be reflected wherever you use the subset. Download the sample file here.

There are 2 examples in this file.  On tab 1, there is a straightforward hierarchy filter. A Hierarchy filter allows you to filter the subset list based on hierarchical arguments. For example, you can specify to only return elements at a certain level, or elements under a certain parent. Hierarchy Filters are fantastic for when you want to display a particular tree of a specific hierarchy on a report:

h-filter1

On the first tab of the examples, I have basically exposed all the variables on the h-filter. Play around and see how they work. You will notice that some of the variables are values in @Value format. These are Jedox Web variables. They are user and session related variables that you can populate and share between reports. More on those later.

h-filter2

The second tab illustrates the flexibility of Subsets and Dynaranges. The ‘Show Parents Beneath Children”  is manually set on the General tab, but it is not a variable option. You can, however, set a flag in the subset formula that gets generated.

What we are doing here is generating the base subset in the Subset Editor, and then changing the DynaRange type to Formula. We can now modify the formula manually to allow the user to toggle between showing parents above and below the rows:

h-filter5

The finished report now allows the user to toggle between having totals at the bottom at the top of the row elements:

h-filter6

Web Reporting with Dynaranges and Attribute Filters

On the Web, with Dynaranges, you can make your report length determined by how the user interacts with the report. Dynaranges allow you to specify a single row of data and then determine what happens to that row when the user interacts with it in report mode.

It is a powerful Web component because you can create completely customised, dynamic reports that allows users to explore your data without restrictions while keeping a structure of a formatted report.

You can use the following as datasources in a Dynarange:

  • Subsets
  • Formulas
  • Relational Queries

a-filter_1

Dynaranges can be on either columns or rows. This means that both axes of your report can be dynamic. In addition, if you select a subset that contains hierarchical structure, the user can double click to drill down to the next level of the data, and the report will expand automatically.

Relational Dynaranges allow you to write a relational (SQL) query via an ODBC connection and return rows of a single column dynamically. You can then use ODBCDATA() functions to pull out the additional columns you require. The report expands and contracts with the data you are returning.

a-filter_2

Everything in the Dynarange range will be calculated. This means that non-subset values (eg spreadsheet formulas) can be included and the results will be calculated at runtime with the Dynarange.

OK. An example.

Attribute Subset in a Dynarange

Download the example here.

This contains a Dynarange and subset based on attributes in the customer dimension. The user selects an attribute from the Customers dimension in the first combo box. This then filters the second combo box to show all the data values against that attribute name. The user can then filter the elements on the dynarow. If you look at the subset on the dynarow, you can see that the attributes are dynamic and pointing to the results of the combo box:

a-filter_3

This is a handy dynarow style for filtering a sorting through a dimension with many attributes. Note: In the example, the elements returned to the second combo box (the attribute data) may be duplicated as it is displaying a value for each element in the dimension. You can remove duplicates only by writing a macro (that is attached to the combo box) to loop through an array of elements and picking out only the unique ones.

Introduction to Security in Jedox

Jedox has a robust security model. You can allocate permissions down to cell level if required. All of its security objects are held within cubes and dimensions so it is pretty straightforward to set up and apply security. Its user security is group and role based, which means that users are members of groups and groups have certain characteristics defined for them by their role memberships.

Jedox can be set up for LDAP and Single Sign on, which enables Network Administrators to deploy user security centrally via an Active Directory server.

Moving down from user security, most objects in Jedox can be managed by security as well. This includes elements, dimensions, cubes; as well as reports, folders, web objects (eg Report Manager). For instance, you might have an ETL developer who only requires the ETL Manager. You can easily set up a role which would only give this access.

The only thing that cannot be managed (at the moment) is databases. I am certainly looking forward to this in a future release as this would open up the potential for more powerful multi-tenancy applications.

User Security is managed by System Manager. Here we can add users, roles, groups and manage relationships between them:

security_sysmgr

Unfortunately, you need to create your own reports out of the system database to manage Cube and Dimensional Security. After doing this half a dozen times, I realised that standardisation was required here, so I set up a couple of templates. Here are the ones we use for our clients:

Web Security Template (.wss)

Excel Security Template (.xls)

The Web Template:

security_web_template

The Excel Template (password to open: jedox):

security_excel_template

These templates allow you to easily manipulate and manage security, including doing things like applying like-for-like security to new users, setting dimensional security, and cube permissions.

NOTE: If manipulating/changing these templates, DO NOT use PALO.DATAC formulas when accessing security cubes. You will get instability. You must use PALO.DATA formulas.

Permissions – The Hierarchy of Rights

Without getting too technical at this point, the key part to remember here is that you need to assign to a group certain levels of access to particular objects. By default, a group with no rights assigned or restricted will have access to everything (you can reverse this default if needed from Jedox 5 onwards).

When assigning security to cells, dimensions or cubes you have the following options:

S (splash): Exists only for the rights object “cell data”. It includes writing into consolidated cells and (indirectly) their children down to the lowest level.

D (delete):  Permits the deletion of rights objects.

W (write):  Permits the writing of rights objects.

R (read): Permits the reading of rights objects.

N (none):  No type of access permitted.

A trap for young players here: assigning write access does not give you the right to enter a zero or delete a value. You can only add data. To get full ‘write’ rights, you need to actually assign Deletion (D) rights. The Deletion right also allows you to delete your data, which sometimes comes in handy.

Managing Web Reporting

Jedox is a broad platform. There is a huge amount of functionality under the covers that you have access to. Sometimes the manuals are a bit cryptic in their description of what is available. So over the next little while, I plan to expose a fair bit of this functionality and how it is used in a business context. Some of it is straightforward and some is quite complex, so bear with me and just take on board what you can understand. Feel free to ask questions.

A key part of Jedox is its Excel add-in. It is by far the most popular and used front end for Jedox, given the ubiquity and flexibility of Excel.  The main game, however, is the Jedox Web interface. It is powerful and flexible. More development effort has been put into the web interface than any other Jedox front end, which sort of indicates where Jedox AG sees the future of the product.

Ok, so let’s get into it.

 Performance Dashboard

Every wondered who is using your web reports? How often? What are the usage peak times, peak users? All that information is available in the core.log. It is a standard log file format, which is about as useful as an ashtray on a motorbike when it comes to reporting. So we need to parse it with the ETL to get something beneficial out if it.

Download the Performance Dashboard. This .pb file (A Jedox report application bundle) is a handy application which enables you to monitor and react to users hitting your Jedox web reports. It is essentially a cube and a report built by parsing the core.log file. Once you have downloaded it, do the following:

1. Log in to Jedox Web (File Manager)

2. Select an appropriate group to import the .pb file into and import it.

3. Once imported, you should see the following:

Imported Files in File Manager

4. Download the Jedox-Stats.xml. This is a Jedox ETL Project. Open ETL Manager and import the project.

Import Project into Jedox ETL

5. Download the Jedox-Stats.zip. Unzip this file. This is a Jedox database that you need to add to your Jedox data folder (stop the Jedox MOLAP Service first).

6. Start the MOLAP Service; change the path in the ETL Projects parameters / variables to match your system settings. The file path for the Core log is the most important one at this point.

Jedox ETL Variables

7. Run the job-[00]:CoreStatsBuild job. This will populate your cube with the settings from your system. The default ETL captures 2 measures – reports successfully loaded and reports that have been attempted to be loaded by users.

Checking the success of the ETL Job

8. Once you have ran the ETL, open the Performance Dashboard. It should look something like this:

Jedox Wed Performance Dashboard

The dash is pretty self-explanatory: the line chart on the left represents all hits and avg/hts per day as per your core.log. You can change server/date period at the top. The middle doughnut represents successful loads vs attempted loads. The chart on the right represents the top 10 reports by that selected period. The real interesting stuff here is the Top Times, Users, Reports. The top times are top hours in the day users hit the reports. Hover over the >> to see more information and click to filter by users for that time. You can select a user for that time and click the >> to see that users top reports for that time. Click on the report and it will take you to a simple analysis of that report by user. Select Top or Flop (bottom reports). Click on a report and it will take you to further analysis by user of that report.

Remember, this is just a template or starting point – it is just a Jedox Web page, and you can edit this yourself and customise it as required. Note: I use cell A1 to hide/unhide rows and columns (via Jedox (HIDEROW() and HIDECOLUMN() functions) used but not displayed. Change cell A1 to 0 to display all hidden rows/columns; enter 1 to hide again.

This type of information is handy, especially when you are centrally deploying an enterprise rollout of Jedox to a wide user base and you need feedback on what reports that users are using and which ones they are not. This ensures you are targeting your reports correctly and it provides insight into how users are interacting with the reports.