Need help with Report building, particularly Total By

Krisztina Zappert

Hi,

I would like to ask for some tips about the best practices with regards to building an archive report.

We have an archive cube with the following 6 entities: Month, Reporting Year, Version, Business Area, Currency, PnL. PnL is the biggest one with <200 members, all of the others are <20 members.

I have a screen selection on everything but the PnL: dynamic selection on a flag cube for current month, dynamic selection on current Reporting year and current Version. Business Area selected by user. Currency static selected.

I have PnL or rows and I have put the month on columns as I was trying to solve this. We also have rules on the PnL lines, to calculate certain percentage PnLs.

I am trying to build a report with the following columns:

• Current month
• LY month
• Full Quarter
• LY Full Quarter
• Full Year
• LY Full Year
• YTD
• LY YTD

I have had no issue with the first two columns, but I am having an issue with the third one. I was planning to use the Total By function to show the full Quarter, but my values seem to be disregarding any screen selections I have.

Here is how my test layout (just to present this issue here) looks with no screen selections:

I can see that the Total By works as expected.

However, as soon as I add one screen selection, let's say, Version, I start seeing the unexpected results:

The Total By Quarters are still the same as they were before I made my screen selection.

Is this expected behavior? Am I misunderstanding how the Total By is supposed to be used? I could not find anything about screen selections in the documentation of the function. Furthermore, can I use Total By in combination with the Previous Year time function to get my 4th (and 6th) column?

If Total By is not the recommended function in this case, what is? Or would the official recommendation be a Report cube separate from the Archive cube - but we are worried about the added maintenance of cubes just for the purpose of this report (since we would need a cube by quarter and by year). We have also considered making use of the Apply Selection to Layout block functionality, but were hoping to solve this problem with built-in block functions only, for ease of maintenance. It is also a performance burden to run 4 Apply Selections - opening of the screen takes much longer, which is not ideal for a relatively small cube.

Thank you for any recommendations on the best way to approach building such a report. We are in Board 14.5.

Hamza Mesbahi

Hi @Krisztina Zappert,

Based on the knowledge manual, this feature only supports dynamic aggregation on entities present in the axes. This explains why it worked with quarter (when month was on rows), but not when selecting a version.

I can think of a few ways to achieve the outcome you're after without using "Total by".

Option 1: using native dataview functionality (not requiring the use of "Total By").

You would have four blocks in your layout:

• Cube for current time selection
• cube with a "previous year" time function applied
• cube with yearly cummulative value time function
• Finally, cube with both Yearly cummulative value and previous year applied.

You would configure your axes as follows:

• P&L line by row,
• Month and Quarter by column.

if you enable "row totals" on the first two blocks (current year and previous year), the dataview will automatically calculate and display the quarterly and yearly totals. This means the following columns will be generated automatically:

• full quarter
• ly full quarter
• full year
• ly full year

It would look something like this:

Option 2: using flex grid

Another approach is to use a flex grid instead of a dataview. Flex grid is well suited for presenting data with different aggregations and metrics, allowing you to pivot the data across different dimensions as you wish.

You can reuse the same blocks from option 1 to configure your Flexgrid layout, then use the "Fields" menu to slice and present the data as needed.

Hope this helps!

Kind regards,