TABLE OF CONTENTS

• Overview
• Interface
• Example - Acumatica

Overview

Slice is a feature and friendly user interface that allows you to create a series of copies of the selected worksheet, where specified values in the original report are substituted by values you provide.

The feature is a convenient alternative to using blueprints to prepare report sheets from a template.

Interface

To open the Slice user interface, go to the Velixo NX tab in the Excel ribbon and click the Slice button in the Tools section.

This will bring up the Velixo side panel and the Slice interface.

The Slice interface contains the following elements:

• The Source sheet field - the source sheet is the template for your report, based on which your new report copies will be created. An active worksheet becomes a Source sheet when you click the Slice button in the Tools section of the Velixo NX menu.
  
  
• The Parameters for substitution section - click the Add button to expand
  
  
  
  • Cell field - the cell address in the template worksheet containing the parameter to be replaced in the created report(s) by values provided in the Value field.
    You can toggle between typing in the address/range and selecting from the workbook by clicking the blue icon.
    
    
  • Name field - type a name to define the name for the cell/range in Name Manager. If the cell/range is already named, the field will display the corresponding name.
  • Value field - the cell or range containing the value(s) to be substituted in the created report(s) for the value declared in the Cell field.
    You can toggle between typing in the address/range and selecting from the workbook by clicking the blue icon. In manual typing mode, this field accepts comma/semicolon-separated values (for instance, CUSTOMER1, CUSTOMER2), Excel arrays (for instance,={"CUSTOMER1", "CUSTOMER2"}) or formulas (for example, =EXPANDBRANCHRANGE("Connection", "*;-RETAIL") will return all branch codes except RETAIL).
    Use the separator toggle button to select between comma and semicolon as separator.
    
    The parameters are previewed below once entered:
    
    
    
• The Add button - lets you add more parameters for substitution.
  Note: Adding a new parameter will cause the number of created worksheets to be multiplied by the number of options the parameter contains – three parameters with ten options each will result in a thousand sheets!
  
  
• The Destination sheet name fieldThe worksheets created using the Slice feature will be named as provided in this field. The contents of the curly brackets ({}) are, by default, parameter names provided above and will be replaced with the value(s) provided in the Value field in each of the created worksheets (visible in the preview section mentioned above).
  
  You can change the destination sheet name as needed. In addition to parameter names, you can also use curly brackets ({}) containing named items derived from these parameters. For instance, if your report contains a named item AccountName calculated using a formula based on one of your parameters for substitution, you can add {AccountName} to the title. It will then be replaced with the appropriate value in the titles of the resulting sheets.
  
  Important: The character limit for an Excel worksheet is 31. Names exceeding this limit will be shortened and duplicate names will be suffixed with consecutive numbers. For instance, Invoice VERYLONGCOMPANYNAME BRANCHTHREE and Invoice VERYLONGCOMPANYNAME BRANCHSEVEN will become, respectively Invoice VERYLONGCOMPANYNAME BR1 and Invoice VERYLONGCOMPANYNAME BR2.
  
  Use the Reset button to return to the default, autogenerated title.
• The Destination field - defines where the created worksheets will be located
  • New workbook - creates resulting worksheets in a new workbook
  • Current workbook - creates resulting worksheets in the current workbook
  • Distribution list - adds a new row containing a blueprint for creating the resulting workbook during Distribution to a new or existing Distribution List, according to blueprint syntax.
    The remaining fields in the Distribution List worksheet will not be automatically populated and need to be added manually.
    
    This option is only available in Excel for Windows, with Velixo Tools installed.
    
    Choosing this option enables selection of one of the following in the Distribution list field:
    • Names of existing Distribution lists in the workbook - choosing one adds the row containing the blueprint row in the selected existing Distribution List worksheet
    • Create new distribution list - adds a new Distribution List worksheet with a row containing the blueprint
      
      
• The Formula removal mode field - determines whether formulas in the created worksheets are kept or removed. Also determines the format selection in a Distribution List when this option is selected in the Destinationfield.
  • Do not remove - all formulas from the template remain in the created workbooks and workbook ERP connections are preserved (Excel type in a Distribution list).
  • All formulas - Velixo formulas as well as other formulas are replaced with their results. If the Destination is New workbook, the ERP connections are removed (Excel (remove all formulas) type in a Distribution list).
  • Velixo formulas only - Velixo formulas are replaced with their results and all other formulas remain unchanged. If the Destination is New workbook, the ERP connections are removed (Excel (remove Velixo formulas) type in a Distribution list).
• The Slice button - creates worksheets according to all settings above.
• The Cancel button - closes the Slice panel.

Example - Acumatica

In this example, you can learn how to create a series of individual reports that retrieve Invoices and Memos from the ERP for each defined Customer.

Let’s start with a simple Generic Inquiry function that provides information about Invoices and Memos. Note that the Customer value is Placeholder name, as this is the template worksheet on which our reports will be based (this also causes the formula to display #N/A and not calculate properly).

Cell B5 contains a filter built using the GIFILTER function, that assures that the results for each resulting worksheet are filtered match the Customer value substituted for the Placeholder name in cell B3.

We are also using a “helper” worksheet containing a GI formula that lists all Customers in New York City. This formula resides in a separate worksheet, since otherwise it would be copied into each of the created reports.

Now, in the Slice interface, you can configure the parameters to be substituted:

• Set the Cell to B3, since this is the address of the value that will be substituted in each resulting report.
• Name the parameter. Customer will be the name in this example.
• Select the range containing the values to be substituted in cell B3 of each resulting report. Notice that, once selected, the values appear in the Preview section.

With parameters for substitution selected, configure the setting for the destination worksheets:

• Enter the name for the worksheets that will contain the resulting reports. In this case, each sheet will be named Invoices followed by the CustomerID it relates to, for instance, Invoices ABCSTUDIOS.
• Choose the destination for the created reports. In this example, the reports will be created in a new Excel workbook.
• Decide whether you want to keep or remove Velixo and other formulas. Here, to make the report accessible to stakeholders who do not use Velixo, we’re removing Velixo formulas.

Upon clicking the Slice button, a new workbook opens, containing worksheets with reports created for each of the substituted values, with Velixo formulas and the ERP connection removed.