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:
- Create a new report titled
EmployeeList-CH6.rptDesign. - Create a new Data Source from the Classic Cars Sample Database.
- Create a new Report Parameter and call it
rprmEmployeeJobTitle. Set the Data type toString, and change the Display type to Combo Box.
- Notice that the dialog box changes. We can set the Selection list values option to either Static or Dynamic. Setting the option to Static implies that we need to input these values ourselves at design time, whereas setting to Dynamic implies the values will be generated based on the results of a dataset. The advantage to this is that we can have a back-end database someplace that we can simply insert values into and they will autopopulate this list. So, it can be populated from an existing table in our database. The disadvantage is that we need a database, which can go offline, and we need to maintain it. Therefore, the wiser option is to choose Dynamic.
- Here are all the options we can set for dynamic generation. If we already have a dataset, we can base the dynamic generation off of that. In this example, we will create a new, dedicated dataset strictly for populating our parameters. Go ahead and click the Create New button.
- Name the dataset as
dsEmployeeJobTitlesand use the following SQL statement.select distinct CLASSICMODELS.EMPLOYEES.JOBTITLE from CLASSICMODELS.EMPLOYEES
- Click Finish in the SQL Editor.
- 8 Click OK and exit out of the Edit Data Set dialog. We will now be back at the New Parameter dialog with the dataset already filled in to our new dataset. From the two drop-down boxes, chose JOBTITLE for both the value and the display text. Why are there two—the reason is for normalized databases. We can choose an ID for the value and a string to be displayed to the user. For example, if we were choosing a status code for defect tracking, we would have things such as Fixed, New, and In Process as possible status values for an issue, and these would have ID numbers such as 1, 2, and 3. We don't expect the user to know the ID codes, so we would display the names instead. The following is a screenshot of the dialog with the values filled in and the dataset created:
- As the Default value, use Sales Rep.
- Click OK.
- From the Data Set Explorer, create a new dataset called
dsEmployeeList. Use the following SQL Statement:select * from CLASSICMODELS.EMPLOYEES
- In the Edit Data Set dialog, select the Filters category. Create a new Filter.
- Under Expression, select JOBTITLE from the drop-down list.
- Under Value 1, select<Build expression> from the drop-down list.
- In the Expression Editor, under Category, select Report Parameters, under Sub-Category select All, and then select the Report Parameter as
rprmEmployeeJobTitle. Double-click it until it appears in the textbox. Once there, click OK.
- Drag-and-Drop the
dsEmployeeListfrom either the Data Explorer or from the Outline. - Preview the report.
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.