Technical Manual > Inventory > Reports

Reports

MBE Columns and Reports
Adding Report Column
- Adding MBE Column using Helper Proc
- Not running update statement for disabled MBE Columns
Adding Report using Helper Proc
Lookup Fields

MBE Columns and Reports

MBE stands for “Management by exception” and it is a style of business management that focuses on identifying and handling cases that deviate from the norm, recommended as best practice by the project management method, more info on Wikipedia

MBE columns provide a generic way to add columns to the AGR system which can be used in reports.

MBE columns and reports are very important in the AGR system and consultants do a lot of work customizing that part for the client.

The system comes with standard columns and reports already added but consultants can add more customer specific columns and reports.

Consultants most often add customer specific columns to table mbe_item_custom or table mbe_item_extra.

Consultants also add update statements for the added columns to proc mbe_item_custom_populate or proc mbe_item_extra_populate.

Helper proc mbe_add_column can be used to make it easier to add MBE columns to the mbe_columns table.

Function dbo.mbe_column_active can be used in the procs to check if MBE column is active (currently used for standard report columns).

Tables and ex views:

SELECT * FROM mbe_columns
SELECT * FROM mbe_columns_ex ORDER BY table_name, column_name
SELECT * FROM mbe_tables
SELECT * FROM mbe_column_groups

SELECT * FROM mbe_reports
SELECT * FROM mbe_reports_ex        ORDER BY group_name, id
SELECT * FROM mbe_report_columns    ORDER BY mbe_report_id, id
SELECT * FROM mbe_report_columns_ex ORDER BY group_name, report_name, id
SELECT * FROM report_filters        ORDER BY mbe_report_id, id
SELECT * FROM report_filters_ex     ORDER BY group_name, report_name, id

Adding Report Column

Step 1

Add the custom mbe field to the table mbe_item_custom located on PROD. Add new column and choose column name and type. At first the table only contain the field item_id. Do not populate the table just yet.

When adding report columns, we do NOT support duplicate column names. Keep this in mind when adding columns.

create

Step 2

Custom mbe field added to the table mbe_columns. After inital load the system contains a number of standard mbe fields. All mbe fields both standard and custom are stored in the table mbe_columns. Insert a row to the table mbe_columns which matches the name of the column added in step 1.

mbe_columns

id (int): Automatically generated id
mbe_table_id (int): Id of the table in mbe_tables
column_name (nvarchar): Name of the column in the source table
mbe_column_group_id (int): Refer to the mbe_column_groups table where you can define the report column groups
description (nvarchar): Description of the mbe column
alignment (nvarchar): not used, should always be populated with null
caption (nvarchar): 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 system
lookup_table_id (int): Id of the table in mbe_tables if the column should be a lookup column. See further documentation of the lookup columns.
editable (bit): 1 if the field is editable in the system, 0 otherwise. NOTE we do not support editable columns, should always be populated = NULL
column_format (nvarchar): Can be used to set details about the formatting of a column. Possible setting options: id, integer, number, currency, percent, percentage, date, datetime
column_aggregation (nvarchar): Possible aggregation: AVG, SUM, MIN, MAX, DISTINCT
created_at (datetime) - required
updated_at (datetime) - required
deleted (bit): 1 if the column should not be shown in the system, 0 otherwise.

Most often the table mbe_item_custom is used for custom mbe fields, specially if the field is calculated or from the ERP system. Note, it is possible to link the new mbe field to the other tables listed in mbe_tables or create a new table and add to mbe_tables in exceptional cases.

mbe_tables

id (int): id
table_name (int): Name of the source table
table_type (nvarchar): Possible type: base / lookup / detail
date_field_name (nvarchar):
created_at (datetime) - required
updated_at (datetime) - required

Step 3

The last step is to populuate the mbe field in the procedure mbe_item_custom_populate on PROD. The procedure contains a template of how to populate a column.

Adding MBE Column using Helper Proc

Helper proc mbe_add_column makes it a little bit easier to add MBE columns meaning you dont need to spend time writing the SQL statement and finding id’s for the MBE table, the column group and the lookup table.

Proc parameters:

@table (nvarchar) - required: Name of table in mbe_tables
@column (nvarchar) - required: Name of column
@group (nvarchar) - required: Name of group in mbe_column_groups
@lookup_table (nvarchar) - required: Name of lookup table in mbe_tables
@format (nvarchar) - required: Format for column f.e. string, date, integer, float:4 or currency:::2
@aggregation (nvarchar) - required: Aggregation for column f.e. distinct, sum or avg
@caption (nvarchar) - optional: Caption for column (default NULL meaning name of column used)
@description (nvarchar) - optional: Description for column (default NULL)
@deleted (bit) - optional: If column should be disabled by default (default 0)

Examples to add column without and with a lookup table:

mbe_add_column 'mbe_item_custom', 'my_new_column', 'DETAILS', NULL, 'string', 'distinct', 'my caption', 'my description'

mbe_add_column 'items', 'location_id', 'BASIC_INFO', 'v_mbe_locations', 'string', 'distinct', 'LOCATION', 'MBE_i_LOCATION'

Not running update statement for disabled MBE Columns

Function dbo.mbe_column_active is used in update procs when we want to skip update statements for disabled MBE columns.

Function parameters:

@mbe_table_name (nvarchar) - required: Name of table in mbe_tables
@mbe_column_name (nvarchar) - required: Name of column in mbe_columns

Proc code would be something like this:

IF dbo.mbe_column_active('mbe_item_custom', 'name_of_mbe_column') = 1
BEGIN
    UPDATE ...
END

Note that some update statements update multiple columns meaning you need to do it like this:

IF dbo.mbe_column_active('mbe_item_custom', 'column_1') = 1 OR dbo.mbe_column_active('mbe_item_custom', 'column_2') = 1
BEGIN
    UPDATE ...
END

Note that OR is used meaning all related MBE columns need to be disabled to stop the update.

Currently the function is being used for new standard report columns but NOT the old standard columns, see procs mbe_item_period_calc_populate and mbe_item_statistics_populate.

Adding Report using Helper Proc

Reports can be scripted using the mbe_report_add helper procs, example:

    DECLARE @report_id INT, @report_name NVARCHAR(255)

    SET @report_name = 'Test report'
    IF NOT EXISTS(SELECT * FROM mbe_reports WHERE [name] = @report_name)
    BEGIN
        EXEC @report_id = mbe_report_add '6-General', @report_name, '-'

        EXEC mbe_report_add_column @report_id, 'items',                 'location_id'
        EXEC mbe_report_add_column @report_id, 'items',                 'item_no'
        EXEC mbe_report_add_column @report_id, 'items',                 'name'
        EXEC mbe_report_add_column @report_id, 'item_order_routes',     'cost_price'
        EXEC mbe_report_add_column @report_id, 'mbe_item_statistics',   'last_sale_date'
        EXEC mbe_report_add_column @report_id, 'mbe_item_period_calcs', 'avg_sale_last_12_months'

        EXEC mbe_report_add_filter @report_id, 'MbeReportFilterText', 'items', 'closed', 'equal', '0'
    END

Lookup Fields

Step 1

Lookup fields require numerical values instead of strings in the SQL table. That means, for example, that a column in mbe_item_custom would have the values [Sweden, Norway, Finland] for a text-filter but a lookup column has the numeric values [1,2,3] for corresponding countries. We have the following steps to map the numeric values to the real values.

We start by making a new table in prod. The table needs three columns: id, name and if the row is active as lookup or not. This table will keep a distinct list of the possible values of the lookup and give a unique id to each value.

create table dbo.[table_name]_lookup
(
id int identity,
name nvarchar(255),
active bit
)

Step 2

To populate the new table, alter the procedure dbo.mbe_item_custom_populate in prod and add the following bit of code after replacing the relevant information.

[table_name_lookup] is the table created in step 1
[where data_from_ERP] is a table/view from the ERP system
[desired_column_name] is a distinct list of all the possible values in the lookup, only one column

  
        -- Update for the lookup [table_name]
              MERGE dbo.[table_name_lookup] AS TARGET
              USING  
               (select distinct [desired_colum_name] as name  
                   from [data_from_ERP])  
    AS SOURCE  
              ON  
             (
             isnull(TARGET.[name], 'Unique string') = isnull(SOURCE.[name], 'Unique string')
             )
             WHEN MATCHED AND ( ISNULL(TARGET.[active],0) <> 1 ) THEN  
             UPDATE SET TARGET.[active] = 1
             WHEN NOT MATCHED BY TARGET THEN  
                  INSERT ([name], [active]) VALUES (SOURCE.[name], 1)
             WHEN NOT MATCHED BY SOURCE THEN  
       UPDATE SET TARGET.active = 0

Example

id	item_id	country
1	111	Norway
2	222	Sweden
3	333	Norway
4	444	Sweden
3	555	Finland

Desired output from this table

SELECT  DISTINCT country FROM items

country
Norway
Sweden
Finland

For more complicated tables, this could be a view.
Excecute both the new code and the whole procedure. By now, the table from step 1 is populated with a list of distinct values, all of which have a unique id.

Step 3

Create a view that has the data from the table we created.
The view makes sure our data structure is still valid if more values are added or changed.
Notice it only selects values with active = 1
For simplicity and traceability, create the view with an identical name as the table but beginning with v_.

USE [agr_prod]
GO
SET ANSI_NULLS ON
GO  
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================================

--  DD/MM/YYY.intials    Created

-- ===============================================================================

CREATE VIEW v_[table_name]_lookup
AS
SELECT id, name  
FROM dbo.[table_name]_lookup
WHERE active = 1
GO

Step 4

Add a column to mbe_item_custom with type int. This column will store the id of the lookup value for each item.

ALTER TABLE agr_prod.dbo.mbe_item_custom ADD descriptive_column_name int NULL

Step 5

To link the id stored in mbe_item_custom to the correct value we must alter the stored procedure mbe_item_custom_populate again and add this piece of code below the code we added in step 2. Basically, we’re updating the column we added to mbe_item_custom with numeric values. We select the item_id for each item from the source data and using the view from step 3, we select the numeric value corresponding to the actual lookup value.

The inner select clause should result in a table with two columns: item_id and the value of the lookup. The number of rows is the same as in mbe_item_custom
The outer select clause should result in a table with two columns: item_id and the id of the lookup value. The number of rows is the same as in mbe_item_custom

  Following are two (real) examples:

-------------------------------------------------------------

-- Link lookup table to values
-- Ny vara ef að fyrsti söludagur er fyrir minna en 180 dögum síðan.

UPDATE m
SET m.ny_vara = u.ny_vara  
FROM mbe_item_custom m INNER JOIN
(
SELECT v.item_id,   
vl.id as ny_vara -- The value we want to put in the column. Notice we need to rename the column to match mbe_item_custom
FROM mbe_item_statistics v  
 INNER JOIN (SELECT s.item_id,
CASE WHEN s.first_sale_date > getdate() - 180          
THEN 'Já' ELSE 'Nei' END AS name
FROM  mbe_item_statistics s
) v2  
on v2.item_id = v.item_id
INNER JOIN dbo.v_ny_vara_lookup vl ON v2.name = vl.name
) u  
on m.item_id = u.item_id

-------------------------------------------------------------

UPDATE m
SET m.product_group_code = u.product_group_code
FROM mbe_item_custom m  
INNER JOIN
(
SELECT i.id AS item_id,                             -- Outer select clause: item_id, id of the lookup from the view created.
v1.id AS product_group_code        
FROM items i
inner join (
SELECT i2.id AS item_id,                                -- The inner select clause: item_id, value of lookup  
ni.[Product Group Code] AS product_group_code     
FROM [agr_stg].[nav].[Item] ni
JOIN items i2 ON ni.[no_ ] = i2.item_no
) v2
ON i.id = v2.item_id
inner join dbo.v_product_group_code_lookup v1 ON v2.product_group_code = v1.name
) u
ON m.[item_id] = u.item_id

Step 6

The final step is to move the data into AGR, which is done by editing two tables:

Add the view (v_[table_name]_lookup) into the last row of mbe_tables
Add a row in mbe_columns