ParameterizeReports

Parameterize reports

1. Add parameters to your report.

Mostly report template contains parameters that you enter when running a report. There are various types of input parameters for different types of data however all parameters passed to Aperte Reports should be a java.lang.String class. When you add parameter in iReport tool, you have to set up some required properties.

2. Input parameter properties.

Each parameter is transformed into a Vaadin field. Parameter attributes specify how to view it and suggests possible to choose values??. Property input_type defines type of Vaadin control to be diplayed. In example below it will be a simple text field.

Complete list of types can be found in system specification.

3. Add dictionary to parameter.

For example, prepare a report of using Vies where you will be able to select user from a list. Add username parameter where input_type=select to new report, then add another property - dict_query where value is simmilar to below.

java:comp/env/jdbc/unirep; select distinct(username), username as displayed_username from aperte-reports_report_order

Query for text field should always return two columns. First value goes to the report and the second is displays to user. All available parameters attributes are described in system specification.

4. Use a parameter in your report query.

To display selected user activity, add the following condition to query from previous example.

o.username = $P{username}

Entire query should look like follows.

select
	t.reportname,
	count(o.id),
	to_char(o.start_date, 'day') as day
from
	vries_report_order o,
	vries_report_template t
where
	t.id = o.report_id
	and o.start_date >= now() - justify_days(interval '7 days')
	and o.username = $P{username}
group by
	t.reportname,
	to_char(o.start_date, 'day')

Add Bar chart component to report and connect it to a values. Don't forget about datasource parameter.

5. Run report and input parameter value.

Put report into Aperte Reports manager and click Generate. You'll see a selection list with users names.

Choose user from a list and click again Generate button. The Report for selected user will appear.

6. Add more parameters and properties.

Complete the parameters and properties according to the following.

• parameter username | property | value | |:-----------|:--------| | input_type | select | | dict_query | java:comp/env/jdbc/unirep; select distinct(username), username as displayed_username from vries_report_order | | label | user name selection | | required | true | | required_error | user name is required! |

This will add a label and exist validation. The next parameter will be displayed as a checkbox and enabling you to filter reports sended via email.

• parameter email_flag | property | value | |:-----------|:--------| | input_type | checkbox | | label | show only reports sent to email |

Now add parameters limiting time interval.

• parameter date_from | property | value | |:-----------|:--------| | input_type | date| | label | input start date |

• parameter date_to | property | value | |:-----------|:--------| | input_type | date| | label | input end date |

Modify the query taking a parameters into account.

select
        t.reportname,
        count(o.id),
        to_char(date_trunc('day', o.start_date), 'YY.MM.DD') as day
from
        vries_report_order o,
        vries_report_template t
where
        t.id = o.report_id
        and ($P{date_from} = '' or o.start_date >= to_date($P{date_from}, 'DD-MM-YYYY HH24:MI'))
        and ($P{date_to} = '' or o.start_date <= to_date($P{date_to}, 'DD-MM-YYYY HH24:MI'))
        and o.username = $P{username}
        and (o.recipient_email is null = ($P{email_flag} = 'false') or o.recipient_email is not null)
group by
        t.reportname,
        date_trunc('day', o.start_date)

Upload report template into Aperte Reports and run it. You will see added checkbox and date fields, which will influence the outcome of the report.

• Next chapter