The Grid Editor component is not a report type in the classical sense. It is rather a very flexible spreadsheet like database editor that can be used in situations where you want to enable a user to do some basic data administration. Once defined the grid editor can be used as any report, that is, it can be used by users in their TeamSpace and users can even export the underlying data to Excel and schedule the report.

The grid editor component is configured by providing a datasource and a ReportServer script. ReportServer scripts are covered in greater detail in Chapter 21. and in the separate ReportServer scripting guide and it might be helpful to skip the following details on a first read and come back to grid editors once you have a basic understanding of ReportServer scripts.

The simplest use case for a grid editor is when you have a database table and you want to give a user the possibility to edit the data in that table. At the very basis you need to generate a GridEditorDefinition which handles the interaction with the user. For relational databases, ReportServer provides a helper class called DbGridEditorDefinition (located in net.datenwerke.rs.grideditor.service.grideditor.definition.db) which tries to handle as much of the interaction (loading data, storing values, etc.) as possible. Consider the following example script:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'

return definition

Here we obtain a new instance of a DbGridEditorDefinition and load an adapter object which is used for most of the configuration. At the very least you need to specify which database table you want to work on. With the above configuration ReportServer will attempt to load the data as

SELECT * FROM T_AGG_CUSTOMER

and display the data paged with a page size of 100 rows. To change the number of rows per page you can call setPageSize() on the adapter and set the number of rows. By default the editor will allow the user to edit every cell, to delete entire rows and to insert new rows. The entire editing process is cached on the client and only if the client calls save will the data be stored.

If you need to filter the data on your table, you can use set the whereClause property as shown in the following example:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.whereClause = 'CUS_CUSTOMERNUMBER > 100'

return definition

Consider the following data table (a small extract of the demo data):

CUS_CUSTOMERNUMBER	CUS_CUSTOMERNAME	CUS_CREDITLIMIT
386	Lordine Souveniers	121400
412	Extreme Desk Decorations, Ltd	86800
456	Microscale Inc.	39800

Now suppose the user changed the credit limit of Microsale to 50000. In this case ReportServer builds the following update statement

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNUMBER = 456 , 
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNUMBER = 456 AND  
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND 
        CUS_CREDITLIMIT = 39800

Assuming that CUS_CUSTOMERNUMBER is the table's sole primary key, this statement would be a bit of an overkill as the WHERE clause lists fields which are not part of the primary key. You should thus always tell ReportServer the primary key of a table. In addition we might want to only display a fraction of the table:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

Now if a user updates the table the following statement will be generated behind the scenes:

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNUMBER = 456 , 
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNUMBER = 456

If you are updating a table containing an auto-increment primary key, you should add it to the list of columns and set its column configuration to be non-editable (setEditable(false)). You can further hide it if desired (setHidden(true)). As an example, here you can see a table ''TABLE_NAME'' having an ''id'' auto-increment primary key and a text column ''your_column''. The example uses the fluid API (see Section A Fluid API for more details).

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,"TABLE_NAME")
    .setPk('id')
    .columns()
        .add('id')
			.setEditable(false)
			.setHidden(true)
        .add('your_column')
    .done()

return adapter;

You should avoid using the Grid Editor on tables that do not have a primary key (or have the primary key not displayed). Assume the following configuration:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

Now if a user updates the table ReportServer generates the following update query

UPDATE T_AGG_CUSTOMER 
    SET  
        CUS_CUSTOMERNAME = 'Microsale Inc.' , 
        CUS_CREDITLIMIT = 50000 
    WHERE 
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND 
        CUS_CREDITLIMIT = 39800

This update statement might not uniquely identify the data row and thus trigger an update on multiple rows and thus might not have the intended effect.

Besides the standard API to configure the grid editor there exists a compact, fluid API. First we can make it easier to obtain an adapter object. For this there in the scope of the script you have access to an object called gridHelper which provides the method initDbGridEditor. Then, to initiate the API one needs to call the configure method on the adapter object. The above example can be rewritten in the fluid API as

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

The configure method takes as parameter a report object (the corresponding report object is present in the script's scope) and the table name. From there you can access various configuration, amongst others, column configuration. By calling the columns method you start the column configuration which you end again by calling done.

For the remainder of the description of the grid editor we present features first with the "classical" API and then how to do the same with the fluid API.

A Grid Editor's adapter object provides several additional configuration options that we discuss next.

Per default the Grid Editor displays the data in a paged fashion showing 100 records on each page. In order to increase the number of records on each page you can call the adapter object on setPageSize(pagesize) specifying the size a page should have. To disable paging you can call setPaging(false).

By default, users can filter the table by specifying a search string for every column. Furthermore users can sort the Grid Editor by every column. If you would like to globally disable sorting or filtering you can use the following methods of the adapter object:

setSortable()	If true then sorting is enabled. (Default: true)
setFilterable()	If true filtering is enabled. (Default: true)
setCanAddRecords()	If true adding records is enabled. (Default: true)
setCanDuplicateRecords()	If true duplicating records is enabled. (Default: true)
setCanRemoveRecords()	If true removing records is enabled. (Default: true)

Note that filtering and sorting can also be specified on a per column basis.

All the above configuration can also be done via the fluid API by calling, for example,

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
   .setPaging(false)
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

In order to further configure how columns are presented to the user, you can specify so called column config objects which provide column specific configurations. Basic column configs are specified via instances of class GridEditorColumnConfig located in net.datenwerke.rs.grideditor.service.grideditor.definition:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter

adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

def nameConfig = new GridEditorColumnConfig()
nameConfig.displayName = 'NAME'
adapter.setColumnConfig('CUS_CUSTOMERNAME', nameConfig)

return definition

Or more compactly via the fluid API

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
        .add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .setDisplay('Name')
        .add('CUS_CREDITLIMIT')
    .done()

return adapter;

For setting the display name you may also use the shortcut .add('columnName','distplayName').

Besides changing the name of columns you can also specify a number of display options

setWidth()	Defines the display width. (Default: 200)
setEditable()	If true then the column is editable. (Default: true)
setHidden()	If true then the column is not displayed. (Default: false)
setSortable()	If true then the column is sortable. (Default: true)
setOrder()	Allows to specify the order by supplying "asc" or "desc". For the fluid API there is the shortcut of calling setOrderAsc().
setFilterable()	If true then the column can be filtered. (Default: true)
setEnforceCaseSensitivity()	If true then filtering on that column is always case sensitive. (Default: false)

When a user adds a record to a table, all values are by default set to NULL. You can specify a default value for a column by using the setDefaultValue method of a column configuration object.

Without further configuration ReportServer will only enforce that entered data is of the correct type. For example, if a field is of type INTEGER, then a user can only type in digits into the text field. In order to further restrict what users can enter you can add one or more Validators to each column. Validators are located in package net.datenwerke.rs.grideditor.service.grideditor.definition.validator. The following validators are available

MaxIntegerValidator	Allows to specify an upper bound for columns of type INTEGER
MinIntegerValidator	Allows to specify a lower bound for columns of type INTEGER
MaxBigDecimalValidator	Allows to specify an upper bound for columns of type DECIMAL
MinBigDecimalValidator	Allows to specify a lower bound for columns of type DECIMAL
MaxLongValidator	Allows to specify an upper bound for columns of type LONG
MinLongValidator	Allows to specify a lower bound for columns of type LONG
MaxDoubleValidator	Allows to specify an upper bound for columns of type DOUBLE
MinDoubleValidator	Allows to specify a lower bound for columns of type DOUBLE
MaxFloatValidator	Allows to specify an upper bound for columns of type FLOAT
MinFloatValidator	Allows to specify a lower bound for columns of type FLOAT
MaxDateValidator	Allows to specify an upper bound for columns of type DATE
MinDateValidator	Allows to specify a lower bound for columns of type DATE
MaxLengthValidator	Allows to specify a maximum length for character based columns
MinLengthValidator	Allows to specify a minimum length for character based columns
RegExValidator	Allows to restrict text based fields to match a pattern

In order to configure a validator you instantiate the corresponding object and provide the necessary configuration in the constructor. All Min/Max validators take as configuration the bound as well as an error message that is displayed in case a user enters a value that violates the bound. For example

new MaxIntegerValidator(15, "Values must be less than 15");

The RegExValidator takes as configuration a regular expression (see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/util/regex/Pattern.html for an introduction) and an error message. To, for example, restrict data to conform to a date format of type "yyyy-mm-dd" you could use

new RegExValidator("^\d{4}-\d{2}-\d{2}$", "Value should be of format yyyy-mm-dd");

With the fluid API there are also shortcuts to validators. You can call addValidator(new ..) when configuring a column. Additionally there are the following shortcuts:

addEmptyValidator(message)
addFixedLengthValidator(length, message)
addRegExValidator(regex, message
addMinLengthValidator(min, message
addMaxLengthValidator(max, message)
addMinValidator(min, message)
addMinValidator(max, message

By default the Grid Editor constructs form fields matching the type of a column. That is, for a text columns a text field is created, for date columns a date picker, for booleans a checkbox. For certain fields you can change the default behavior and specify a custom editor. In order to tell a column that it should use a custom editor use the setEditor method of a GridEditorColumnConfig option. All custom editors are located in package net.datenwerke.rs.grideditor.service.grideditor.definition.editor.

Sometimes boolean values are not stored as booleans in a database but as text or int values. For example, you might have a text column with the values "true" and "false" or an integer column with values 1 and 0. In this case you can use a TextBooleanEditor or an IntBooleanEditor in order to still present a user with a simple checkbox, rather than a textfield or a number field. As configuration you can tell the editor which value is representing TRUE and which value is representing FALSE. Per default TextBooleanEditor uses the strings true and false and IntBooleanEditor uses integers 1 and 0. To change the default use methods setTrueText (resp. setFalseText) and setTrueInt (resp. setFalseInt).

The following is an example assuming the column names textbool and intbool.

def textbConf = new GridEditorColumnConfig();
textbConf.setEditor(new TextBooleanEditor());
adapter.setColumnConfig('textbool', textbConf);

def intbConf = new GridEditorColumnConfig();
intbConf.setEditor(new IntBooleanEditor());
adapter.setColumnConfig('intbool', intbConf);

Using the fluid API, we can define quasi-boolean editors for columns by calling withIntBooleanEditor, or withTextBooleanEditor.

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE')
    .setPk('...')
    .columns()
        .add('column')
            .withIntBooleanEditor()
    .done()

return adapter;

Sometimes dates are stored in text form, for example, as strings of the form yyyy-mm-dd. In these cases you can tell the Grid Editor not to use a basic text field but a date picker. For this use the editor TextDateEditor. For configuration you should provide the corresponding date pattern (see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html for an introduction). The following is an example for how to use the TextDateEditor.

def dateConf = new GridEditorColumnConfig()
dateConf.editor = new TextDateEditor('yyyy-mm-dd')
adapter.setColumnConfig('textdate', dateConf)

Using the fluid API, we can define quasi-boolean editors for columns by calling withTextDateEditor, or withTextDateEditor(format) and specifying the format.

Often you might want to allow users to choose from a list of values. For text based columns and integer columns you can define selection lists via

TextSelectionListEditor	A drop down editor for text based columns.
IntSelectionListEditor	A drop down editor for text based integers
LongSelectionListEditor	A drop down editor for text based longs
DateSelectionListEditor	A drop down editor for text based dates
DecimalSelectionListEditor	A drop down editor for text based BigDecimals
FloatSelectionListEditor	A drop down editor for text based float
DoubleSelectionListEditor	A drop down editor for text based double

A selection list can be configured in two ways. Either you can specify a simple list of values or you can specify a map of label-value pairs. Each entry of a selection list consists of a label (the string that is shown to the user) and a value (the actual value that is stored in the database). In case you provide a simple list, each entry serves both as label and as value. Following is an example of a simple selection list for a text column.

def ddTextConf = new GridEditorColumnConfig();
def textddEditor = new TextSelectionListEditor();
textddEditor.setValues(['a','b','c','d']);
ddTextConf.setEditor(textddEditor);
adapter.setColumnConfig('textdd', ddTextConf);

It configures a selection list with the entries a, b, c, and d. Alternatively, you can specify each value individually:

def ddTextConf = new GridEditorColumnConfig();
def textddEditor = new TextSelectionListEditor();
textddEditor.addValue('a');
textddEditor.addValue('b');
textddEditor.addValue('c');
textddEditor.addValue('d');
ddTextConf.setEditor(textddEditor);
adapter.setColumnConfig('textdd', ddTextConf);

If you want to distinguish between labels and values you can either specify the map directly by calling setValueMap(). Or you can add each entry individually as follows:

def ddTextConf = new GridEditorColumnConfig();
def textddEditor = new TextSelectionListEditor();
textddEditor.addEntry('a','b');
textddEditor.addEntry('c','d');
textddEditor.addEntry('e','f');
ddTextConf.setEditor(textddEditor);
adapter.setColumnConfig('textdd', ddTextConf);

For integer columns the configuration works identical with the only difference that you assign a IntSelectionListEditor instead of a TextSelectionListEditor and that values are of type integer. Following is an example using a simple list to define values:

def ddIntConf = new GridEditorColumnConfig();
def intEditor = new IntSelectionListEditor();
intEditor.setValues([2,3,5,7,11])
ddIntConf.setEditor(intEditor);
adapter.setColumnConfig('intdd', ddIntConf);

And an example with custom labels.

def ddIntConf = new GridEditorColumnConfig();
def intEditor = new IntSelectionListEditor();
intEditor.addEntry('foo',2)
intEditor.addEntry('bar',7)
ddIntConf.setEditor(intEditor);
adapter.setColumnConfig('intdd', ddIntConf);

With the fluid API we can also compactly generate selection editors. By calling withSelectionEditor() you start the configuration of the editor which you end by calling done(). This allows you to configure an editor for example as:

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE)
    .setPk('...')
    .columns()
        .add('column')
            .withSelectionEditor()
               .addValue('A')
               .addValue('B')
            .done()
    .done()

return adapter;

Within the edit mode for the editor you have the very same methods addValue and addEntry. In addition you can add multiple values via the method from which either takes a list of values (corresponding to addValue) or a map (corresponding to addEntry).

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE)
    .setPk('...')
    .columns()
        .add('column')
            .withSelectionEditor()
               .from([1,2,3,4])
            .done()
    .done()

return adapter;

Finally, a frequent objective is to construct the values for the selection list from a database query. To this end, you can use the fromSql which takes either a SQL statement or a connection object and a SQL statement. In case you provide no connection, the same connection as to the grid editor is used. In case you provide a connection, be sure to close the connection after usage. The SQL statement needs to return either two columns (key,value) or a single column.

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE')
    .setPk('...')
    .columns()
        .add('column')
            .withSelectionEditor()
               .fromSql('SELECT key, value FROM X')
            .done()
    .done()

return adapter;

Within your script you can access a couple of predefined variables that allow you to access the report object, as well as the current user and the parameters. The following variables are available:

report	The corresponding GridEditorReport object.
user	The current user.
parameterSet	The ParameterSet object with the current parameters.
parameterMap	A map allowing to easily access parameters.
gridHelper	Used to easily construct an adapter object

You can easily obtain a database connection for the datasource that is configured at the report by using the getConnection() method from the DbGridEditorDefinition object.

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)
def connection = definition.getConnection(report)

connection.close()

In the following we explain how you can use foreign key relationships to make editing easier. Consider a database table Products that has the following structure

productNumber	The primary key (INT)
productName	(VARCHAR)
productCategory	A foreign key pointing to Table Categories (INT).
productSupplier	A foreign key pointing to Table Suppliers (INT).

Here, we have to foreign key relationships, one pointing from products to a specific product category and a second one pointing to a supplier for the product. What we are modeling here is a many-to-one relationship. That is, a product is in exactly one category, but of course, a category can contain multiple products. Now, assume that we have category and supplier tables that look like:

categoryNumber	The primary key (INT)
categoryName	(VARCHAR)
categoryDescription	(VARCHAR)

supplierNumber	The primary key (INT)
supplierFirstName	(VARCHAR)
supplierLastName	(VARCHAR)

Now, consider that you use a basic grid editor instance to manage the product table:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productNumber')
adapter.addColumns('productNumber', 'productName', 'productCategory',  'productSupplier')

return definition;

In this case, if you wanted to switch the category of a product, you would need to know the category number. In such cases, it might be helpful, to instead be able to choose a category using the category name rather, but this is stored separately from the Products table. For such cases you can specify foreign key columns which allow you to display different information in place of the information that is within the table. On update and insert this information is then replaced again by the correct value.

In order to specify a foreign key column you addForeignKeyColumn() methods provided by the adapter. They take four or five parameters:

column	Denotes the column in your table that has the foreign key relationship. In the example this would be categoryName.
fkTableName	Denotes the table corresponding to the foreign key. In the example this would be Categories.
fkColumn	Denotes the column within the foreign key table. In the example this would be categoryNumber.
displayExpression	A SQL expression to select a unique value from the foreign key table that is then displayed. Usually this is a single column, but it could also hold a more complex expression. In the example we could simply set it to categoryName
displayName/config	The final parameter takes either a String or a GridEditorColumnConfig object. If a string is specified this is used as the display name for the column. If a config object is specified this will be used as configuration for the column. If neither is specified then the column name is set to column.

We could thus specify the relationship as follows in groovy code:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productNumber')
adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName','Category')
           .addColumns('productSupplier')

return definition;

Similarly, you could additionally define the foreign key relationship also for the productSupplier column.

Usually, you would additionally define an editor that allows users to select a category. For this, you can specify a config object that is used as fifth argument. For example something along the following lines (note also the additional imports).

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.editor.*

import groovy.sql.Sql

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productNumber')

// define config for categories
def categoryConfig = new GridEditorColumnConfig(displayName: 'Category')
def categoryEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT categoryName AS name FROM Categories ORDER BY 1' ){
        categoryEditor.addValue(it.name)
    }
} finally{
    connection.close();
}
categoryConfig.setEditor(categoryEditor);

adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName',categoryConfig)
           .addColumns('productSupplier')

return definition;

In the above example we used the attribute categoryName to display category names instead of category numbers. Assume, that we wanted to display the suppliers as LASTNAME, FIRSTNAME. For this we can use SQL expressions in place of the display expression. For example, in MySQL to select LASTNAME, FIRSTNAME we can use the following query

SELECT CONCAT(supplierLastName, ", ", supplierFirstName) FROM Suppliers

Our example would, thus, change as follows (note the changes in the second to last line)

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.editor.*

import groovy.sql.Sql

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productCode')

// define config for categories
def categoryConfig = new GridEditorColumnConfig(displayName: 'Category')
def categoryEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT categoryName AS name FROM Categories ORDER BY 1' ){
        categoryEditor.addValue(it.name)
    }
} finally{
    connection.close();
}
categoryConfig.setEditor(categoryEditor);

adapter.addColumns('productCode', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName',categoryConfig)
           .addForeignKeyColumn('productSupplier','Suppliers', 'supplierNumber', 'CONCAT({{table}}.supplierLastName, ", ", {{table}}.supplierFirstName)','Supplier')

return definition;

The change should be somewhat unexpected as we additionally added the string {{table}} twice. This becomes necessary since behind the scenes ReportServer needs to create a complex SELECT statement that joins together the foreign key tables. The replacement {{table}} is then used to plug in the correct temporary table name. Of course, we could also for the supplier add an editor. Here we would not need {{table}} replacement. Instead, the code for the editor is straight forward:

def supplierEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT CONCAT(supplierLastName, ", ", supplierFirstName) AS name FROM Suppliers ORDER BY 1' ){
        supplierEditor.addValue(it.name)
    }
} finally{
    connection.close();
}

Of course, you can also define foreign key columns via the fluid API. For this, use the fk method. Additionally, to add the "default" selection list editor for a foreign key, i.e., the selection list that displays all possible choices you can use the withFkEditor method. To further fine tune the selection you may us the withFkEditorWhere(...) method which takes as input a where clause that is added to the underlying SQL query.

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'Products')
    .setPk('productCode')
    .columns()
        .add('productCode')
        .add('productName')
        .add('productCategory')
           .fk('Categories','categoryNumber','categoryName')
           .withFkEditor()
        .add('productSupplier', 'Supplier')
           .fk('Suppliers', 'supplierNumber', 'CONCAT({{table}}.supplierLastName, ", ", {{table}}.supplierFirstName)')
    .done()

return adapter;