Introduced in BIRT 2.3 is a graphical query designer that is available every time a designer uses a data store. The idea is to provide a visual representation of tables and their relationships for report designers that may not be as fluent in SQL. This also gives developers a chance to quickly create a general SQL statement that can be refined without having to type code. In 2.5, this was expanded to be available from the JDBC Database Connection for Query Builder type of Data Source. Let's now follow these steps and create data:
Create a new report titled dataStoreExample.rptDesign.
Create a new Data Source.
Choose the Create from a connection profile in profile store option in the New Data Source window.
In the Connection Profile dialog, click the New button.
On the next screen, we have the option to create a new data profile. This would require us to know the JDBC URL to connect to the database. For this example, just select BIRT Classic Models Sample Database. Select a file to store the configuration.
We can now use this file any time we want to connect to this database. The information is already filled out in the Connection Profile dialog when we return to it. Click on Next to continue.
On the next dialog, we can set JDBC parameters for this instance of a connection. There is nothing to set for this example, so click on Finish.
Right-click on the Dataset icon in the Data Explorer and choose New.
In the next dialog, we will notice a new Data Set Type: SQL Select Query [Query Builder Prototype]. We can also see the profile information in our Data Source selection. Enter a name for our Dataset.
We now get a query dialog that is similar to Microsoft Access query builder. To add tables to the dialog, right-click on the middle pane.
Add the EMPLOYEES and OFFICES tables. Select the OFFICECODE field from the EMPLOYEES table and drag it over to the OFFICECODE field in the OFFICES table. This will modify the query to create a join. Then, click on the checkboxes next to EMPLOYEENUMBER, LASTNAME, and FIRSTNAME in the EMPLOYEES table, and OFFICECODE and CITY in the OFFICES table.
We can now use these datasets in the same manner as we would any of the others.