List tables, views or stored procedures

1. In browser, log in to Izenda as a user with Data Model permission.

2. Click Settings, then Data Setup then Data Model in the left menu.

3. Select the Setting Level: either System or a specific tenant.

4. Click Tables, Views or Stored Procedures in the Middle Panel.

   

5. Visible tables, views, or stored procedures from all connections will be displayed in the upper data source grid, and columns of the currently selected one will be displayed in the lower Column Grid.

   

6. By default, only the first page with 10 items are displayed. To see more, either use the next page icon

   

   or select a larger number in Items per page box.

   

Search for tables, views or stored procedures

The Search box at the top allows user to search for specific tables, views or stored procedures.

1. Select a specific element to search for in the dropdown on the left of the Search box. Default is All.

   

2. Type a partial name and click the search icon (🔍).

3. Only the matching tables, views or stored procedures will be displayed.
   

Assign a category to a table, view or stored procedure

1. Select a name in the Category dropdown to assign it to the table, view or stored procedure.

2. If the category name is not yet in the list, user can add it by typing the name in and press Enter.

   

3. Continue to assign category to more tables, views or stored procedures in the same page.

4. Click Save button at the top, then click OK in the confirmation pop-up.

   Note: User must save the assignments before moving to another page or another group in the Middle Panel.

Assign an alias to a table, view or stored procedure

1. Enter an alias into the Database Source Alias box to assign it to the table, view or stored procedure.

2. Continue to assign alias to more tables, views or stored procedures in the same page.

   The alias can contain any characters except for “[” and “]”.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note: User must save the assignments before moving to another page or another group in the Middle Panel.

Note: Within a category, the aliases cannot be duplicated. In , Alias_1 is duplicated because the data sources are in the same Category2, and Alias_2 is valid because the data sources are in different categories.

Manage relationships for a table, view or stored procedure

1. Click the relationhips icon at the end of each line to open the Relationships dialog.
2. See Data Model/Relationships and Schema for how to manage relationships.

Assign an alias to a column in table, view or stored procedure

1. In the Column Grid, enter an alias into the Column Alias box to assign it to the column.

2. Continue to assign alias to more columns in the same page.

   The alias can contain any characters except for “[” and “]”.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note: User must save the assignments before moving to another page or another group in the Middle Panel.

Note: Within a table or view, the aliases cannot be duplicated.

Select visible or not for a column in table, view or stored procedure

A visible column will be included in any field selection control in report.

1. Untick the Visible check-box to exclude the column from field selection controls in report, or leave it checked to include.

2. Continue for more columns in the same page.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note: User must save the changes before moving to another page or another group in the Middle Panel.

Select filterable or not for a column in table, view or stored procedure

A filterable (and visible) column will be included in any filter in report.

1. Untick the Filterable check-box to exclude the column from filters in report, or leave it checked to include.

2. Continue for more columns in the same page.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note: User must save the changes before moving to another page or another group in the Middle Panel.

Update Filter Value for a table/view field or a stored procedure parameter

This feature are only available for tables/views’ fields from version 2.14.0

1. Click the icon in Filter Value box.

   Note: For Store Procedures, Filter Value icon only appears for parameters.

2. Select either Filter Lookup Key - Value or User Defined Filter Value

   • Example to set parameter @OrderID to look up from NorthwindA.dbo.Orders.OrderID, displaying column ShipName to end-user.

     

   • Example to set parameter @OrderID to look up from a list of 3 values: the value of Tenant ID and 2 fixed values NewValueA and NewValueB.

     
3. The selected filter value will appear in the Filter Value box.
   
   

4. Click Save button at the top, then click OK in the confirmation pop-up.

Add a calculated field to table or view

WHENscore>=0ANDscore<7THEN'GOOD'WHENscore>=7ANDscore<9THEN'EXCELLENT'WHENscore>=9THEN'OUTSTANDING'

1. Select the table or view in the upper data source grid.

2. Click Add Field button on top of the Column Grid to open Add Calculated Field pop-up.

3. Enter the field name into Column Name box.

   The field name must be unique in that table or view.

   

4. Enter the definition for the calculated field into Expression box.

   Click the bulb icon (💡) to see the list of available fields, functions and operators. Then click a field, function or operator to insert it into the cursor position.

   

5. Depending on the formula, a suitable data type is suggested in Data Type drop-down.

6. Click Preview button to see a sample result of the formula.

7. Click OK to accept the formula and close the pop-up.

   

8. Click Save at the top.

Remove a calculated field from table or view

1. Select the table or view in the upper data source grid.

2. Click the remove icon (X) at the end of the calculated field.

   The remove icon is only enabled for calculated fields.

   

3. Click OK in the confirmation pop-up.

   If the calculated field has been used in any report, user will have to confirm that these reports will not be viewable anymore.

4. Click Save at the top.

Set stored procedure as dynamic

For stored procedures with any input parameter, they can be set as dynamic.

1. Tick the Dynamic check-box to set a stored procedure as dynamic.

2. Click OK in the confirmation pop-up.

1. Click Save button at the top, then click OK in the confirmation pop-up.

2. The stored procedure will be set as dynamic and its schema will be removed from the lower Column Grid. The Execute button is also disabled.
   

Set stored procedure as non-dynamic

1. Untick the Dynamic check-box to set a stored procedure as non-dynamic.
2. Click Save button at the top, then click OK in the confirmation pop-up.
3. The stored procedure will be set as non-dynamic. The Execute button is also enabled for user to get the schema.

Execute a non-dynamic stored procedure to get the schema

1. Click the Execute button above the lower Column Grid.
2. System tries running the stored procedure to get the schema.
3. The schema will be populated into the lower Column Grid.
4. Click Save button at the top, then click OK in the confirmation pop-up.

The action will fail if one of the parameters requires not null and Filter Value has not been defined.

In this case, please update the Filter Value section.