Reports Tab Configuration

You can configure the Reports tab using special worksheets that have the _setup suffix.

For instance, use the report_setup worksheet to configure the tab for the report worksheet.

To create these worksheets with control templates, use the Create Report Setup Worksheet button in the Developer Tools menu.

Switching between report and configuration worksheets is straightforward using the dialog box launcher in the Parameters group.

You can also easily verify your configuration changes on the Reports tab.

The report tab configuration worksheet can include the following elements, separated by empty rows:

• Header
• Filter
• Reload
• Save
• Merge
• Actions
• Context Menu
• Parameter

Header

Cell A1 must contain the savetodb_90 value.

Filter

Filter elements define a row and a column with empty, 0, or 1 cell values.

The add-in hides rows and columns for 0 and unhides them for 1.

Since filter cells can contain formulas that use parameter values, you can create dynamic reports using only Excel formulas.

The add-in displays filter elements in the Apply menu of the Configuration group.

Here’s a sample configuration:

Column values:

• A - filter
• B - A filter name for the Apply menu
• C - An ImageMso name for the Apply menu
• D - A filter row address
• E - A filter column address

You can search for ImageMso values online.

Reload

The reload elements allow reloading data from other worksheets using the Reload menu items.

Typically, reports consume data from these worksheets via formulas.

Here’s a sample configuration:

Column values:

• A - reload
• B - An item name for the Reload menu
• C - An ImageMso name for the Reload menu
• D - Empty
• E - A ListObject or worksheet name to reload
• F - Empty
• G - A parameter name of the reloaded object query
• H - A parameter value of the reloaded object query

If the reloaded query has multiple parameters, use subsequent rows to fill columns G and H.

To reload multiple objects using the same menu item, use additional rows to fill columns E, G, and H.

Save

The save elements enable saving data using the Save menu items.

Each element requires two ranges:

• A source range
• A connected ListObject for saving data

The add-in reads data from these ranges, compares it, and executes SQL commands to update the target range. It then reloads the ListObject data to prepare for new data.

The configuration must include a column or list of columns used as row keys. The add-in uses these keys to link source and target rows. You can use the rownum or rownum2 value to link rows by their row numbers.

Here’s a sample configuration:

Column values:

• A - save
• B - An item name for the Save menu
• C - An ImageMso name for the Save menu
• D - A name of the source range
• E - A name of the connected ListObject for saving data
• F - A column name or a comma-separated list of column names for row keys
• G - A parameter name of the object used to save data
• H - A parameter value of the object used to save data

If the target query has multiple parameters, use subsequent rows to fill columns G and H as shown in the sample.

To save multiple ranges using the same menu item, use additional rows to fill columns D-H.

Merge

The merge mode is similar to the save mode described above.

However, unlike the save mode, the add-in does not delete target rows that are absent in the source.

Use the merge keyword in column A.

Actions

The actions elements allow executing various operations through the Actions menu.

Here’s a sample configuration:

Column values:

• A - actions
• B - An item name for the Actions menu
• C - An ImageMso name for the Actions menu
• D - Empty
• E - A name of the connected ListObject table for the connection string
• F - An action to execute
• G - A parameter name
• H - A parameter value

The action to execute can include:

• A procedure name or SQL code
• A URL
• ExportToExcel
• ExportToPDF

ExportToExcel and ExportToPDF are built-in SaveToDB actions.

Refer to the sample for stored procedures below.

Context Menu

The contextmenu elements enable executing various operations via the Excel context menu.

These are similar to the action elements described earlier.

However, the context menu must include a range name in column D, and the actions can utilize the cell context.

The add-in displays context menu items for cells within the specified range and reads column headers from the first row.

Context menu actions can access values from the active row using parameters with the header names. They can also use context values like regular SaveToDB event handlers:

• @column_name
• @cell_value
• @cell_number_value
• @cell_datetime_value, and others

Refer to the Developer Guide for a complete list of available context values.

Here’s a sample configuration:

Note that context menu elements require FaceId values instead of ImageMso in column C.

Parameter

The parameter elements define ribbon parameters.

Here’s a sample configuration for a drop-down list parameter:

Column values:

• A - parameter
• B - A ribbon parameter name
• C - A datatype
• D - List values
• E - An actual parameter value
• F - A name of the connected ListObject
• G - A parameter name of the connected ListObject

This configuration defines four list elements, including the first empty element.

When a user changes a parameter value, the add-in updates the actual value in column E.

You can use these values in your formulas, including for setting parameter values of other configuration elements.

This feature behaves like VBA form controls, but you define it here and use ribbon parameters instead.

The add-in supports the following datatypes:

• list
• string
• integer
• double
• date
• bit
• boolean

Elements of the list datatype must contain list values. The add-in updates column E with the selected item index, starting from 1.

Elements of the bit and boolean datatypes display checkboxes on the ribbon. Column E will contain values TRUE or FALSE, without NULL.

Other types will display ribbon textbox controls. The add-in validates input values according to the specified datatype and updates column E with the entered value.

Here’s a sample configuration for a checkbox parameter:

Here’s a sample configuration for parameters of a connected ListObject:

Column F contains the name of the connected ListObject. Use the Database Connection Wizard to connect it, for example.

Column G contains parameter names of the connected ListObject, while column B includes names of the ribbon parameters.

You can leave columns C and D empty, as the add-in automatically detects parameter datatypes and value lists.

When a user modifies these parameters, the add-in updates column E with new values, reloads the connected ListObject, and reapplies active filters.

For example, if a user changes the category_id parameter, the report will reflect updated data, including new empty and non-empty rows.

If your filter displays only non-empty rows, you'll receive an accurate report as the add-in reapplies the filter.