ruudheemskerk Posted August 15, 2011 Share Posted August 15, 2011 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_CODESELECT DEPARTMENT_CODE, DEPARTMENT_NAMEFROM ORGANISATION Query for SUBDEPARTMENT – value SUBDEPARTMENT_CODESELECT SUBDEPARTMENT_CODE, SUBDEPARTMENT_NAMEFROM ORGANISATIONWHERE 1=1AND 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_ MANDATORY2. 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:Inputcontrols1. DEPARTMENT_MANDATORY 2. SUBDEPARTMENT_OPTIONAL3. EMPLOYEE_MANDATORY Query for EMPLOYEE_MANDATORY – value EMPLOYEE_CODESELECT EMPLOYEE_CODE, EMPLOYEE_NAMEFROM ORGANISATIONWHERE 1=1AND CASE WHEN $P{DEPARTMENT_OPTIONAL} IS NOT NULL THEN DEPARTMENT_CODE=$P{DEPARTMENT_OPTIONAL} ELSE 1=1 ENDAND CASE WHEN $P{DEPARTMENT_MANDATORY} IS NOT NULL THEN DEPARTMENT_CODE = $P{DEPARTMENT_MANDATORY} ELSE 1=1 ENDAND CASE WHEN $P{SUBDEPARTMENT_OPTIONAL} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_OPTIONAL} ELSE 1=1 ENDAND CASE WHEN $P{SUBDEPARTMENT_MANDATORY} IS NOT NULL THEN SUBDEPARTMENT_CODE = $P{SUBDEPARTMENT_MANDATORY} ELSE 1=1 END Best regards, Ruud Heemskerk Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now