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".
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
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';
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