Dashboard Prompt for BI Publisher Reports

Question

Summary

Content

Hi all,

I have a question regarding dashboard prompt for BI Publishers.

It seems to work fine for Person Number but not Business Unit, Hire Date.

Here is the configuration:

And here are some details of the report:

Prompt :

code:

SELECT PN.FULL_NAME

, PP.PERSON_NUMBER

, TO_CHAR(PS.DATE_START, 'MM/DD/YYYY') AS HIRE_DATE

, MN.FULL_NAME AS MANAGER_NAME

, PJ.NAME AS JOB_NAME

, B.BU_NAME AS BUSINESS_UNIT

, OU.NAME AS ORGANIZATION_NAME

, LO.LOCATION_NAME

FROM PER_ALL_ASSIGNMENTS_F AG

INNER JOIN PER_PERIODS_OF_SERVICE PS ON AG.PERIOD_OF_SERVICE_ID = PS.PERIOD_OF_SERVICE_ID

LEFT JOIN PER_PERSON_NAMES_F PN ON PS.PERSON_ID = PN.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN PN.EFFECTIVE_START_DATE AND PN.EFFECTIVE_END_DATE

AND PN.NAME_TYPE = 'GLOBAL'

LEFT JOIN PER_PEOPLE_F PP ON AG.PERSON_ID = PP.PERSON_ID

AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE

LEFT JOIN PER_ASSIGNMENT_SUPERVISORS_F MR ON AG.ASSIGNMENT_ID = MR.ASSIGNMENT_ID

AND TRUNC(SYSDATE) BETWEEN MR.EFFECTIVE_START_DATE AND MR.EFFECTIVE_END_DATE

AND MR.PRIMARY_FLAG = 'Y'

LEFT JOIN PER_PERSON_NAMES_F MN ON MR.MANAGER_ID = MN.PERSON_ID

AND SYSDATE BETWEEN MN.EFFECTIVE_START_DATE AND MN.EFFECTIVE_END_DATE

AND MN.NAME_TYPE = 'GLOBAL'

LEFT JOIN PER_LOCATION_DETAILS_F_VL LO ON LO.LOCATION_ID = AG.LOCATION_ID

AND TRUNC(SYSDATE) BETWEEN LO.EFFECTIVE_START_DATE AND LO.EFFECTIVE_END_DATE

LEFT JOIN HR_ORGANIZATION_UNITS_F_TL OU ON OU.ORGANIZATION_ID = AG.ORGANIZATION_ID

AND TRUNC(SYSDATE) BETWEEN OU.EFFECTIVE_START_DATE AND OU.EFFECTIVE_END_DATE

AND OU.LANGUAGE = USERENV('LANG')

LEFT JOIN PER_JOBS PJ ON AG.JOB_ID = PJ.JOB_ID

AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE

LEFT JOIN FUN_ALL_BUSINESS_UNITS_V B ON B.BU_ID = AG.BUSINESS_UNIT_ID

AND TRUNC(SYSDATE) BETWEEN B.DATE_FROM AND B.DATE_TO

WHERE TRUNC(SYSDATE) BETWEEN AG.EFFECTIVE_START_DATE AND AG.EFFECTIVE_END_DATE

AND AG.ASSIGNMENT_TYPE = 'E'

AND AG.PRIMARY_FLAG = 'Y'

AND AG.EFFECTIVE_LATEST_CHANGE = 'Y'

AND (PS.ACTUAL_TERMINATION_DATE IS NULL OR PS.ACTUAL_TERMINATION_DATE > TRUNC(SYSDATE))

AND (LEAST(:P_BUSINESS_UNIT) IS NULL OR B.BU_NAME IN (:P_BUSINESS_UNIT))

ORDER BY PN.FULL_NAME

timdexter · Answer

Hi

You have not said what version you are running? Looks like 12c but what is the minor version?

How is not working? Is it being ignored by BIP?

You are using the least function in your SQL. Are you expecting multiple values for the prompt value? Why not limit the user to a single value?

If its working for Person Number in the BIP query, then what is different on the dashboard side of the equation ie the prompt set up?

Some more info will help us to help you

Cheers

Tim