Define a configured report providing export data

Configuration of the configured report is done in Alfabet Expand.

1. In the Reports tab, right-click the Reports folder or a sub-folder thereof and select Create New Report.
2. Click the new configured report and set the Type attribute to NativeSQL.
3. Define the following attributes:
   • Name: Change the technical name of the configured report.

     Rules for defining technical names

     Technical names should be unique and should not contain white spaces and special characters except underscore and hyphen. They are used to identify configuration objects unambiguously in technical processes.

   • Category: Select the use case category you defined for the generic API integration use case.
   • Native SQL: Open the editor and define the native SQL query for the configured report:

     Open an editor for an attribute

     Click in the attribute value field. A button for opening the editor appears on the right of the field.

     • The first column defined in the SELECT statement of the SQL query is ignored for technical reasons.
     • The report result is a simple table which includes both the parameters for the report call and the JSON data to be set in the body of the call. The parameters must be identical for all rows of the report.
     • If the exported data must be defined as JSON field containing an array, the complete array for the JSON field must be defined as text in the column returning the data for the JSON field. Variable values can be included between text elements:
       • '[ {"schemaNamespace": "default", "values": {"name": "'+APPLICATION.NAME+'", "objectstate": "'+ISNULL(OBJECTSTATE, '')+'"}}]' as 'objectData'

       The column must have the data type Text for the data mapping in the resource bundle definition and the Attribute definition of the ADIF export scheme.

     • The columns of the report dataset can have a name differing from the parameter names and JSON body field names. Mapping of the report data to the correct parameters and JSON fields is done in the ADIF configuration.
     • You can use Alfabet query parameters in the query to refer to the current environment, for example, to the current user or current user profile.

       Alfabet query parameters

       You can use Alfabet query parameters in both native SQL queries and Alfabet queries to refer to the current context in the Alfabet user interface. For example, to refer to the object the user is currently working with, the user executing the report, or the current workflow being executed in WHERE clauses. During execution of the Alfabet query, the Alfabet parameter is substituted with the current return value. If no value is returned, the Alfabet query is executed without the WHERE statement.

       Write Alfabet parameters as:

       • @ParameterName

       For example:

       • WHERE APPLICATIONGROUP.BELONGSTO CONTAINS @BASE

       finds all application groups subordinate to the application group the user is currently working with on execution of a native SQL query.

       The following Alfabet parameters can be used for Alfabet queries and native SQL queries:

       Parameter Name	Description
       BASE	Returns the value of the REFSTR property of the current object.
       CURRENT_PROFILE	Returns the value of the REFSTR property of the current user profile.
       CURRENT_USER	Returns the value of the REFSTR property of the current user.
       CURRENT_MANDATE	Returns the current mandate of the user. The Alfabet parameter can be used for example to limit search results to objects that are assigned to the mandate the user is currently logged in with, for example: ALFABET_QUERY_500 FIND ICTObject WHERE ICTObject.MANDATEMASK CONTAINS @CURRENT_MANDATE NOTE: The parameter CURRENT_MANDATE (and the whole statement it is used in) is ignored if the user has no mandate assigned or is working as mandate master.
       CURRENT_CULTURE	Returns the Microsoft® Windows® Locale identifier (LCID) of the current culture of the Alfabet user interface. Please note that the culture that is selected as default culture is always returned as 127, which means default, independent from the LCID of the culture.
       TODAY	Returns the current date.
       ALFA_SERVER_URL	Returns the value of the attribute Web Server of the server alias configuration of the Alfabet component processing the query. The attribute Web Server is editable in the Overview tab of the server alias configuration editor.
       BASE<number>	For queries defined in the context of layered diagrams and grid reports only! If the report is configured to allow cascading queries, and more than one level of related objects us defined, BASE0 returns the value of the REFSTR property of the parent object in the directly superordinate level of the diagram. BASE1 returns the ancestor object in the level superordinate to the parent object level etc.
       WIZARDBASE	For queries defined in the context of wizard configuration only! Return the value of the REFSTR property of the base object of the wizard. The base object of the wizard can be different from the base object of a wizard step. This parameter allows to refer to the base object of the wizard for example in pre-conditions of steps that have a different base object than the overall wizard.
       WORKFLOW	For queries defined in the context of workflow configuration only! Returns the value of the REFSTR property of the workflow. Each workflow corresponds to an object Workflow in the Alfabet database. The parameter is useful when defining queries that refer to properties of the class Workflow, like for example the workflow owner or the workflow initiator. .
       WORKFLOWBASE	For queries defined in the context of workflow configuration only! Returns the value of the REFSTR property of the base object of the workflow. The base object of the workflow can be different from the base object of a workflow step. This parameter allows to refer to the base object of the workflow for example in pre-conditions of steps that have a different base object than the overall workflow.
       LAST_EXECUTION	For queries defined in the context of monitors only. Returns the date of the last execution of the monitor.

4. In the toolbar, click the Save   button.
5. Right-click the report node and select Set Report State to 'Active'.
6. Right-click the report node and select Review Report. The report dataset is displayed in the Alfabet user interface and you can review whether the result is as expected.

   To change the report, you must first right-click the configured report and select Set Report State to 'Plan'. The final state of the configured report must be Active to use it for data import.