Creating financial reports in WebIntelligence like a pro

[marton_horvath2]

Introduction of the problem

In this post I will share a technique to create tables in WebIntelligence like in professional financial statements.

To replicate an actual report, I took a table from SAP's financial report, page 18.



Financial reports have two distinctive features that make them difficult to recreate in Webi:

• unbalanced hierarchies - they have different number of levels. In the above example: Total revenue consists of two subgroups: Cloud and software and Services. While Cloud and software is divided further, Services is not. Services doesn't have any child node.


• expand and collapse - of course there's the well known drill down feature, but it's just not the same... if you drill to a segment, you don't see the other segment totals any more. I have recieved the request for this functionality a number of times, especially from users of finance departments. It's bit of a first world problem, but I understand the need.

In an OLAP cube/universe both of these are handled out of the box.

But how can we handle these requests if dealing with a relational database/universe? For about a decade I thought it's not possible with WebIntelligence...

The step-by-step guide to create a nice expandable/collapsable hierarchical table

I will work on the well known eFashion universe to make it easier to follow.Unfortunately eFashion doesn't have P&L structure, but the store hierarchy will do. Let's select: State, City, Store name, Year and Sales revenue. And create the following cross-table:



Create a break based on the State and City. The table should look like:



Now we already have something that we can expand and collapse with the Outline function (Analysis tab right side, or Reading mode also right side). (Note: you have to click into the table to be able to use the feature)

But unfortunately the above table is still very far from a professional looking financial report.

First of all, let's add the measure to the break footers as well, in order to get the subtotals.

The next issue is that in the break display options we don't have "Only display in footer", or similar. Unfortunately the options are Display first, Display all, Merge or Repeate first on new page. Either way we would lose the nice uninterrupted tree view...

The solution for this is to add a completly new column, display the group footer in the new column and hide the original column with the breaked dimension.

Step-by-step of the above:

Insert column to the left (of the breaked State column), and insert =[State] to this new column, to the footer row of the the [State] break. Perform the same for city, To get this:



Select the "line item" of [State] break and [City] break and hide the dimension.



Now the structure already looks good. It only takes a bit of formatting here-and there and we have our nice report.

Formatting steps:

• remove the background of each cell (header and measure default alternate colors)


• remove the vertical borders on each cell


• except [State], remove bold, and make all fonts black and regular


• merge the footer cells of the [State] with the column on the right of it, so the hierarchies can "overlap" (see it in relation of Massachusetts and Boston below)


• align measure column headers to the right; others to the left


• create bottom borders gradually ligher along the hierarchy


• remove all the borders before the sublevel hierarchy item


• collapse items that are not meaningful (unbalanced hierarchy items)

The result should look like this:



 

Note that the first picture in this post was also created in WebIntelligence.