A closer look at dynamic slicesA closer look at dynamic slices admin Mon, 2019-11-18 14:48
Dynamic slices can be quite useful when the elements you display in your reports evolve over time, they automatically update with the new elements.
The following article will try to dig into the parameters that define these slices and show some of the possibilities to interact with these. The idea was originally submitted by Philip Bichard.
The dynamic slice parameters stored in the names list of the worksheet
to display these in Excel: insert -> name -> paste -> paste list
most of the parameters are defined as SLxxCyy
xx is the slice reference 01, 02, 03... for as many slices as there are in the report
yy is the stacked dimension reference
ex: SL01C01 relates to the first stacked dimension on top of columns
SL01C02 relates to the 2nd stacked dimension from the top
SL02R01 relates to the first stacked dimension for rows on the most left of the 2nd slice
CUBE01RNG location of the cell hosting the name of the cube referenced
SL01C01DIMNM subset used or dimension name if not using a saved subset
SL01C01ELEMS_01 list of elements to display
SL01C01EXPANDUP 1/ 0 trigger consolidations as collapsed or expanded
SL01C01FMTNM name of the elements format to use
SL01C01RNG range for the stacked dimension
SL01DATARNG range for DBRW cells
SL01FILT filter settings
SL01R01ALIAS name of the alias used
SL01R01ELEMS_01 list of elements to be displayed
SL01R01NM subset name
SL01R01RNG section boundary range
SL01ZEROSUPCOL zero suppress on columns trigger
SL01ZEROSUPROW zero suppress on rows trigger
SX01RNG section boundary range
SXBNDDSP section boundary display trigger
TITLE1 subnm formula for 1st dimension
TITLE1RNG cell location
TITLE3ALIAS alias to display for the dimension
redefining the following name SL01FILT from
SL01FILT ="FUNCTION_PARAM=0.000000€SORT_ORDER=desc€TUPLE_STR=[Sales Measures].[Sales Units]"
SL01FILT ="FUNCTION_PARAM=0.000000€SORT_ORDER=asc€TUPLE_STR=[Sales Measures].[Sales Cost]"
would change the column on which the sorting is made from sales units to sales cost, and also the order from descending to ascending.
One could also achieve a similar result with an MDX expression.
The following code will change the subset from the dimension on the row stack to the predefined "Level Zero Dynamic" subset so all elements will then be displayed.
Sub ChangeRowSubset() ActiveSheet.Names.Add Name:="SL01R01NM", RefersTo:="Level Zero Dynamic" Application.Run "TM1REFRESH" End Sub
Note, you need to use TM1REFRESH or Alt-F9 to get the slice to rebuild itself, TM1RECALC (F9) would only update the DBRW formulas.
Dynamic slices will break with the following popup "No values available" because of some element not existing anymore or not having any values for that specific slice.
An easy fix is to disconnect from the TM1 server, load the report, remove the element causing trouble from the slice and names table, then reconnect to the TM1 server, the dynamic slice will refresh on reconnect just fine.