Retrieve Fiscal Year Dates Dynamically
At some point, you will be asked for information by Fiscal Year. The Fiscal calendar was set up in Project Server so it should be accessible for reporting, right? A fair number of companies have fiscal calendars which don’t exactly line up with the standard calendar which can cause some challenges. You could hard code the beginning and ending dates of the fiscal year, but then that would create an annual report maintenance task. What’s a report writer to do?
If you’ve set up the Fiscal calendar within Project Server, it is easy to retrieve this data for the current Fiscal Year based on the current date. The Fiscal calendar information is stored in the MSP_TimeByDay table and can be queried to do all sorts of date based operations.
The example provided below can be used with Excel based reports as well as with SSRS reports. The key is to do the lookup in two steps. The first part of the query retrieves the Fiscal Year beginning and ending date so that you can use this information in the WHERE clause of the following query to filter the dataset.
Let’s say I need to find out in real time, how many hours of work were scheduled by month for the Fiscal Year. The real-time requirement pushes me to use SQL rather than OLAP for this data. Since monthly data on assigned work and cost requires aggregation of the time phased assignment data, I need to query the Assignment By Day view (MSP_EpmAssignmentByDay_UserView) in the Reporting database. Production reports would require additional joins to Resource and Project views to complete the dataset but for illustration purposes, I’ll limit the example to only the Assignment By Day view for illustration simplicity.
The following query works in Excel where I find this data is most commonly requested.
The DECLARE statement declares the two variables where we will store the date results for the beginning and end of the current Fiscal Year.
The next SELECT statement uses a subquery to get the beginning and ending date of the current Fiscal Year. The subquery uses the current date to find the current Fiscal Year for use in the WHERE clause of the primary query.
You may be wondering what this clause is doing as you look at the code below. CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) This clause is needed to address the time mismatch between the TimeByDay data and the current time.
All of the dates in the TimeByDay field in the MSP_TimeByDay table are stored as dates with midnight time. If you retrieve the current date and time, the numbers won’t match if you attempt to use it as a filter as the time portion will be different. So, you have to reset the time part of the current time back to midnight for this filtering to work.
- GETDATE gets the current date and time.
- Casting the result as a FLOAT represents the date and time as a decimal number, where the portion to the left of the decimal is the date and the time is stored to the right of the decimal.
- The FLOOR statement sets the decimal portion of the number to its lowest value, which would represent midnight.
- The outer CAST makes it a Datetime field data type again so that we can use it to filter on a Datetime field. In the last query, you use the declared variables to find all records between the beginning and finishing date of the Fiscal Year. Note, this technique can also be used to get the current Fiscal Quarter, current Timesheet period, calendar month, etc. There is a fair number of data elements in the MSP_TimeByDay table that you can use for date related needs.
DECLARE @BEGINDT DATETIME, @FINISHDT DATETIME SELECT @BEGINDT = MIN(TimeByDay) , @FINISHDT = MAX(TimeByDay) FROM MSP_TimeByDay WHERE FiscalYear = (SELECT FiscalYear FROM MSP_TimeByDay WHERE TimeByDay = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) SELECT * FROM MSP_EpmAssignmentByDay_UserView WHERE MSP_EpmAssignmentByDay_ UserView.TimeByDay BETWEEN @BEGINDT AND @FINISHDT
WHERE DO I PUT THIS?
If you are using one of the default Excel report templates in the Project Server Business Intelligence Center, you would open the file in Excel client.
- Go to the Data tab
- Click Connections
- Select the connection and click the Properties button
- Go to the second tab and click into the SQL box
- Select all (Ctrl+A) and delete the existing SQL
- Copy the above query and paste into this box
- Click OK to update and OK on any warning
- Once updated, click Close to close the dialog
Once it returns the data, you would see the returned fields in the Excel field well next to the Pivot Table.
Looking for more Project Server information?
Visit the AboutMSProject.com Recommended Books page.
February 21, 2013 at 6:04 pm, serverpants said:
I love the “Where do I put this” portion on this post! Sometimes I wonder if I’m just leaving anyone who stumbles across my blog in the dark about that. I have been thinking about creating a “Start Here” page with basically that info in it, and linking to it at the bottom of any post where I hand out a query. (which is most of them, let’s be honest) Now I feel I must!
March 12, 2013 at 8:05 am, Brian McCool said:
Great article! There is a small problem with the parenthesis locations in the SQL. This line:
CAST(FLOOR(CAST(GETDATE() AS FLOAT) AS DATETIME))
CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
April 11, 2013 at 8:50 pm, Treb Gatte said:
Hey Brian and thanks for reading. Great catch and I’ve updated the article.