My report currently generates a stacked bar graph, where it lists every project in the database (each associated with a project_type), and the bars represent the number of tasks for the project in each status (To Verify, To Do, In Progress).
I would like to add a function for users in Jasperreports Server to filter which projects they want to see using a checkbox or list to select the project_type for which they want to see data for. I tried using a multi select query input control as I searched online, but the examples used simpler queries and I was unable to apply the same concept to my own report. My query for the report currently looks like
SELECT PROJECT_TYPE.PROJECT_TYPE_NAME, PROJECT.PROJECT_NAME, STATUS_TYPE.STATUS_TYPE_NAME,COUNT(*) AS TasksFROM TASKINNER JOIN PROJECT ON PROJECT.PROJECT_ID = TASK.PROJECT_IDAND PROJECT.POURCENTAGE < 100AND PROJECT.PROJECT_TYPE < 6AND PROJECT.PROJECT_STATUS_ID < 3INNER JOIN PROJECT_TYPE ON PROJECT.PROJECT_TYPE = PROJECT_TYPE.PROJECT_TYPE_IDINNER JOIN STATUS_TYPE ON TASK.STATUS_TYPE_ID = STATUS_TYPE.STATUS_TYPE_IDINNER JOIN PRIORITY_TYPE ON TASK.PRIORITY_TYPE_ID = PRIORITY_TYPE.PRIORITY_TYPE_IDAND NOT PRIORITY_TYPE.PRIORITY_TYPE_NAME = 'Hold'AND STATUS_TYPE.STATUS_TYPE_NAME IN ('To do','In Progress', 'To Verify')GROUP BY PROJECT_TYPE.PROJECT_TYPE_NAME, PROJECT.PROJECT_NAME, STATUS_TYPE.STATUS_TYPE_NAMEorder by 2, case status_type_namewhen 'To do' then 1when 'In Progress' then 2when 'To Verify' then 3else 4end
How should the query for the input control and the parameter be set up in this case?
What did you try to do for an input control? I don't think the complexity of the query affects how you set up the input control. You just want a simple filter on the PROJECT_TYPE_NAME field. The query controls which data is displayed, and the input control functions within the confines of that data.