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