Creating a custom datasource

bsilverm

Hello All,

I did a lot of research and work before posting here, so please excuse me if I am asking something that I should have already read by now.

I am looking to create a good datasource I can use to create some BIRT reports against one of my applications that has a database back-end. I've created a JDBC datasource, however am only seeing the ability to execute SQL statements. What I want to do, is build a "schema" in BIRT so that I can choose from a pre-defined set of values in my back-end database. I want to define these values however, rather than use the table names and field values. For example, if one of my tables is "employees", with a field of "first name", I want to be able to create a dataset, and be presented with a list of values (ie - Employee -> FirstName). I have created a connection profile, and tried using that but it is very generic and does not appear I can customize the data source. I have looked into creating an ODA plugin, and have used the plugin wizards which generated a lot of sample java code. I feel I am missing something, or do I truly have to write java code in order to get my data source customized the way I want it? Can someone please set me straight? There is a lot of documentation, however I am not sure what I need to do to proceed. Thanks all!

- Ben

Virgil Dodson

Hi Ben,

Can you elaborate a bit more on what you want to see? The way I understand it, you can create SQL today to a JDBC datasource, but you would rather present to the BIRT report designer person, a list of table and field names rather than have them type SQL. If this part is correct, then there are a couple of options.

First of all, you can have someone who is effecient at SQL design a bunch of queries and have them stored in a library. The person designing the report can then choose a DataSet by it's friendly name (e.g. HR Payroll Data) and then select fields from that for the report. A downside to this approach is that the query may pull more data than is actually used on a report.

Another option that is supposed to be coming with BIRT 2.3 is a GUI query builder where users grab a table and can select fields from it. Here you will not have to know SQL to get data.

Another option you mentioned above is to use the ODA capability of BIRT to define your own custom data source/set. You can get started by using the the BIRT project wizards for creating a new ODA Runtime, and a ODA Designer project. This will create a simple ODA that you can modify. In this case, you can create any sort of screens you want, like a picklist of all the fields available to a user, and then you control the SQL going back to fetch the resultset.

Finally, a commercial alternative could be used. For BIRT reports that are deployed to the Actuate iServer Express, there is a feature/product called Information Objects. These Information Objects can be used as a data source for a BIRT report. They are very similar to what you described above where you create a schema of available tables and fields available to users and they are able to pick from these fields to create the report. Some automatic query trimming happens so that only the fields needed are selected at runtime. The Information Objects are available in the BIRT Designer for report developers... and you can also use the Information Objects with the Web Based BIRT Designer for End users.