This details information about the data structure found in workspaces in AGR. You can see detailed information as to how to create a workspace here.
To add new information to your workspace framwork you first need to add your tables to the data element structure in the database and you need to alter two tables, data_element_ref_tables and data_element_ref_tables_connections. In addition the table data_element_ref_tables_interaction is automatically populated and updated with the trigger tr_de_populate_table_interaction
Best practices
data_element_ref_tablesThis control table lists up the SQL tables that store the data shown in the workspace columns.
id (int) - required: The id of the ref table you are adding, should be within your selected id rangename (nvarchar) - required: The name you give the ref table, must be uniquecaption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: Can be helpful to document the purpose of adding the tableref_table_schema (nvarchar) - required: The database schema of the SQL tableref_table_name (nvarchar) - required: The name of the SQL table that contains the column you want to add (the ref table)ref_table_id_column (nvarchar) - required: The name of the id column in the table you are addingdata_element_ref_table_connectionIn this control table we setup rules around how our ref tables should be joined together. If you want to join on two columns you add two separate entries into this table
table_1_id (int) - required: The id (from data_element_ref_tables) of the first table you want to jointable_1_name (nvarchar) - required: The reference name (from data_element_ref_tables) of the first table you want to jointable_1_column_name (nvarchar) - required: The name of the column in the reference table that should be used in the join.table_2_id (int) - required: The id (from data_element_ref_tables) of the second table you want to jointable_2_name (nvarchar) - required: The reference name (from data_element_ref_tables) of the second table you want to jointable_2_column_name (nvarchar) - required: The name of the column in the reference table that should be used in the join.connection (nvarchar) - automatically populated: Is populated automatically with a trigger when you make changes to the data element structure. If you think this has not been updated try to run the procedure de_populate_table_interactiondata_element_ref_columnsIn this control table we configure the columns themselves and connect to the reference tables
id (int) - required: The id of the ref column you are adding, should be within your selected id rangename (nvarchar) - required: The name you give the ref column, must be uniquecaption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: appears when user clicks the question mark in View Settings in the systemgroup_id (id) - required: The id of the group you want to put your column in. The group_id references the table data_element_groups. The group you select must be connected to the workspace type so that the column will appear in the systemtype (nvarchar) - required: The data type of the column. Supported types are int, decimal, string and date.ref_table_id (id) - required: The id of the table that contains the column, from data_element_ref_tablesref_value_column_name (nvarchar) - required: the name of the column in the sql table.ref_value_expression (nvarchar) - not requiredvisible_as_filter (bit) - required: 1, if you want to be able to filter by this column; 0, otherwisevisible_as_column (bit) - required: 1, if you want the column to be selectable in the system; 0, otherwiselookup (bit) - required: 1, if you want your column to be a lookup column; 0 otherwise. If the column is an id column and the name of the column is to be pulled from another table then you set lookup=1 and fill in the lookup_column with the name of the column (in data_element_ref_columns) that holds the id of the column. Then you need to have a seperate entry for the id columnlookup_column (nvarchar) - not required: Name of the lookup column (see comment above)low_cardinality (bit) - required: 1, if you want your filter appear as checklist by default; 0, otherwiseconfig (json) - required: Stores the default column config for the column, that can then be overwritten in each view.data_element_calc_columnsIf you require columns in your workspaces that are only simple calculations based on other columns we recommend using calculated columns as much as possible. The standard columns refer to a specific column in the database and when they are added to the query it increases the load on the de_query join. However calculated columns are only calculated in the client making the performance in many cases much better. The formula can however look at the data on the level displayed in the client so if your calculations require going down a level to complete the calculations you will have to do the calculations in the database.
id (int) - required: The id of the ref column you are adding, should be within your selected id rangename (nvarchar) - required: The name you give the ref column, must be uniquecaption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: appears when user clicks the question mark in View Settings in the systemgroup_id (id) - required: The id of the group you want to put your column in. The group_id references the table data_element_groups. The group you select must be connected to the workspace type so that the column will appear in the systemtype (nvarchar) - required: The data type of the column. Supported types are int, decimal and datedependent_columns (nvarchar) - required: a comma seperated list of the name of the columns that the calculations depend on. Here you can either refer to a standard column from data_element_ref_columns table or another calculated column.visible_as_filter (bit) - required: 1, if you want to be able to filter by this column; 0, otherwisevisible_as_column (bit) - required: 1, if you want the column to be selectable in the system; 0, otherwiseconfig (json) - required: Stores the default column config for the column, that can then be overwritten in each view. For calulated columns you put the calculation formula here in the default column with the property jsformula. Example:{
"jsFormula": "weight*order_multiple"
}
Best Practices
data_element_seriesTo create a new data series, simply add it as a new row in the table data_element_series. In this control table we configure the series themselves and determine the level the series is distributed to by connecting it to the relevant id column in data_element_ref_columns
This is a new table in version 6.2, this was previously stored in the table data_elements. The database also comes with the view v_data_elements that parses the new server json config into columns compatible to previous versions.
id (int) - required: The id of the series you are adding, should be within your selected id rangename (nvarchar) - required: The name you give the series, must be uniquegroup_id (id) - required: The id of the group you want to put your series in. The group_id references the table data_element_groups. The group you select must be connected to the workspace type so that the series will appear in the systemsystem_default_element (bit) - required: 1, if this data series comes standard with the system; 0, otherwiseclient_config (nvarchar) - not required: Stores the default series config used in the client if the config isn’t owerwritten in the workspace view. More hereserver_config (nvarchar) - required: Stores the server config for the series. See more about the available configs below. Only required properties need to be populated, others will revert to default settings.caption (string) requiredThe display name of the series within workspaces. This can be a translation string.
{
"caption": "Sales"
}
description (string)Description string for the series. Shown when expanding the info toggle next to the series name.
{
"description": "Some descriptive text about the series"
}
data_series_type (string) “stored”Defines the type of the series. Three types are available “stored” DEFAULT | “stored_generated_view” | “client_calculated”
stored The series data is read from a database table or view. data_object must be configured when using this type.stored_generated_view The configuration of generated_view creates a auto generated data base view that is then used for the data series.client_calculated Series that is calculated on the fly when opened in a workspace. Configured in client_calculation.{
"data_series_type": "stored_generated_view"
}
data_object (object)Required if using a data_series_type stored.
{
"data_object": {
"name": "dbo.histories_sale",
"data_element_ref_table_id": 1,
"id_column": "item_id",
"value_column": "value",
"without_date_column": false,
"date_column": "history_date"
}
}
name (string) required: Database object (table or view) name and schema. If no schema is supplied dbo schema is assumed.data_element_ref_table_id (int) required: Reference table id of the table the series links to. The ids are stored in dbo.data_element_ref_tablesid_column (string) “id”: Name of the series id column. The name “id” is assumed if this is not configured. If *PK* is entered, then the series table primary key columns will be matched with the reference table primary key columns (the columns must have matching names).value_column (string) “value”: Name of the series value column. The name “value” is assumed if this is not configured.without_date_column (boolean) false: Set to true if the series has no date column (dateless series).date_column (string) “date”: Name of the series date column. The name “date” is assumed if this is not configured. Not needed if without_date_column is truegenerated_view (object)Required if using a data_series_type stored_generated_view.
{
"generated_view":{
"name":"dbo.v_demand_ly",
"data_element_ref_table_id":1,
"id_column":"item_id",
"value_column_calculated":{
"name":"demand",
"calculation":"COALESCE([planner_baseline],IIF(itd.demand_setup = 1,ISNULL([demand_from_stores],0),ISNULL([forecast],0))+ISNULL([planner_promotions],0)"
},
"date_column_calculated":{
"name":"demand_date",
"calculation":"DATEADD(YY,1,{date})"
},
"join_object":"item_details"
}
}
name (string) required: Name of the database view that will be generated. If no schema is supplied with the name dbo schema is assumed.data_element_ref_table_id (int) required: Reference table id of the table the series links to. The ids are stored in dbo.data_element_ref_tablesid_column (string) “id”: Name of the id column used in the view. The name “id” is assumed if this is not configured.value_column_calculated (object) required: Value column configuration.
name (string) “value”: Name of the value column used in the view. The name “value” is assumed if this is not configured.calculation (string) required: SQL Formulation of the value column for the view. Other series can be referenced by their name encapsulated in square brackets [].date_column_calculated (object): Date column configuration.
name (string) “date”: Name of the value column used in the view. The name “date” is assumed if this is not configured.calculation (string) “{date}”: SQL Formulation of the date column for the view. Must include {date} in the formulation as a placeholder for each series date column.join_object (string): Name of a join object defined in join_objects that can then be used in the value or date column configuration by referencing columns using the objects defined alias.client_calculation (object)Required if using a data_series_type client_calculated.
{
"client_calculation": {
"data_series_dependency": ["original_sale","sale"],
"js_formula": "pc(original_sale,sale)"
}
}
data_series_dependency (array): Array of series names from dbo.data_element_series that the client calculated series js_formula uses. This can be any series type, even another calculated series. The series is only available if all dependant series are available in the workspace setup.js_formula (string) required: javascript formulation of the calculated series. The formulation can reference data series by name if they are listed in the data_series_dependency array. Conventional javascript expressions are supported plus these custom built in functions:
pc(series_name_1, series_name_2): Calculates a change in percentage from series_name_1 to series_name_2getDateInt(): Returns the current date as an integer: Year * 10000 + Month * 100 + DayOfMonth e.g. 20191128join_objects (array)Array of json objects, each containing a configuration for a database object (table or view) that can be used in different places of the series config e.g. generated_view
{
"join_objects":[
{
"name":"item_details",
"object":"dbo.item_details",
"id_column":"item_id",
"alias":"itd"
},
{
"name":"mbe_item_statistics",
"object":"dbo.mbe_item_statistics",
"id_column":"item_id",
"alias":"mis"
}
]
}
name (string) required: Name of the join object used in other places of the series config to reference the join object.object (string) required: Database object (table or view) name and schema. If no schema is supplied dbo schema is assumed.id_column (string) “id”: Name of the objects id column. The id column values have to match the series id column. The name “id” is assumed if this is not configured.alias (string): Alias that can be used to reference the object. The name value is used if this is not configured.period_grouping (string) “days”Defines the lowest level period grouping of the series. The value must exist in the data_element_period_grouping column in the dbo.data_element_ref_period_column_details table.
The three standard period groupings are: “days” DEFAULT | “weeks” | “months”.
However more can be configured in dbo.data_element_ref_period_column_details
{
"period_grouping": "weeks"
}
aggregation (object)Defines how values with in the series are aggregated when viewed on a higher level.
{
"aggregation":{
"function":"sum",
"period_values_to_aggregate": "all"
}
}
function (string) “sum”: aggregation function used for the series. Available functions are: “sum” DEFAULT | “avg” | “min” | “max”. “sum” is selected if not configured.period_values_to_aggregate (string) “all”: What values within a period grouping are used in the aggregation.
update (object)The update settings controls whether or not a series is editable and how.
{
"update":{
"enabled":true,
"data_series_to_update":"adjusted_sale",
"update_action":"batch_update_series",
"post_update_action_sps":[
"test",
"longTest"
],
"limit":{
"min_value":0,
"min_series":"original_sale",
"max_value":1000,
"max_series":"original_sale"
},
"refresh_profile":"affected_rows_only",
"weekend_distribution":{
"enabled":false,
"saturday":true,
"sunday":true,
"settings_object":{
"join_object":"item_details",
"saturday_settings_column":"is_sale_on_saturdays",
"sunday_settings_column":"is_sale_on_sundays"
}
}
}
}
enabled (boolean) false: If true updates are enabled on the series.data_series_to_update (string): Name of the series that is supposed to be updated when this series is edited. By default it is just the series it self, but can be any suitable series of the type stored. This is required if a stored_generated_view or client_calculated series is supposed to be updatable.update_action (string) “batch_update_series”: Name of the action procedure that executes the update. batch_update_series is the standard procedure but a custom one can be created and listed in dbo.de_action_sps.post_update_action_sps (array): Array of strings containing names of action procedures to execute after the update. The procedures must exists in dbo.de_action_sps and are executed in the order they are listed in the array.limit (object): Min and max values for the series
min_value (int): Minimum value for each cell of the series.min_series (string): The value of the series will not go under the value of the min_series. This will override min_value if both are configured.max_value (int): Maximum value for each cell of the series.max_series (string): The value of the series will not go over the value of the max_series. This will override max_value if both are configured.refresh_profile (string): If updating a cell should trigger a refresh of other cells or series in the workspace grid.weekend_distribution (object): This object enables the weekend distribution settings (global or config). Determines how and if values are distributed on weekend days.
enabled (boolean) false: if true, weekend distribution settings for the series are used, either what is specificed within this object or what comes from global settings in core.setting. If this object is kept as false it behaves the same way as if it had not been configured at all and all weekend distribution setting is ignored, also what is in core.setting. This means that the weekends are just like any other days, therefore get values.
If nothing else is configured global settings forecast_sale_on_saturdays and forecast_sale_on_sundays in core.setting are used.saturday (boolean): If true values are distributed to saturdays and if false they are not. Overrides the forecast_sale_on_saturdays if configured.sunday (boolean): If true values are distributed to sundays and if false they are not. Overrides the forecast_sale_on_sundays if configured.settings_object (object): If down to series id level configuration is available it is configured here.
join_object (string): References a join_object where the settings are stored.saturday_settings_column (string) “is_sale_on_saturdays”: The name of the binary column that stores the saturday settings. is_sale_on_saturdays is used if not configured.sunday_settings_column (string) “is_sale_on_saturdays”: The name of the binary column that stores the saturday settings. is_sale_on_sundays is used if not configured.workspaces (object)Data series workspaces specific settings.
{
"workspaces": {
"available": true, -- true DEFAULT
"summary_in_grid": true -- true DEFAULT
}
}
available (boolean) true: If false the series is not available to be selected into any workspace.summary_in_grid (boolean) true: If false summary for the series will not be shown in workspaces grids.affected_by_series (array)List of series that affect this series if those series are using refresh profiles.
Theses are not series this series will affect, these are series that will affect this series.
{
"affected_by_series":[
"original_sale",
"adjusted_sale",
"connected_sale"
]
}
The data series refer to the date SQL table for the definition of the calendar. The table contains columns that set the captions of the grid columns.
NOTE: If the date table is customized for example with non-calendar based week numbers the caption of grid columns will use the caption from the date table. However, workspace period filter by default uses it’s own, calendar-based logic for captions. This can be changed with the “Use period captions from database” setting which is off by default.