Reports Tab Configuration

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

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

You can create such worksheets with control templates using the Create Report Setup Worksheet button of the Developer Tools menu.

You can easily switch between report and configuration worksheets using the dialog box launcher of the Parameters group.

Also, you can easily check 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 the empty, 0, or 1 cell values.

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

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

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

Here is 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 on the Internet.

Reload

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

Usually, reports consume data from such worksheets using formulas.

Below is 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 rows below and fill columns G and H.

Use rows below and fill columns E, G, and H to reload multiple objects using the same menu item.

Save

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

Each element uses two ranges:

• A source range
• A connected ListObject used to save data

The add-in reads data from the ranges, compares data, and executes SQL commands to update the target range.
Then, it reloads the ListObject data to be ready to save new data.

The setting must contain a column or column list used as row keys.
The add-in uses such keys to link source and target rows.
You can use the rownum or rownum2 value to link rows by row numbers.

Below is 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 used to save data
F - A column name or a comma-separated column list used as 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 rows below and fill columns G and H as shown in the sample.

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

Merge

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

Contrary to the save mode, the add-in does not delete target rows absent in the source.

Use the merge keyword in column A.

Actions

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

Below is 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 used to get the connection string
F - An action to execute
G - A parameter name
H - A parameter value

The action to execute can contain:

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

ExportToExcel and ExportToPDF are built-in SaveToDB actions.

See a sample for stored procedures below.

Context Menu

The contextmenu elements allow executing various operations using the Excel context menu.

They are similar to the action elements described above.

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

The add-in shows the context menu items for cells in the range.

Also, the add-in reads column headers from the first row of the range.

Context menu actions can use values of the active row using parameters with the header names.

Also, context menu actions can use context values like regular SaveToDB event handlers:

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

See a complete list of the available context values in the Developer Guide.

Below is a sample configuration:

Note that context menu elements require FaceId values but not ImageMso in column C.

Parameter

The parameter elements allow defining ribbon parameters.

Here is a sample configuration of the 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

The sample 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 such values in your formulas, including for setting parameter values of other configuration elements.

This feature works like VBA form controls. However, you define it here and use the ribbon parameters instead of the form controls.

The add-in supports the following datatypes:

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

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

The elements of the bit and boolean data types place checkboxes on the ribbon. Column E has values TRUE or FALSE, without the NULL.

The elements of other types place ribbon textbox controls. The add-in checks input values according to the specified data type and updates column E with the typed value.

Here is a sample configuration of the checkbox parameter:

Here is a sample configuration of the parameters of the 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 omit values in columns C and D as the add-in automatically detects parameter datatypes and value lists.

When a user changes such 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 has updated data, including new empty and non-empty rows.

If your filter shows only non-empty rows, you will get an actual report as the add-in reapplies the filter.