Week begin date in a OTBI pivot table report

ravinat

I built a pivot report in Project Financial Management that reports approved labor hours by resource and task by week. While there is a function to report the hours by week, it allows you to report by week. It reports the week number starting from Jan 1 - it reports weeks 1..52. This does not help users as they have to decode when each week begins. I have not found a functionality that can help decode the week begin date for each week.

This is the function I use for the calendar week:

WEEK_OF_YEAR("Expenditure Item and Cost Distribution Details"."Expenditure Item Date")

Is there a way to get the week begin date for the week in question?

I have attached the report generated by the OTBI report. For privacy reasons, the project number, name, resource name and task names have been changed.

RN - Weekly Project Hours by task and resource for a period.xlsx

ravinat

Found this by a Google search and tested it. It works:

TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN("Expenditure Item and Cost Distribution Details"."Expenditure Item Date")) + 1, MIN("Expenditure Item and Cost Distribution Details"."Expenditure Item Date"))