[#5402] - Usage of parameter in query which is not inputcontrol for the report produces empty inputcontrol

Category:
Bug report
Priority:
Normal
Status:
Assigned
Project: Severity:
Major
Resolution:
Open
Component: Reproducibility:
Always
Assigned to:

Bug in Jasperserver inputcontrols with parameter usage

Problem
Usage of parameter in query which is not inputcontrol for the report produces empty inputcontrol

Case:
In resourcefolder we defined following inputcontrols:
• DEPARTMENT_OPTIONAL -> parameter: $P{DEPARTMENT_OPTIONAL}
• DEPARTMENT_MANDATORY -> parameter: $P{DEPARTMENT_ MANDATORY}
• SUBDEPARTMENT -> parameter: $P{SUBDEPARTMENT}

Query for DEPARTMENT_OPTIONAL – value DEPARTMENT_CODE
SELECT
DEPARTMENT_CODE
, DEPARTMENT_NAME
FROM ORGANISATION

Query for SUBDEPARTMENT – value SUBDEPARTMENT_CODE
SELECT
SUBDEPARTMENT_CODE
, SUBDEPARTMENT_NAME
FROM ORGANISATION
WHERE 1=1
AND CASE WHEN $P{DEPARTMENT_OPTIONAL} IS NOT NULL THEN DEPARTMENT_CODE=$P{DEPARTMENT_OPTIONAL} ELSE 1=1 END

We would like to include the following:
AND CASE WHEN $P{DEPARTMENT_MANDATORY} IS NOT NULL THEN DEPARTMENT_CODE = $P{DEPARTMENT_MANDATORY} ELSE 1=1 END

Main Problem:
If the parameter in the query is not one of the inputcontrols on the report then the query does nothing (the inputcontrol is completely empty)

For this we have to create the following set of inputcontrols:
• SUBDEPARTMENT_ DEPARTMENT_OPTIONAL
• SUBDEPARTMENT_ DEPARTMENT_MANDATORY

For usage in combination on report A:
• DEPARTMENT_OPTIONAL
• SUBDEPARTMENT_ DEPARTMENT_OPTIONAL

For usage in combination on report B:
1. DEPARTMENT_ MANDATORY
2. SUBDEPARTMENT_ DEPARTMENT_ MANDATORY

Bigger problem:
• We have sometimes reports with 5+ parameters/inputcontrols depending on each other, however these inputcontrols are in mixed usage for different reports

Due to the main problem we have to make all kind of inputcontrols for all kind of combinations.

Our request:
Allow usage of parameters in the inputcontrol queries which are not used as an inputcontrol on the report. (the parameter has to be defined as parameter on the report)

In that case we can do:
Inputcontrols
1. DEPARTMENT_MANDATORY
2. SUBDEPARTMENT_OPTIONAL
3. EMPLOYEE_MANDATORY

Query for EMPLOYEE_MANDATORY – value EMPLOYEE_CODE
SELECT
EMPLOYEE_CODE
, EMPLOYEE_NAME
FROM ORGANISATION
WHERE 1=1
AND CASE WHEN $P{DEPARTMENT_OPTIONAL} IS NOT NULL THEN DEPARTMENT_CODE=$P{DEPARTMENT_OPTIONAL} ELSE 1=1 END
AND CASE WHEN $P{DEPARTMENT_MANDATORY} IS NOT NULL THEN DEPARTMENT_CODE = $P{DEPARTMENT_MANDATORY} ELSE 1=1 END
AND CASE WHEN $P{SUBDEPARTMENT_OPTIONAL} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_OPTIONAL} ELSE 1=1 END
AND CASE WHEN $P{SUBDEPARTMENT_MANDATORY} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_MANDATORY} ELSE 1=1 END

ruudheemskerk's picture
Joined: May 2 2010 - 11:11pm
Last seen: 6 years 6 months ago

1 Comment:

#1

Thanks for catching it! The defect has been logged in internal tracker.

Feedback
randomness