Format data representation with Alfabet instructions

You can use Alfabet instructions to alter the display of datasets resulting from Alfabet queries or native SQL queries.

If instructions may be combined with native SQL, a tab for the definition of instructions will be available in text editors and the report assistant. If the tab is not available, then it is not permissible to define instructions with native SQL for the respective configuration.

When writing an Alfabet query directly in a text editor, all instructions are written in a section that starts with Instructions and ends with EndOfInstructions:

• Instructions
• any of the instructions specified below;
• any of the instructions specified below;
• EndOfInstructions

When writing a native SQL query directly in a text editor, the instructions must be separated from the native SQL query with the comment /* Alfabet Instructions */:

• /* Alfabet Instructions */
• any of the instructions specified below;
• any of the instructions specified below;

However, in the Alfabet Query Builder and in the tabs for instructions definition in combination with native SQL, the instructions are defined without the delimiters Instructions and EndOfInstructions.

Please consider the following when defining instructions:

• To define instructions, the Show properties of the Alfabet query must be defined as an XML element. When using the Alfabet Query Builder, this is automatically done.
• It is not allowed to use whitespaces in the instruction outside of string specification of the delimiter.
• It is not allowed to use line breaks within the instruction.
• Each instruction must end with a semi-colon.
• You can specify multiple instructions in the same Instructions section.
• When defining instructions, you must specify column names to identify the columns that the instruction applies to. For information about the column names resulting from Alfabet query and native SQL query definitions, see the section ATO: Defining Column Names and Captions. Alternatively, instructions can refer to columns by their index number. For more information see ATO: Referencing Columns in Instructions by Index Number.
• Instructions are executed in the order that they are specified. This can be crucial for the instruction definition (for example, if an instruction refers to a column that has been renamed by an instruction that is executed earlier). An exception to this rule is the coloring of cells in the report via ColorAssignment and RowColorAssignment instructions. The ColorAssignment instruction supersedes the RowColorAssignment independent from the order of specification. If you define the GroupBy_Ex instruction in combination with instructions that are assigned to rows in a report, for example to define link assignment or coloring of cells or rows, the GroupBy_Ex instruction must be defined first and the instruction for formatting of the rows must defined after the GroupBy_Ex instruction.

• When defining native SQL queries the following sequence of execution of a native SQL query must be considered when defining the native SQL query:

  1. Alfabet parameters in the Alfabet query are translated to corresponding native SQL code and a result set is created.
  2. Alfabet instructions are executed on the result set. This may influence the availability and display of columns in the result table.
  3. The first column in the result table after the execution of instructions is not displayed.

To specify instructions in the Alfabet Query Builder or the Instructions tab of the SQL query editor of Alfabet Expand:

1. Go to the Instructions tab.
2. Click the New Instruction button, select the sub-menu containing the instruction and select the instruction from the drop-down list.

    

3. Either an example instruction will be written in the text editor or an editor will open. If the example is written in the text editor, change the example as needed. If an editor opens, enter the required values in the editor controls.

We're working on it. Stay tuned!