SQL databases

Connection settings Firebird, IBM DB2, MariaDB, Microsoft Azure SQL, Microsoft SQLServer, MySQL, Oracle, PostreSQL, SQLite

▪ Basic settings: Specify the server (<Server name>
[\<Instance name>][:<Port>] and select the type of authentication. Both SQL Server and Windows authentication are supported as methods for logging in.

Windows authentication represents the highest level of security and also uses Windows user accounts for the logging into the SQL Server.

SQL Server authentication (if such is supported by the server) will also allow external access.

▪ Expert mode: For special connection settings, you can enter the connection string manually.

Examples of connection strings:

Firebird: User=SYSDBA;Password=masterkey;Database=myDataBase.fdb; DataSource=localhost;Port=3050;Dialect=3;Charset=NONE; Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0; MaxPoolSize=50;Packet Size=8192;ServerType=0

Microsoft Azure SQL: Server=tcp:myServerName.database.windows.net; Database=myDataBase;User ID=myUsername@myServerName; Password=myPassword;Trusted_Connection=False;Encrypt=True;

Microsoft SQL Server: Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

PostgreSQL: Server=127.0.0.1;Port=5432;Database=myDataBase;User
Id=myUsername;Password=myPassword;

MySQL: Server=myServerAddress;Port=3306;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

In special cases it may be necessary to increase the timeout directly in the connection string. Information about connection strings and possible parameters, see https://www.connectionstrings.com.

Example for Postgres:

Server=127.0.0.1;Port=5432;Database=myDataBase;User Id=myUsername;Password=myPassword;CommandTimeout=20;Timeout=15;

▪ Full Database: all tables and views will be available, to which the specified connection has access.

▪ Single Queries: Define any number of custom SQL queries, whose results can then be used by reports.

▪ Add SQL Query: Enter the query. Test the query using the respective button. Any number of queries may be entered.

Example for a single query:

SELECT users.type, sale.date_finished::date AS date,

SUM(sale_position.amount) AS total_amount

FROM public.sale_position, public.sale, public.users

WHERE sale.date_finished IS NOT NULL

GROUP BY sale.date_finished::date, users.type

ORDER BY users.type, date;

▪ Add Query (Assistant): With some data sources (MS SQL, Oracle, MySQL, MariaDB, PostgreSQL, SQLite), a wizard is available for creating database queries. More information on the wizard is given in the section Query-Assistant.

▪ Add Routine (Wizard): For some data sources, routines (stored procedures, table value functions) can be added with the defined parameters (including support for data source parameters). These routines can also return data and be used directly in the Report Server.

▪ PostgreSQL: Table-Valued Functions (At least 2 columns within the table return)

▪ Schema: With some data sources (MS SQL, Oracle, DB2), you can specify a scheme to only load tables with this scheme. Depending on the type of data source, you can also specify multiple schemes (e.g., separated by commas). Particularly in relatively large databases, this can provide a clearer overview and increase processing speed.

▪ Relations: You can define the relations between the various queries or add further relations to the automatically determined relations. The database structure will be analyzed, such that that all the user has to do is simply select the relevant tables and fields. Proceed as follows:

5. Enter a table alias (relation name). This name is displayed below the 1:n linked child table beneath the parent table in the Designer.

▪ Chose the child table and the foreign key field that corresponds to the primary key of the parent table.

The option "Use schema prefix for table names" is available for several databases. A database schema allows a logical separation of tables. E.g. tables with the same name can be used within the same database.

The Wizard for single queries is a highly flexible method of easily creating queries so that only certain types of records are available as data sources. You can define and combine any number of conditions and also group conditions. The Wizard is available for the following data sources: Microsoft SQL, Oracle, MySQL, PostgreSQL, SQLite and MariaDB.

▪ In the Data section, choose the Single Queries option and click on Add Query (Assistant).

▪ Choose the table and the fields that will be passed to the query. This is equivalent to defining the SELECT <Fields> FROM <Table> section of the SQL query.

▪ By clicking on the green plus sign, a context menu appears where you can choose whether to add a condition (WHERE) or create a new group.

▪ By clicking on an operator, you can move the group up or down, move it into a new group or integrate it into a parent group.

Click on "Convert to SQL Query" to convert the query created in the assistant to a regular SQL query. This allows you to process the query directly using SQL instead of the assistant. The assistant is no longer available for this query after the conversion!

You can define any number of conditions and thereby access all of the displayed fields.

▪ To define a condition, click on the green plus symbol in the Query Assistant and then "Add Condition".

▪ In the "Fields" list, click on the field that contains the desired property.

The symbols show the respective field types (alphanumeric, numeric, date, logical).

▪ Select the desired operator from the "Operators" list. Operators that are permissible for the field selected will be displayed (e.g. equal to, not equal to, begins with, contains, is empty).

▪ Enter or select the property (Fixed value) to be searched for in the "Comparative value" box or compare the property with another field in the same table (Field).

▪ For date (time) fields, the following additional functions are available: Year(), Quarter(), Month(), Day(), Hour(), Minute(), Second(). Select one of the functions and enter a comparative value.

▪ To add an additional condition, click on the green plus sign. You now have the following options:

Multiple conditions are combined into a group and groups can also contain additional groups. They can be created in various manners and any number of hierarchies are supported:

▪ To create a new group, click on the green plus sign and select "Create new group" from the context menu.

▪ By clicking on an operator (e.g., "all"), you can move the group up or down, move it into a new group or integrate it into a parent group.

By default, for each group, the operator is set to "all". To change the operator, click on it and choose one of the logical operators: