Dynamic Grouping Using Parameter

Dynamic Grouping using Parameter: Passing parameters in the group by statement

Prerequisites: JasperStudio 6.3.0
Database: Foodmart

There might be requirements, where we need to group the records dynamically i.e., pass the parameter in the group.

Following are the steps to follow to ensure the parameters are passed in the group by statement.

Step 1: Create a report

Step 2: Create 2 parameters as follows

Default Value Expression for groupBy parameter:

($P{criteria} == "store") ? "employee.store_id":($P{criteria} == "department")?"employee.department_id":"1=1"

Step 3: Add the below query in the main dataset

SELECT	CASE	WHEN $P{criteria} = 'store' THEN $P!{groupBy}
				WHEN $P{criteria} = 'department' THEN $P!{groupBy}
		END AS Store_or_Department,
		COUNT(employee.employee_id) AS employee_count
FROM employee
GROUP BY $P!{groupBy}
ORDER BY $P!{groupBy}

Step 4: Design the report as per the requirement

When “store” is passed in the criteria parameter, the report fetches the employee count grouped by store

When “department” is passed in the criteria parameter, the report fetches the employee count grouped by department

In case if you have any queries please get us at support@helicaltech.com

Thank You
Sai Kavya Sathineni
Helical IT Solutions Pvt Ltd