Dynamic Columns

SaveToDB products enable you to show, hide, and rename data columns based on query parameters.

Imagine you have a table that contains data for all clients. Each client may require a unique set of columns or specific column names. You can set up mappings between clients and their respective column sets to display only the relevant columns when a user selects a client.

For example, consider a data table (s14.data) with the following columns:

id	client_id	id1	id2	id3	string1	string2	int1	int2	float1	float2

The mapping table (s14.view_aliases) is configured as follows:

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 dynamic columns, 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>

The SQL code should be:

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

This setup ensures that users see specific column names for each client.

You can also use objects of other types instead of inline SQL. Just return the necessary mappings based on the parameter value.

The dynamic column handler must return three columns:

Source column name
Column alias
Visibility flag

The visibility flag can take the following values:

NULL: Keep the column visible as is
0: Hide the column
1: Show the column