Slice: Substitute combinations of parameters and skip "irrelevant" combinations

Introduction

You may wish to use the Slice functionality to replace combinations of two or more parameters without generating reports for irrelevant combinations that contain no data – in the current implementation, Slice would create a report for each possible combination, even the irrelevant ones.

While such an addition to Slice is under development at Velixo, follow the guide below for a workaround enabling this use case.

Workaround

This workaround involves joining your variables into a compound parameter to iterate over. Then, you can split this parameter in the worksheet you wish to Slice, and use the parts of the compound parameter as regular variables.

Create a table with value combinations

First, create a new “helper” worksheet in the workbook that contains the report you wish to Slice.

Then, create a table where each row contains a combination of values you would like to substitute, with each column containing a separate parameter. For instance, in this example, we are replacing combinations of Location and Department dimension values.

You can also leverage Velixo functions to create a dynamic “helper” table.

Join value combinations

Now, create a new column in the table, where you will join the text from each Location and Department cell in a row using the | (pipe) character (or another delimiter of your choice, not present in the joined values) as a delimiter, for instance, using the TEXTJOIN Excel function.

Split combined values

In the worksheet with your original report, paste the first combination of joined parameters (100 - Entity #1|100 - Program #1, in this example) into an empty cell. You might want to choose a cell you can hide later if you do not wish for it to be visible in your report.

Ensure that the cells with the values you wish to replace when Slicing are above one another or in a row and in the same order as in the “helper” table, depending on how you’d like to use the TEXTSPLIT function. In the first one of these cells, add a TEXTSPLIT formula – it will split your parameters into their respective destinations (be sure to use the same delimiter as in the previous step).

Configure and Slice

Select the cell in the report sheet containing the original joined set of parameters (in this example, cell A9 – 100 - Entity #1|100 - Program #1) and click the Slice button in the Velixo NX menu, then name the parameter.

Switch the Value box to Excel range mode (Excel icon) and select the cell range in the “helper“ worksheet containing the joined parameters you wish to substitute.

Set up the remaining settings as desired and click Slice to create reports for each set of parameters.

Refer to the article on Slice for more information about the available settings.