none
timesheet status report, including not started RRS feed

  • Question

  • I'm trying to create a timesheet status report. I'm having great success regarding any timesheet that has been created, but I need help to add in the timesheets that haven't even been created yet. I know this information must be in Project Online somewhere, because the timesheet tiles you can have from the webpart "track my work" include the counts of timesheets that haven't been created in the unsubmitted count, I just cannot find how to access this data through reporting.

    Much appreciated,

    Ian


    Ian

    Tuesday, July 7, 2015 3:17 PM

Answers

  • 4 queries

    1) ResourcesTimephasedDataSet

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/ResourceTimephasedDataSet?$select=ResourceId,TimeByDay,BaseCapacity&$filter=BaseCapacity gt 0"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.StartOfWeek([TimeByDay], Day.Monday)),
        #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "StartOfWeekWcapacity"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"TimeByDay", "BaseCapacity"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"StartOfWeekWcapacity", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [StartOfWeekWcapacity] < DateTime.Date(DateTime.LocalNow()))    
    in
        #"Filtered Rows"

    2) Timesheets

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Timesheets?$select= TimesheetStatusId,StatusDescription,TimesheetOwnerId,StartDate"),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}})
    in
        #"Changed Type"
     

    3) Resources (I have a custom enterprise field called "employmenttype" included. I also wanted to display only the child department of Resource Departments, so there's some splits and removes included.

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Resources?$select=ResourceId,ResourceName,ResourceTimesheetManageId,ResourceDepartments,EmploymentType&$filter= ResourceType eq 2 and ResourceIsGeneric ne true"),
        #"Split Column by Delimiter" = Table.SplitColumn(Source,"ResourceDepartments",Splitter.SplitTextByEachDelimiter({"."}, null, true),{"ResourceDepartments.1", "ResourceDepartments.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ResourceDepartments.1", type text}, {"ResourceDepartments.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ResourceDepartments.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ResourceDepartments.2", "ResourceDepartments"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"ResourceTimesheetManageId"},#"Renamed Columns",{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ResourceName"}, {"NewColumn.ResourceName"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.ResourceName", "TimesheetManager"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"ResourceTimesheetManageId"})
    in
        #"Removed Columns1"

    4) Bringing all those queries together, ALL_Timesheets. It includes two columns for status... one detailed, and the other I'm calling summary which groups into approved, submitted, and not submitted (which includes anything not of the first two statuses. This would mean that if you don't force a timesheet manager and have two or more possible approval steps that "acceptable" status would be included in "not submitted" in this report, but you could change that to be whatever you need.)

    let
        Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
        #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Not Yet Created",Replacer.ReplaceValue,{"NewColumn.StatusDescription"}),
        #"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"ResourceId"},Resources,{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}, {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn2",{"ResourceId"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"StartOfWeekWcapacity", "WeekStarting"}, {"NewColumn.StatusDescription", "Status"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ResourceDepartments", "TimesheetManager", "ResourceName", "WeekStarting", "Status"}),
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ResourceDepartments", Order.Ascending}, {"TimesheetManager", Order.Ascending}, {"EmploymentType", Order.Ascending}, {"ResourceName", Order.Ascending}, {"WeekStarting", Order.Ascending}}),
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "SummaryStatus", each if [Status]="Approved" then [Status] else if [Status]="Submitted" then [Status] else "Not Submitted")
    in
        #"Added Custom"

    For the first three queries, load them to "Connection only", and the fourth, load to table and data model. I'm doing two tabs, one for the table, and one for a power view report.


    Ian


    • Marked as answer by Ian Bruckner Thursday, July 16, 2015 2:30 PM
    • Edited by Ian Bruckner Thursday, July 16, 2015 3:48 PM had an extra comma in the last let statement of the first query.
    Thursday, July 16, 2015 2:29 PM
    • Marked as answer by Ian Bruckner Friday, July 17, 2015 2:25 PM
    Friday, July 17, 2015 2:25 PM

All replies

  • And actually, I have done it using a pivot table and telling it to display rows without values, but I need the data in table form so that timesheet managers can filter the report based on dates and their name, without using a pivot table.

    Ian

    Tuesday, July 7, 2015 3:19 PM
  • Hi Ian,

    Prasanna did an excellent blog post about timesheet reporting with odata, including not created timesheets:

    http://www.prasannaadavi.com/2014/06/timesheet-status-report-with-odata-in.html


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Tuesday, July 7, 2015 3:28 PM
    Moderator
  • Thanks, but no. That's not helpful as he uses a pivot table. I need the data in raw table form so that timesheet managers can filter the report based on dates, their own name as a timesheet manager, and status. A pivot table could give me a good overview, but it is not useful on an operational basis.

    Ian

    Tuesday, July 7, 2015 3:36 PM
  • Ian,

    If you click on the Pivot Table and click Insert Slicer in the Analyze tab, the timesheet managers can dynamically filter based on whichever fields you designate as slicers. I normally insert a row to host my slicers.

    If the concern is the formatting, try this.

    • Click on the Design tab
    • Select Report layout
    • Show in Tabular form
    • Select Report layout
    • Repeat item labels
    • Select Subtotals
    • Do Not Show Subtotals

    This gives you a table layout with the power of the Pivot Table.

    Hope this helps.

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog | Learn Resource Management

    Tuesday, July 7, 2015 4:26 PM
    Moderator
  • Thanks, Treb, but that doesn't get me much further. I've already tried displaying the data as a flattened pivot table, but the "values" are always numerical. For example, I can't display timesheet status in a resource name (x) by date (y) format.

    Ian

    Tuesday, July 7, 2015 8:07 PM
  • Turns out this couldn't be done in Project Server 2010 either, and Microsoft Support confirms that's the case in Project Online. I made a feature request, and if you agree this would be good for you, please vote for the idea:

    http://office365.uservoice.com/forums/264636-general/suggestions/8750512-project-online-reporting-include-not-yet-created


    Ian

    Tuesday, July 7, 2015 8:09 PM
  • You can create a custom formula in the Pivot Table that translates the status into a number. Then use the icon sets in conditional formatting to indicate the status. Essentially, there's four. Not created, created not submitted, submitted not approved and approved. So you could go grey, red, yellow, green to show progression.

    I've written this report before using a Pivot table and it served the purpose. I'll have to see if I can dig it up.

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog | Learn Resource Management

    Tuesday, July 7, 2015 11:20 PM
    Moderator
  • Hi Ian,

    Not sure to understand why a pivot table is not suitable. It will allow the resource manager to filter on date (timesheet periods) and their name like with a table. Also you could use a current user filter to automatically return their team's timesheets based on their RBS. Just a though..


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, July 8, 2015 7:11 AM
    Moderator
  • Thanks for the reply, Guillaume. A pivot table won't work because I would be unable to display actual values because you can only do numerical things there like sums, maximums, counts, etc. The pivot table then also would always need as many columns as there are time periods, or some other measure. That would be ok if we could manage to have everyone be within 1 or two or even 5 time periods of being submitted and approved... but that's been a very big challenge here, so this report would need to easily display in a concise method (not scrolling horizontally) actionable information for supervisors. This means showing them only what they need and no more, as in a simple list/table.

    Pivot tables would work at a high level, but to make that information actionable for the supervisors given our culture, it would need to meet these requirements.


    Ian

    Wednesday, July 8, 2015 1:15 PM
  • Ian, are you talking about something like this? This is from one of the PowerBI  Designer exercises I'm covering in my upcoming Resource Management class. This report is quite doable with Project Online and doesn't require pivot tables.

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog | Learn Resource Management

    Wednesday, July 8, 2015 5:20 PM
    Moderator
  • Thanks for the reply, Treb, but no, not quite. That functions much the same as a pivot table in that we're looking at 2 dimensions of data, Resource X Week. I would need the information in list form, so that there would be lets say, 3 columns:

    ResourceName | TimePeriod (or i'll use EndDate) | Status

    The problem with this, of course, is that Project Online reporting does not offer a status of "Not Yet Created". I'm thinking its pretty important to have that field directly from Project Online, because you cannot assume that every active resource should have a timesheet entry for all time periods... you'd have to introduce logic like making sure you only have a row if the Timesheet period began after the resource creation date, or after the resource earliest available date if one exists, or even further, only if the resource availability was positive during specific timeframes... because when you edit a resource in ms project you can enter in distinct periods... such as gone over the summer, then back in the fall, then gone again in the spring, etc.

    Project Online does take those times into account for displaying the number of timesheets unsubmitted for the user and for their timesheet manager on the timesheet and timesheet approval tiles, so I know the smarts are in there somewhere... they just are available directly through reporting.


    Ian

    Wednesday, July 8, 2015 5:59 PM
  • Ian,

    Ok, you sucked me into a great challenge but I've got it working, at least in PowerBI Designer. :-)

    The solution assumes that:

    • The timesheet period starts on Saturday, Sunday or Monday
    • You are only looking at previous weeks from today
    • Timesheet periods are not shown for a resource which are dated prior to their creation
    • Timesheet periods are not shown when Timesheet period capacity is 0 or less
    • I used Start Date but you could easily substitute End Date
    • I'm ignoring a few other checks for the time being that would be necessary for a client ready report

    This PWA instance was just created so only two periods are visible at the moment. If I remove the Created Date filter, more periods show. Molly Dempsey below only has capacity for the first timesheet period as she is no longer with us in the second period.

    This solution also allows the use of Administrative time periods and show that those weeks still require a timesheet.

    The M code required was a lot less complex than I expected so that's a good thing.

    The final output looks like this:

    Now, the challenge is to decide how best to present this solution as there are a number of logic steps, based on detailed knowledge of the reporting store. I may do this as a webinar instead of a blog post, so that you can see the building of the report as I talk through the process.

    Let me work the logistics and I'll post a link to the webinar shortly.

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog | Learn Resource Management

    Thursday, July 9, 2015 12:37 AM
    Moderator
  • Treb, that looks very promising! Your assumptions and descriptions made how to create this a report a little clearer to me. I'm going to give it a whirl now, but I'd also be appreciative of seeing your writeup/ webinar.

    Thanks again!


    Ian

    Friday, July 10, 2015 3:19 PM
  • Well, Treb, I'm still too green within Power Query to make any headway. I have it all in my head how I think I'd fit it together, but am not familiar with this new language (m code). I'm very excited to see how you made it work. I'm thinking this example will go a long way towards helping me understand!

    Ian

    Monday, July 13, 2015 2:32 PM
  • nvm, got it!

    I'll post details soon.


    Ian

    Monday, July 13, 2015 3:33 PM
  • I'm scheduling my webinar for next week as I'm slammed this week. I'm curious to see what you did. Send me an email at treb dot gatte at tumbleroad dot com and let's talk.

    The link to the webinar is here: http://ccst.io/e/projectonlinetimesheetaudit

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog | Learn Resource Management

    Monday, July 13, 2015 6:13 PM
    Moderator
  • 4 queries

    1) ResourcesTimephasedDataSet

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/ResourceTimephasedDataSet?$select=ResourceId,TimeByDay,BaseCapacity&$filter=BaseCapacity gt 0"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.StartOfWeek([TimeByDay], Day.Monday)),
        #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "StartOfWeekWcapacity"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"TimeByDay", "BaseCapacity"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"StartOfWeekWcapacity", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [StartOfWeekWcapacity] < DateTime.Date(DateTime.LocalNow()))    
    in
        #"Filtered Rows"

    2) Timesheets

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Timesheets?$select= TimesheetStatusId,StatusDescription,TimesheetOwnerId,StartDate"),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}})
    in
        #"Changed Type"
     

    3) Resources (I have a custom enterprise field called "employmenttype" included. I also wanted to display only the child department of Resource Departments, so there's some splits and removes included.

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Resources?$select=ResourceId,ResourceName,ResourceTimesheetManageId,ResourceDepartments,EmploymentType&$filter= ResourceType eq 2 and ResourceIsGeneric ne true"),
        #"Split Column by Delimiter" = Table.SplitColumn(Source,"ResourceDepartments",Splitter.SplitTextByEachDelimiter({"."}, null, true),{"ResourceDepartments.1", "ResourceDepartments.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ResourceDepartments.1", type text}, {"ResourceDepartments.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ResourceDepartments.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ResourceDepartments.2", "ResourceDepartments"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"ResourceTimesheetManageId"},#"Renamed Columns",{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ResourceName"}, {"NewColumn.ResourceName"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.ResourceName", "TimesheetManager"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"ResourceTimesheetManageId"})
    in
        #"Removed Columns1"

    4) Bringing all those queries together, ALL_Timesheets. It includes two columns for status... one detailed, and the other I'm calling summary which groups into approved, submitted, and not submitted (which includes anything not of the first two statuses. This would mean that if you don't force a timesheet manager and have two or more possible approval steps that "acceptable" status would be included in "not submitted" in this report, but you could change that to be whatever you need.)

    let
        Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
        #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Not Yet Created",Replacer.ReplaceValue,{"NewColumn.StatusDescription"}),
        #"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"ResourceId"},Resources,{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}, {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn2",{"ResourceId"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"StartOfWeekWcapacity", "WeekStarting"}, {"NewColumn.StatusDescription", "Status"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ResourceDepartments", "TimesheetManager", "ResourceName", "WeekStarting", "Status"}),
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ResourceDepartments", Order.Ascending}, {"TimesheetManager", Order.Ascending}, {"EmploymentType", Order.Ascending}, {"ResourceName", Order.Ascending}, {"WeekStarting", Order.Ascending}}),
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "SummaryStatus", each if [Status]="Approved" then [Status] else if [Status]="Submitted" then [Status] else "Not Submitted")
    in
        #"Added Custom"

    For the first three queries, load them to "Connection only", and the fourth, load to table and data model. I'm doing two tabs, one for the table, and one for a power view report.


    Ian


    • Marked as answer by Ian Bruckner Thursday, July 16, 2015 2:30 PM
    • Edited by Ian Bruckner Thursday, July 16, 2015 3:48 PM had an extra comma in the last let statement of the first query.
    Thursday, July 16, 2015 2:29 PM
    • Marked as answer by Ian Bruckner Friday, July 17, 2015 2:25 PM
    Friday, July 17, 2015 2:25 PM
  • Hey Ian,

    I recorded a short video "The most interesting Time Sheet Audit Report", showing the end product of my PowerBI Desktop version of this report here:

    https://youtu.be/R2iaS39M5WM

    I rebuilt it the night PowerBI Desktop was released to get the latest functionality, including Power Q&A capabilities. Webinar on how to build it is awaiting resolution of a security issue on the video platform. 

    Treb Gatte, Project MVP | Blog | Twitter | CIO Magazine Blog

    Tuesday, July 28, 2015 11:48 PM
    Moderator
  • Could you please share the query of this table with me ?
    Thursday, March 7, 2019 7:55 AM