We have seen how we can work with database-driven data and report parameters to filter data using parameter binding between Report Parameters and Data Set Parameters. We may ask ourselves, why did we go through the first exercise when the second exercise was so much easier? This is because this concept of binding through expressions is used a lot in BIRT.
Report Parameters may require manual input from a user, but we can also have prepopulated drop-down boxes for Report Parameters that are created at design time or even populated from a database or other dataset. This is useful for cases where the input needs to be exact and it is assumed that the user does not know all of the possible values or exactly what information they are looking for.
While filtering of database data can be achieved through Data Set Parameters and the filtering done on the backend database, other types of datasets such as the XML, Flat File, and Scripted Data Sources do not have that capability. So, how can we filter that data based on user input? The answer is by binding the Report Parameter to a Filter expression. We may recall from the last chapter that we were able to filter an XML Data Set by using a specialized expression to retrieve only those employees who had the job title of DEVELOPER. In this next example, we are going to create a simple Employee listing report using the Employees table and, instead of using a WHERE
clause, we are going to use a Filter to limit the returned results. We are also going to bind this Filter to a Report Parameter that will have the possible job titles prepopulated from a database query. Please note, just like with the Report Parameter and Data Set Parameter binding, there is a hard way to do it and an easy way. Let's cover only the easy way:
EmployeeList-CH6.rptDesign
. rprmEmployeeJobTitle
. Set the Data type to String
, and change the Display type to Combo Box. dsEmployeeJobTitles
and use the following SQL statement.select distinct CLASSICMODELS.EMPLOYEES.JOBTITLE from CLASSICMODELS.EMPLOYEES
dsEmployeeList
. Use the following SQL Statement:select * from CLASSICMODELS.EMPLOYEES
rprmEmployeeJobTitle
. Double-click it until it appears in the textbox. Once there, click OK. dsEmployeeList
from either the Data Explorer or from the Outline.Now, when we bring up the Show Report Parameters dialog, we have a drop down with all the possible jobs for the employees. When we select one and click OK, it will filter down to employees with that job.
The example that we just discussed used a database dataset instead of one of the static ones. In the case of a database, we usually want to use a dataset parameter instead of a Filter. Using a WHERE
clause and a dataset parameter will always outperform a Filter as the data is being filtered on the end of the DBMS. The DBMS is much more efficient at filtering data than BIRT would be. If we pulled a large dataset, BIRT would have to retrieve that entire dataset and apply the filter to it, wasting network bandwidth and processing time. Filters are useful in cases where we don't have a database to work with, such as a text file data source, or XML data source. In those cases, there is no DBMS to process a where clause and we have no choice but to use a Filter. But in either case, we have seen how the user can influence the data that gets returned as the earlier example will apply to the scripted data source, the flat file data source, and the XML data source.