Dynamic Columns

SaveToDB products allow showing, hiding, and naming data columns depending on query parameters.

Suppose you have a table with data for all clients.

However, a set of columns is specific for each client or, at least, columns have particular names.

You can configure mappings between clients and column sets to show only the specific columns and hide others when a user changes the client.

For example, a data table (s14.data) has columns:

id	client_id	id1	id2	id3	string1	string2	int1	int2	float1	float2

The mapping table (s14.view_aliases) has the following settings:

client_id	table_name	column_name	alias
1	s14.data	float1	sales
1	s14.data	id1	state
1	s14.data	string1	product
2	s14.data	float1	sales
2	s14.data	string1	region
2	s14.data	string2	manager

To configure the dynamic columns, let's add the mapping table using the DynamicColumns event type in the xls.handlers table:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s14	data		DynamicColumns	s14	dynamic_columns	CODE	<SQL code>

Where the SQL code is

SELECT
    column_name, alias, is_active
FROM
    s14.view_aliases
WHERE
    client_id = @client_id AND table_name = 's14.data'

As a result, users will see specific column names for each client.

You can use objects of other types instead of the inline SQL as well. Just return the required mappings depending on the parameter value.

The dynamic column handler must return three columns:

The visibility flag can have values: