How to create a report with several cubes grouped in lines?

Unknown

Hi,

In Excel I have a report that looks like this:

 

What would be needed in board in order to create a similar structure, where the different cubes are over each other (by rows) instead of side by side (by colums)?

BR, Ray

Helmut Heimann

Hi Ray Martens,

 

if you want to display KPIs by row (and not by column) you will have to implement an account-based model instead of a KPI-based one (which is more or less standard with BOARD). The approach would be as follows:

1. Create an entity to describe your KPI (e.g. Net sales, weight, etc)

2. Include this KPI entity as dimension in your Cube that is dimensioned by Customer

3. populate the cube by customer and KPI (and time of course)

4. Build a DataView displaying the cube by month and customer and put in the KPI entity as second dimension in your x-axis (rows).

 

That should do it--hope, this helps!

For further information you might want to have a look at this document: How To: Choose whether to define an information as an Entity or Infocube  --good luck ;-)

 

Kind Reagrds,

Helmut

 

PS: this solution will give you the opportunity to calculate dependant values directly "on the fly" using BOARD rules (like avg price per kg)...

Previous Member

Hi Helmut Heimann,

thank you for your explanation! It is quite insightful to know that this is a design decision and not a report development decision that would be done much later and also flexible. Still there would be the possibility to create both versions at design time.

BR, Ray

Helmut Heimann

you're welcome!

And you're not restricted in any way using both principles in your applications. The only thing is that the users have to be aware of this since they will have to choose the "right" cube in their self-service ambitions... ;-)

 

Best

Helmut

Alessio Biondi

Hi Ray,

you could also evaluate these two other options in Board:

 

• use of ad-hoc entity but with data picker that will retrieve data
  BOARD Data Picker Functionality 
• use of vertical alignment of dataview (internal option in tab "axes")
  Setting Axes 

 

Keep in mind that these are advanced options but with some particular behaviour, whether in data retrieving or in video rendering: better test them in depth before implementing.

 

Alessio

Etienne CAUSSE

From the look of your table, Vertical Alignment as suggested by Alessio Biondi looks like the easier option by far

Helmut Heimann

Hi Ray Martens,

 

as Etienne CAUSSE stated correctly the vertical alignment would be the easiest way to achieve your aim, sorry. You only have to keep in mind that you will have to adjust the column width for each column separately.

 

Kind Regards,

Helmut

Previous Member

to conclude, I was missing this option, marked in yellow, to align vertically.

It seems that it will not work with some of my tables, as rendering takes forever (I stopped it after 5 minutes) - but for others it works fine.

Thanks to all of you.

Fred Langinieux

Hi Ray and all,

 

Besides the fact that columns need to be individually resized, there are a few notable limitations I can think of with the Align Vertically setting.

- no data entry is allowed

- no nested entities: must be one and no more than one entity in rows and columns

- Excel export from the Windows client doesn't carry the vertical alignment (with the Windowns client)

 

It is otherwise a very useful format if you don't need data-entry or nested entities.

 

Best regards,

Fred.

Alberto Basso

Hi All,

Another important limitation:
- drill down is not available.

 

Alberto

Previous Member

Hi all,

 

I found another limitation:

If one of the blocks is an algorithm of type picture, vertical layout is not available.

 

BR, Ray

Previous Member

Hi all,

 

maybe this is just a bug, not a limitation, but trying to use a layout filter (with the vertical layout) an error message came up.

 

BR, Ray

Daniele Di Lorenzo

Hi Ray Martens

I have several cases like yours and I can confirm that the account-based model approach suggested by Helmut Heimann is the best one. 

 

you need some time at the begin to set it up but finally you'll have a reliable application.

 

I  rarely use the vertical alignment for the limitations already explained in the posts above

 

best regards

 

Previous Member

Hi all,

 

If you are still deciding between using an Infocube- or an Entity-based approach, I want to provide my two cents -- and why we're switching back to the Infocube-based approch. As always YMMV.

 

Our main reason for finally trying out the Entity-based approach (let's call it the KPI-setup) is that it will allow you to use rules.

Only there are a couple of problems associated with using rules

• You cannot use computed KPIs in "Refer-To" - see Refer-to + rules? 
• You will need to use the KPI-dimension in the rows of the dataview - otherwise rules do not work.
  • were you using filters/sorting/top-x? Those don't work as expected anymore - or not at all.
  • did you group rows by another dimension? ? at least two dimension on rows ? affects the layout, sorting/top-x/filters are applied on the lowest/innermost row dimension.
  • plan on using rules-computed KPIs by column? Won't work. While you can have the KPI dimension in columns, the rules do not function on columns.

 

Other problems of the KPI-setup, not associated with rules

• Need to use column-based dataviews? You could use 'refer-to', making your layout more complex instead of easier.
  • Want to use dataentries in combination? Won't work.
• Using the KPI-dimension on the column and also need the month there? ? two dimensions on columns seem to behave odd. e.g.: columns don't group as rows do; column titles get meaningless.
• In our case, the loading process takes longer than before. We used to load several cube values in parallel with one datareader. All of the cubes were in one big table and structured by invoice line. With the KPI-setup, it's still one big table, but with one cube-value per line. The table needs the structure of invoice line + KPI ? more rows, more data to be transferred from the database to Board ? more IO ? longer loading.

 

A second reason we wanted to use the KPI-setup was to be able to use a pager in order to dynamically select what information the dataview would show.

That would allow the user to show 'sales per customer' or 'profitability per customer' with a single dataview and a pager.

Why didn't that work? We have a couple of KPIs that have to be computed - they are computed by rules. Remember if you are using rules, you'll need the KPI-dimension in the rows. Now combine that with grouping the information on a customer level. Now you have two dimensions on the rows. With that you cannot use sorting/top-x anymore. This also changes the layout - because you'll have a new grouping level, with meaningless subtotals. Of course you can flatten the groups, then the layout is good, except for a superfluous column with the KPI name.

 

 

In addition to the points mentioned above I want to go through a couple of points from How To: Choose whether to define an information as an Entity or Infocube which is already a great document with many insights.

 

Approach 1 (Infocube)

•  Every time you need to calculate a KPI you need to define multiple blocks with all the factor infocubes into the dataview layout

This does not need to be true as you can save algorithms. See Column Algorithm and search for save.

 

Approach 2 (Entity)

• Performance is generally better.

In our case performance is mostly the same.

• Unskilled users could forget to select the interested KPI and they will get meaningless data

This turns out, not be a problem, as the our infocube model was already creating problems for unskilled users.

 

 

 

 

Now obviously I tried a bit more in order to the a good layout by rows. See Cubes grouped in lines, a combining approach. The reason why we didn't pursue that further is because of the slowness. When grouping the information by customer, the datapicker will be called for every row. Lots of rows? Very slow. A handfull till a dozen of rows - good performance.

 

So our current approach is to use the vertical alignment with all its quirks. And to use it only with very few rows - because many rows means poor layout performance.

 

This turned out to be a bit of a rant.

Also: please upvote Paul Wyatt - GVA's idea Pivoting Dataviews which essentially asks for a better solution.

 

 

BR, Ray