I have created a new post for the above issue, as I couldn't post my suggestion here for some reason...
Hope its helpful
Thank you for your answer, but I had seen this explanation but does not help in my case because I have a column that tells me if the day is a work day or not, and have to do it in answer.
Example: Start Date = 2013-09-02
End Date = 2013-09-30
If 2013-09-11 and 2013-09-19 are holidays, my column to have return 18 days.
I have a column with start date of campaign, other with end date and the column "Work Day" whith flag "Yes" or "NO".
If you have "Working Day" flag why don't you use filter to exclude "N" then simply count
We can try to create a function in DB which returns the Number of Working Days on passing the Start Date and End Date of Campaign Calendar
By you saying there is a column "Work Day" with flag "Yes" or "NO", I assume there is a table called Campaign Calendar which may provide this info like if each and every day is working day or not.
02-SEP-13 00:00:00 YES
03-SEP-13 00:00:00 YES
04-SEP-13 00:00:00 YES
05-SEP-13 00:00:00 YES
06-SEP-13 00:00:00 YES
07-SEP-13 00:00:00 NO
08-SEP-13 00:00:00 NO
If the above is the case, then try to create a function as below which return number of working days between two dates
FUNCTION DATE_DIFF(ST DATE, ED DATE) RETURN NUMBER
IS NO_OF_WDAYS NUMBER;
DT1 DATE := ST; ---VARIABLE TO HOLD START DATE
CHR1 VARCHAR2(10); ----VARIABLE TO HOLD FLAG FOR EACH DATE
HOLIDAY NUMBER := 0; ----COUNTER WHICH GIVES US NUMBER OF HOLIDAYS BETWEEN TWO DATES INCLUDING SATURDAYS AND SUNDAYS
NO_OF_WDAYS := TRUNC(ED - ST);
SELECT Work_Day /* THE COLUMN WHICH HOLDS THE FLAG*/
FROM CAMPAIGN_CALENDAR /* THE TABLE WHICH HAS THE INFO */
WHERE DATE_COLUMN = DT1;
IF(CHR1 = 'NO') THEN
HOLIDAY := HOLIDAY + 1;
DT1 := DT1 + 1;
EXIT WHEN DT1 > ED;
NO_OF_WDAYS := NO_OF_WDAYS - HOLIDAY;
Once the above function is written, use Evaluate function in the RPD and pass both the columns as parameters.
EVALUATE('DATE_DIFF(%1,%2)' ,Campaign Start Date,Campaign End Date)
Whenever the column gets pulled in the report, the above db function will be fired hence resulting in Number of Working days.
Hope its helpful
Thank for your answer, I will test,
I think it´s Ok, but I get this error, --> A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
Thank you very much, I will go configure the file and test again.
I use this function somewhat simpler:
create or replace
startdate IN NUMBER
, enddateIN NUMBER
) RETURN NUMBER AS
select count(*) into diff from dim_time
where id_date >= startDate
and id_date < endDate
and flag_Work = 'Y';
1 person found this helpful
The error: A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)
It is because the Evaluate parameter in NQSConfig.ini file wasn't setup appropriately.
Please go to the following path:
Open the NQSConfig.INI file in Edit mode
Find the following lines:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 0;
Change the line as
EVALUATE_SUPPORT_LEVEL = 2;
Restart your BI Services
Mark if it is helpful
Thank you. I wil do this.