Erik Cole Acclaimed Contributor.
Acclaimed Contributor.
302 views

add optional parameters to Excel Report

Does anyone know how to add optional parameters to an Excel Report, where the SQL statement is in the template?

0 Likes
1 Reply
jphahn Trusted Contributor.
Trusted Contributor.

Re: add optional parameters to Excel Report

Hello

For us, the following query wotks fine.

<jt:forEach items="${reportingdb.execQuery('SELECT hproj.KNTA_PROJECT_NAME, hproj.P_SAP_NUMMER, pt.TASK_NAME, r.USER_NAME, r.FIRST_NAME ||\\' \\'||r.LAST_NAME NAME, ou.ORG_UNIT_NAME, ae.EFFORT_DATE,\\'KW-\\'||to_char(ae.EFFORT_DATE,\\'IW\\') WOCHE, to_CHAR(ae.EFFORT_DATE,\\'Mon\\') MONAT, to_char(EFFORT_DATE,\\'YYYY\\') JAHR, ae.EFFORT_HRS FROM RPT_FCT_TM_ACTUAL_EFFORT ae JOIN RPT_DIM_RM_RESOURCES r ON (ae.RESOURCE_ID = r.RESOURCE_ID) JOIN RPT_DIM_ORG_UNITS_MV ou ON (r.PRIMARY_ORG_UNIT_ID = ou.ORG_UNIT_ID) LEFT JOIN   RPT_REQ_PFM___PROJECT hproj ON (ae.TASK_PROJECT_ID = hproj.KNTA_PROJECT_PLAN) JOIN RPT_DIM_PM_TASKS pt ON (ae.TASK_ID = pt.TASK_ID) WHERE 1= 1 AND hproj.REQUEST_TYPE_ID IN (30961, 31000) AND INSTR(hproj.KNTA_PROJECT_MANAGER, ?) > 0 ORDER BY ae.EFFORT_DATE ASC',PM)}"var="actuals">

The parameter PM is passed as question mark. It is defined in the report commands as follows:

ksc_run_excel_report ProjectAbsences_OR.xlsx
DEBUG=FALSE
REPORT_ID=[RP.REPORT_SUBMISSION_ID]
PM=[RP.CREATED_BY]
PMU=[RP.CREATED_BY_USERNAME]
USERNAME=[SYS.FULL_NAME]
USERID=[SYS.USERID]
ksc_end_report_parameters

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.