This topic introduces the connection types Designer supports and provides an example to illustrate how you can set up a JDBC connection in Designer.
Designer requires access to your data source to retrieve data for reports, so before you can create reports in Designer, you need to set up a connection to your data source. You can define, name, and store connections in the catalog along with the reports and other resources that use them.
Designer supports the following connection types:
|JDBC||You can use this connection type to connect to a relational database via a JDBC driver.|
|JSON||You can use this connection type to connect to a JSON data source and transform the schemas in the data source to relational schemas.|
|XML||You can use this connection type to connect to and transform an XML hierarchy model to a relational model.|
|SOAP Web Service||You can use this connection type to connect to a SOAP Web Service data source by importing a WSDL file. Designer supports SOAP Web Services defined by WSDL 1.1 or WSDL 2.0 as data sources.|
|MongoDB||You can use this connection type to connect to a MongoDB database and transform the collections in the database to relational schemas.|
|Hive||You can use this connection type to connect to a relational database stored in a Hive data warehouse via a JDBC connection.|
|Elasticsearch||You can use this connection type to connect to an Elasticsearch data source and transform the schemas in the data source to relational schemas.|
|User Defined||Through the User Data Source API, Designer can access data from an external data source, such as a Text file or Excel file, which is not stored in a database or when there is no JDBC driver available.|
|Hierarchical||Designer directly supports XML data source by wrapping the provided Hierarchical Data Source API. Designer's built-in classes can implement the XML hierarchical data source interface. You can directly import an XML data source to a catalog.|
In the following example, we connect to a fictitious MS SQL Server database named SQLData, which runs on a server with the host name db.example.com. The user name and password used to access the database are "dbuser" and "test1234" respectively. To connect a relational database to Designer, generally you need to install the JDBC driver for the database and append its class path to Designer's environment configuration file first, then in Designer connect a catalog to the database by setting a JDBC connection. Designer provides connection plug-ins for the most commonly used relational databases: Oracle, MySQL, MS SQL Server, InterSystems IRIS, and PostgreSQL, for an easy connection setup procedure. Since we are using MS SQL Server in this example, we do not need to bother installing the driver as Designer already includes a plug-in for this database.
- Select Logi Report Designer in the Logi Report folder on the Start menu to open Designer.
- In the Start Page, select SQL Server in the Connect category.
- In the Create Connection to SQL Server dialog box, select New Catalog, specify the catalog name as SQLDemo.cat, then select OK.
You can set up connections in an existing catalog or in a new catalog. Here we chose to create a new catalog for the SQL Server database since it's our first database connection.
- In the prompt warning message, you can select Yes to save the current running catalog. Designer displays the Connect to SQL Server dialog box.
- Type the following database information in the dialog box:
- Select Test Connection to test whether the information you provide is correct.
- If the database has special requirements such as the date format, select More Options to modify the options according to your requirements.
- Select OK. Designer displays the Catalog Manager, showing the connection in the resource tree. At the same time, Designer prompts you to choose what you want to do next (if Designer does not display the dialog box, select Resume on the Catalog Manager toolbar).
- Select Add Tables in the Add Tables/SQL dialog box to add tables stored in the database into the Logi Report catalog.
- In the Add Tables/Views/Synonyms dialog box, select the required database catalog and schema, then select Refresh to load the tables.
- Select the tables you want to add to the Logi Report catalog and select Add.
- Add more tables from other database catalogs and schemas.
- Select Views/Synonyms to add views/synonyms from the database if it contains in the same way.
- Select Done to finish adding tables.
- The names of the added tables in the catalog resource tree are the same as those in the database. You can edit them to something more meaningful. To do this, close the prompt dialog box, then right-click a table and select Rename on the shortcut menu.
- The name text box becomes editable now. Type the new name and select Enter on the keyboard to confirm the change.