Dynamic reports with cube formulas – tips and tricks
Dynamic reports with cube formulas – tips and tricks
In our previous blog post, we introduced the basic principle for dynamic reports with cube formulas.
If you now use z.B. wants to create a report with partial result totals, the additional challenge is that the subtotals are in different rows depending on the particular filter setting:
Changing the filter to the year 2006 shows a considerably reduced assortment and the first partial result is in cell C7 instead of C5:
File for download: [wpfilebase tag=file tpl=filebrowser /]
Tips and tricks
Identification of partial result rows
While partial result rows can be identified quite easily by empty cells in the further detail columns ( IF (D4=""; partial result ; detail row) ), you can unfortunately not address the values so easily: In the pivot table, the rows are displayed with the extension "Result", but in the cube element formula, this must not be used as well. With CHANGE(…; " Result" ; "") you can get rid of this problem. For the colored representation one uses then conditional formatting.
Format date columns
Date fields that also contain a time must be formatted very specially in order to be recognized correctly by the cube element formula. Even if you put the date in this form using a custom format: 2015-12-31T00:00:00 the cube formula still doesn't seem to accept it. I then help myself with the following construct:
Performance and clarity
If you convert a pivot table with several fields in the rows or columns to formulas, many redundancies are created in the cube element formulas. Each added field "inherits" the cube element specification of its (all) predecessors – this makes the formula unnecessarily long, because you only need the last definition part to get the necessary uniqueness. The red colored area can be removed completely:
The reason may be. in that the cube value formula by default always stubbornly accesses the last row field. Now, if that is not unique, as in our case here (30.06.2007), then no correct value would be returned.
Therefore, on the one hand, you can reduce the cube element formulas to the last attribute, but then you have to change the reference in the cubewert formula to the unique element:
Add data sections afterwards:
To do this, simply go to the old pivot table, insert the data slice, cut it out and place it in the report sheet. Now you can easily address it as an additional filter element in the cubewert formulas as well.
The remaining "intelligence" then consists only of if-then cases. All formulas are placed in the IF ERROR bracket to suppress the NVs in case of variable report length.
Tags: #cubeformula #excel #PowerPivot #pivot #dynamic #interactive #report #partial result #subtotals
