how to get all the dates enetered in the date range prompt in webi report even if not in DB table

[HiShilpiRanjan]

Hi,

I am using Webi and IDT 4.2 SP9.

I have two Database tables called Ttawc_Task_Work_Summary and AMS_Project_master.
In my IDT universe, to fulfull some requirement, a derived table based on these two tables is created as below:

SELECT  A.* , B.PRACTICE_GROUP

FROM TTAWC_TASK_WORK_SUMMARY A , AMS_PROJECT_MASTER B where A.FFF=B.FFF(+) AND A.close_date between B.EFF_FROM_DATE(+) and B.EFF_TO_DATE(+)and A.Close_Date BETWEEN to_date( @Prompt('1.From Date','D',,,)) and to_date( @Prompt('2.To Date','D',,,))

I have one more Database table called AMS_Employee_Master which is present in my universe and linked with the above mentioned Derived table as below:

"TTAWC_TASK_WORK_SUMMARY_Drvd".EMPLOY_ID=AMS_EMPLOYEE_MASTER.EMPLOYEE_CODE(+)
Cardinality set is (n,1)

Attached here is the sample of Ttawc_Task_Work_Summary table which is run for 9th to 21st sept 2024 just for two employ_Id: 210215 & 200291

If u see the excel, user 210215 has filled efforts from 9th to 12th Sept only
and user 200291 filled efforts for 9th to 15th Sept only.
So in excel, you will see only those Close_Dates for which user has filled efforts.

Now, the requirement is to create a webi report and find the dates for which the user has not filled efforts.

I need 4 columns in my report:
1. Employee_No: It will come from TTAWC_TASK_WORK_SUMMARY_Drvd table that I created above. I already created Business layer, Classes and objects and getting Employee_No in my report.

2. Entry date: It will also come from TTAWC_TASK_WORK_SUMMARY_Drvd table that I created above. I already created Business layer, Classes and objects and getting Entry_date in my report.
Employee_Name: it will come AMS_employee_master its already there in my report coming from Business layer.
Designation: : it will come AMS_employee_master its already there in my report coming from Business layer.

Now, the requirement is to create a webi report and find the dates for which the user has not filled efforts.

When I execute, webi report, It prompts for date range and Employee_ID,IF I enter
Date tange:9thSep2024 to 21st Sep2024
Employee Id: 210215 ; 200291
  it should show me only the dates for which user has not filled efforts.
So I need to see those dates only in my webi report, for which effort is not filled by the employee Ids entered in the prompt.

So here, as per the excel u see attached and prompt I entered for date range and user Id the webi report should show  the dates for which user has not filled efforts.
Attached Effort_gap_Details.xlsx is the output I need in report after entering the prompt I mentioned above and here below as well:

Prompt Date range:9thSep2024 to 21st Sep2024
Prompt Employee Id: 210215 ; 200291

Don’t give summary of any table sample attached.
Please help me with the way I can achieve this in webi report by creating variables or any other way  at report level.

since xlsx file is not allowed to attach above is Ttawc_task_Work_summary_sample data

and expected output is aatched below:

[nscheaffer]

I don't have time to work out the details now, but you should be able to generated a list of your desired dates in a free-hand SQL statement with a CTE. Then merge that to your main query on the date.

[jemstar]

Do you have a calendar table in your database? If so, create a third query over the calendar table with the same prompts as the query containing the entry date field. Merge dimensions on the entry date and the calendar table date. Use the merged dimension in the report to get all dates. If you don't have a calendar table, then create an excel spreadsheet with all the dates for every year and build your third query over the excel spreadsheet.