Trouble setting up Input Controls

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 Tasks
FROM TASK
INNER JOIN PROJECT ON PROJECT.PROJECT_ID = TASK.PROJECT_ID
AND PROJECT.POURCENTAGE < 100
AND PROJECT.PROJECT_TYPE < 6
AND PROJECT.PROJECT_STATUS_ID < 3
INNER JOIN PROJECT_TYPE ON PROJECT.PROJECT_TYPE = PROJECT_TYPE.PROJECT_TYPE_ID
INNER JOIN STATUS_TYPE ON TASK.STATUS_TYPE_ID = STATUS_TYPE.STATUS_TYPE_ID
INNER JOIN PRIORITY_TYPE ON TASK.PRIORITY_TYPE_ID = PRIORITY_TYPE.PRIORITY_TYPE_ID
AND 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_NAME
order by 2, case status_type_name
  when 'To do' then 1
  when 'In Progress' then 2
  when 'To Verify' then 3
  else 4
  end

How should the query for the input control and the parameter be set up in this case? 

rdp9090's picture
Joined: May 28 2017 - 2:12pm
Last seen: 5 years 8 months ago

1 Answer:

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.

elizam's picture
13944
Joined: Mar 5 2012 - 9:19am
Last seen: 2 years 7 months ago
Feedback
randomness