instantOLAP: The ROWNUM() function and grouping

InstantOLAP is a lightweight reporting and OLAP system. It’s made for OLAP reports. But if we have a non-aggregating query, we can still list all the rows by using the ROWNUM() function, which helps iterating over the result set. But we’re not able to use a dimension for grouping the rows of the result set.

I’ll give an example: imagine we have rows describing some customers. Each customer is in exactly one customer category. What we now want to have is a list of all these customers, but grouped by customer category. And because we have a dimension for the customer category, we want a separate table for each category. The naive solution seems to put the customer category dimension as an iteration into the outer block of the pivot table. But this will not work because ROWNUM() – an thereby the LINE_DIMENSION – is the only dimension used for iteration.

Customer tables preview

But there’s at least one way to achive that. It’s probably not the most elegant way and I’m eager to hear of other solutions, but it works for reports that are not too big.

We create a pivot table and in the outer block where we’ve put the table, we iterate over the customer category dimension. Set the iteration property to {CUST_CAT::'CAT'}. To see the current iteration in the title of the pivot table, put a text into the title property of the table, e.g. "Customer category: " + {CUST_CAT::'CAT'}.

Now, put the fact that holds the information about the customer category in the table. I’ve put Fact:'CUSTOMER_CATEGORY' as the first column. We don’t need to see it, so we can set it invisible. And here’s the trick: for the iteration over ROWNUM(), set the property Visible like this: TONUMBER(CUST_CAT::'CAT') = TONUMBER(MATRIX(1)), where the argument into the MATRIX() function is the position of your fact for the customer category.

Layout of table in the report

Because we have the dimension and level CUST_CAT::’CAT’ as an iteration in the outer block, we basically create as many identical tables as the dimensions level has keys. If there’re 10 keys in the level, we have 10 tables with all the rows from our result set. But only those rows are visible where our fact matches the current key of the dimension, which basically allows us to group the customer rows by the dimension.

One Comment

  1. Posted 2009-10-29 at 23:04 | Permalink

    Hi. Even simpler, set the property “Span Body” of the table to true, this will span (and group) the table-content like headers in normal pivot tables.


Post a Comment

Your email is never shared.