looks problem is with multiple selection of param values in data model , what is the bip version and post screen shots of LOV query and set up in parameter section.
Do you know how frequent the report is getting failed , the issue is with cascaded parameters (refresh parameters on change) , please check :p_bu does not have any data issues like in place of number there nay be symbols in the data. does all the parameters are fixed values from database or keep on changing ?
please check :p_bu does not have any data issues like in place of number there may be symbols--> BU has no symbols (only number data type and 3 options are available for single select not multiple) but the other parameters have special characters like the Department / Class / Sub-Class / Zone and Vendor prompts, many of them have one of those 5 symbols ' . & ( ) on the description.
in the data. does all the parameters are fixed values from database or keep on changing ? It is very very rare that the description changes and the IDs always stays the same.
Issue might be due to cascaded parameters that some parameters depends on other. Is the scheduled report or on demand ? please gather param values when report got failed. better schedule the report and enable diagnostic log file in scheduler page when the report is failed then you will know where exactly the report is getting stopped and which param is causing problem.
We use the report on demand.
When the report fails it fails before the parameters load.
I will schedule the report and hope to get information on which parameter is failing.
Thanks for the suggestion, I will update the thread with the results.
It seems to fail between 1pm and 3pm.
the scheduler was was running fine every 5 minutes and now it failed with the following error.
Job processor caused exception
[INSTANCE_ID=srv-bidev-01.1489010620834] [INSTANCE_JOB_ID=2977]::::ERROR_GETTING_REPORT_DATA::[INSTANCE_ID=srv-bidev-01.1489010620834] IOException:[INSTANCE_JOB_ID=2977] Invalid parameters requested.
oracle.xdo.servlet.data.DataException: Invalid parameters requested.
It does mentions "Invalid parameters requested". What I find strange is the fact that many reports works fine with similar data model setup and the report itself was working this morning..
What are the best practices when it comes to cascaded parameters?
It's 4:15pm - Report is up and running again without no apparent changes
Job Name : test
Job Executed Date : May 25, 2018 4:16:51 PM EDT Status : Job was successfully processed
Ivalid Parameters occure when you refer to other parameters which are null inside of another LOV.
Add NVLs or rework your parameter "hierachy"
I do pass null to pass all values using the least function. Using native BIP setup to pass all values doesn't work since their is more than 999 different values and form my understanding if we choose "all value passed" we are limited. The only workaround I found to pass more than 999 selection is to use the least function and pass it as null, like this:
select distinct CLASS_ID || '-' || CLASS_DESCR cls
where (least(:P_DEPT) is null or DEPARTMENT_ID || '-' || DEPT_NAME in (:P_DEPT))
and BUSINESS_UNIT_ID = (:P_BU)
ORDER BY TO_NUMBER(regexp_substr(CLS,'^[0-9]+')),LOWER(CLS)
It does work but break from time to time, if it's due to the parameters queries, what options do we have to resolve this issue?
could you able capture queries when report job is failed? then you can easily find the error what causing error , while scheduling report there is option in diagnostic tab to choose log files , select last option then schedule report. whenever report failed , upload log file using report failed time stamp, you can find queries fired for that failed report. Thanks
How do we capture queries when report job is fails?
while scheduling report I did not see a diagnostic tab to choose log files, the only tabs I see are "General" "Output" "Schedule" and "Notification"
Where do I find the log file to upload and check for the timestamp?
you have to logon with administrator user