Report Data Sources - Reportizer Documentation

Data source is one of the most important parts of the report. It contains information about the data used in the report.

Reportizer data sources

Reportizer works with relational databases (Paradox, dBase, Oracle, SQL Server, Microsoft Access etc.) and list-based data sources (for example, list of files and folders from specified directory).

Reportizer can work with relational databases through the interface of the following database engines: ADO, FD, Interbase, BDE. Some data sources can be opened only by one of these engines (for example, UDL files can be opened only by ADO). But some data sources can be opened by more than one engine (for example, dBase (DBF) files can be opened by ADO and BDE; Interbase databases can be opened by all the four engines). The choice what engine to use is done by user considering the presence of the engine on the computer, experience and personal preferences. See the descriptions of the database engines below.

Another important property of the data source is a database kind (see descriptions below).

Relational databases consist of tables. Data from the database tables are used by reports and can be extracted through the tables itself or by database queries (which is written in SQL).

You can specify the data source for report while creating new report in New Report wizard. You can change the data source for existing report in Data Source page of main window.

Select Data Source dialog

Use Select Data Source dialog as the first step in constructing the data source for your report. You can invoke it by clicking '...' button near Database input field.

Here, you have several areas to work with:

Interface. Specify interface (database engine) to use for opening the database. Each interface can open different types of databases and might have different ways to open databases and work with them.

ADO
Works with different types of databases like ODBC data sources, multi-table database files like .mdb, .xlsx etc, or folders with text, CSV, dBase, Paradox, or XML files. Alternatively, advanced users can describe the above mentioned databases manually by specifying connection strings for them. In internet, there is a lot of sources with examples of connections strings for specific types of databases.

ADO is usually installed by default in Windows 2000 or above. It can also be downloaded as MDAC package from Microsoft web site.

Check the ADO status by choosing Help | About... menu (System page).

FD
Works with different types of databases (mainly server databases like Oracle, SQL Server, Interbase etc).

Requires the corresponding database clients installed on your computer.

Interbase
Works with Interbase and (with some limitations) Firebird databases.

Requires Interbase client installed on your computer.

Check the Interbase status by choosing Help | About... menu (System page).

BDE
Works with different types of databases like ODBC data sources, BDE aliases, multi-table database files like .gdb etc, or folders with text, CSV, dBase, or Paradox files.

Requires BDE 5.0 or above installed on your computer.

Check the BDE status by choosing Help | About... menu (System page).

File system Represents information about folders and files of computer file system.

Data source. Here you specify the parameters of the database to open. For FD and Interbase interfaces, just fill the list of connection parameters (required parameters are highlighted by colored text). For ADO and BDE interfaces, the connection parameters depend on database kind:

ODBC data source or BDE alias
Select a data source from the drop-down list. You can edit the list by clicking the corresponding buttons to the right.

Note: Invoking the ODBC editor requires running Database Tour with administrator permissions.

Data file
Select a physical file (for example, of DB, DBF, MDB type etc.). You can create a blank MDB or UDL file here.

After clicking Open button, the folder with the selected file (except multi-table files), will be automatically opened as a database and the selected file will be opened as a table of this database. If a multi-table file (for example, MDB or GDB) was selected, then the file will be opened as database.

Folder with database files like .DB, .DBF etc
Select a physical folder with database files.

After that, the selected folder will be opened as a database and the database files, which it contains, will be shown as tables.

Note: If you open folder via ADO interface, select the correct driver for the database from the list of available drivers; for example, if you wish to open folder with .DBF files, then select dBase driver.

ADO connection string
Specify a connection string manually or by using connection string builder.

For some file types, folders or ODBC data sources, the connection string can be generated automatically. To automatically generate the connection string, select needed ODBC data source, file or folder in the corresponding field and click Convert to connection strings button; then, the list of available connection strings for selected database will appear in Connection string field; select the most suitable one from the list.

Advanced users can improve the connection string by manually adding extended parameters prior to clicking the Open button.

Database alias. Specify a unique friendly alias for the database. This will help you easily find the database in future. If you check the Temporary option, the database will be removed from the list of registered databases after disconnecting.

To simply open a previously used database, just select it from the alias list without changing any other other options.

Opening data source

Data source can be opened either automatically (when the report is previewed or printed) or manually by Open / Close Data Source button on Data Source page. It is also possible to open data source in Report Builder (it is useful, for example, to access field or parameter names) by double-clicking the corresponding area in status bar.

Closing data source

Data source is closed either automatically each time the user selects another report or changes data source parameters, or manually on Data Source page.

If the data source (either database or data set) of report is changed (for example, when you change SQL text), then you will be asked to test the data source before template's saving. To avoid the testing, press Ctrl+Shift when clicking Save button.

When working with some data source types, additional components are required. For example, to open Microsoft Access 2007 database (.accdb) or Microsoft Excel 2007 workbook (.xlsx), Microsoft ACE must be installed.

after opening Excel workbooks (.xls, .xlsx) via ADO or via ODBC DSN, you may see no tables in the file. If you are sure the file has data, try to open it by it's native application, assign name(s) to needed cell range(s), save, and then try to open it again. If the result will unsuccessful, try to open the file by direct specifying connection string with different parameters: for example, change ODBC-based string to Jet-based one (the differences are shown in examples of connection strings).

If your report data source uses text or CSV files, it is recommended to create schema for each file. Schema describes field structure and some other properties of data file. Schema files must be placed in the folder, where data files are located. For BDE connections, schema file must have the same filename as the data file, but with .SCH extension. For ADO connections, the schemas for all data files from certain folder are always kept in one file file named Schema.ini. For other details about creating the schemas, please read your database engine documentation.

See also

Query Examples

Examples of ADO Connection Strings

Working with Command Line

Error Messages

Using SQL Queries

ADO	Works with different types of databases like ODBC data sources, multi-table database files like .mdb, .xlsx etc, or folders with text, CSV, dBase, Paradox, or XML files. Alternatively, advanced users can describe the above mentioned databases manually by specifying connection strings for them. In internet, there is a lot of sources with examples of connections strings for specific types of databases. ADO is usually installed by default in Windows 2000 or above. It can also be downloaded as MDAC package from Microsoft web site. Check the ADO status by choosing Help \| About... menu (System page).
FD	Works with different types of databases (mainly server databases like Oracle, SQL Server, Interbase etc). Requires the corresponding database clients installed on your computer.
Interbase	Works with Interbase and (with some limitations) Firebird databases. Requires Interbase client installed on your computer. Check the Interbase status by choosing Help \| About... menu (System page).
BDE	Works with different types of databases like ODBC data sources, BDE aliases, multi-table database files like .gdb etc, or folders with text, CSV, dBase, or Paradox files. Requires BDE 5.0 or above installed on your computer. Check the BDE status by choosing Help \| About... menu (System page).
File system	Represents information about folders and files of computer file system.

ODBC data source or BDE alias	Select a data source from the drop-down list. You can edit the list by clicking the corresponding buttons to the right. Note: Invoking the ODBC editor requires running Database Tour with administrator permissions.
Data file	Select a physical file (for example, of DB, DBF, MDB type etc.). You can create a blank MDB or UDL file here. After clicking Open button, the folder with the selected file (except multi-table files), will be automatically opened as a database and the selected file will be opened as a table of this database. If a multi-table file (for example, MDB or GDB) was selected, then the file will be opened as database.
Folder with database files like .DB, .DBF etc	Select a physical folder with database files. After that, the selected folder will be opened as a database and the database files, which it contains, will be shown as tables. Note: If you open folder via ADO interface, select the correct driver for the database from the list of available drivers; for example, if you wish to open folder with .DBF files, then select dBase driver.
ADO connection string	Specify a connection string manually or by using connection string builder. For some file types, folders or ODBC data sources, the connection string can be generated automatically. To automatically generate the connection string, select needed ODBC data source, file or folder in the corresponding field and click Convert to connection strings button; then, the list of available connection strings for selected database will appear in Connection string field; select the most suitable one from the list. Advanced users can improve the connection string by manually adding extended parameters prior to clicking the Open button.