none
Time Reporting in Excel for Project Server 2010 RRS feed

  • Question

  • Hi,

    I would like to create a report using time, which is ok but it isn't giving me quite what I want.

    I want a project list on the left (ok), and time by week. The values will be cost.

    I can get this, however the month ends for the weeks over run the months. So for example, one week could have two days in one month, and the rest in another.

    What I want, is for days to just be monday - friday like they are in project, so each week is 5 days, without splitting into two at any point.

    Hope this makes sense, finding it hard to write down!

    Tuesday, April 24, 2012 7:39 PM

Answers

  • I'm not sure why you haven't received an answer for this one yet - maybe not that many people use Excel for their reporting.  Here's the solution:

    Don't let SQL try to group weeks/months/years for you, just grab time by day in a query like this:

    SELECT
    MSP_EpmProject_UserView.ProjectOwnerName,
    MSP_EpmProject_UserView.ProjectName,
    MSP_EpmTask_UserView.TaskName,
    MSP_EpmAssignmentByDay_UserView.TimeByDay,
    MSP_EpmAssignmentByDay_UserView.AssignmentCost,
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,

    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmProject_UserView INNER JOIN
    MSP_EpmTask_UserView ON
    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

    INNER JOIN
    MSP_EpmAssignment ON
    MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

    INNER JOIN
    MSP_EpmAssignmentByDay_UserView ON
    MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.ResourceIsActive <>0) AND
    (MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ) AND
    MSP_EpmResource_UserView.RBS like '%staff%';

    (I love this query because it always starts on the Monday of the current week)

    Then in your pivot table, right click on one of the days in TimeByDay and click Group.  Select days, and tick the counter up to 7.  Make sure that the starting date is a Monday (or Sunday) and you should be golden.  We use this all the time and we even have conditional formatting set to show over allocated resources.  We use Excel Services and have page filters by department and user etc and this self-updating spreadsheet is a wonder.

    Now if only I could get it to sum the work by week and only return resources with more than 40 in any week...(that's what I was looking for when I happened across this thread)

    Hope that helps. 

    Elli

    Blog:  http://projectserverpants.wordpress.com/

    Tuesday, August 14, 2012 11:51 PM

All replies

  • Are you building this from one of the OLAP cubes or a SQL query datasource you created yourself?

    Can you post a screenshot of the Excel page with both the report you have and the field 'picker' controls on the right side? That might help us as well.


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, April 24, 2012 10:03 PM
    Moderator
  • Try importing from the reporting db MSP_EPMProjectTimeByDay view into Excel then create a Pivot table on the data.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Tuesday, April 24, 2012 11:54 PM
    Moderator
  • Try using TimeByDay field.

    Abhijeet M. Mohite

    Wednesday, April 25, 2012 7:18 AM
  • Hi,

    There is no ProjectTimeByDay listed in the reporting db (in any of the userviews), it only shows for assignment/resource ones?

    Wednesday, April 25, 2012 3:51 PM
  • Try this query:

    Select MSP_EpmProject_UserView.ProjectName, MSP_TimeByDay_OlapView.CalendarMemberNameYear, MSP_TimeByDay_OlapView.CalendarMemberNameMonth, 
    MSP_EpmAssignmentByDay_UserView.TimeByDay, MSP_EpmAssignmentByDay_UserView.AssignmentCost 
    From MSP_EpmProject_UserView Inner Join MSP_EpmAssignmentByDay_UserView 
    On MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID Inner Join MSP_TimeByDay_OlapView 
    On MSP_EpmAssignmentByDay_UserView.TimeByDay = MSP_TimeByDay_OlapView.TimeByDay


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Wednesday, April 25, 2012 5:25 PM
    Moderator
  • Hi thanks for that.

    It is close, however still not quite what we want.

    In this case, I run this query (similar to above);

    SELECT
    MSP_EpmProject_UserView.ProjectName AS [Project Name],
    MSP_TimeByDay_OlapView.CalendarMemberNameYear AS [Year],
    MSP_TimeByDay_OlapView.CalendarMemberNameMonth AS [Month],
    MSP_TimeByDay_OlapView.CalendarMemberNameWeek AS [Week],
    MSP_EpmAssignmentByDay_UserView.TimeByDay AS [Day],
    MSP_EpmAssignmentByDay_UserView.AssignmentCost AS [Cost],
    MSP_EpmAssignmentByDay_UserView.AssignmentActualCost AS [Actual Cost]

    FROM
    MSP_EpmProject_UserView
    INNER JOIN
    MSP_EpmAssignmentByDay_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay_UserView.ProjectUID
    INNER JOIN
    MSP_TimeByDay_OlapView ON MSP_EpmAssignmentByDay_UserView.TimeByDay = MSP_TimeByDay_OlapView.TimeByDay

    WHERE
    (MSP_EpmProject_UserView.ProjectName <> 'Timesheet Administrative Work Items')

    This returns the same sort if problem. Week 18 runs across April AND May, we don't want that. We basically want monday - friday weeks;

    Year Month Week
    2012 April Week17
    2012 April Week17
    2012 April Week17
    2012 April Week17
    2012 April Week18
    2012 April Week18
    2012 May Week18
    2012 May Week18
    2012 May Week18
    2012 May Week18

    Can this be done? Seems odd the fiscal element cannot be reported at week level.

    • Edited by Jumpy99 Saturday, April 28, 2012 2:34 PM
    Saturday, April 28, 2012 2:34 PM
  • Hi Project Learner

    Have you also tried using the OLAP cubes? You can get this information out using the OLAP cube. The reason why fiscal's are not on a weekly basis is because it is ment to map against your Financial month that you usually manage in your accounting system. Accounting systems have month end not week end. I hope this makes sense

    Thanks


    Marc Soester [MVP] http://marcsoester.blogspot.com

    Sunday, April 29, 2012 11:11 PM
    Moderator
  • Hi Marc

    Thanks - looked at the cube however 'time' seems to over run like mentiond above (e.g week 18 runs across April and May). Is there a pertiulcar field you mean that I am missing?

    Monday, April 30, 2012 8:19 PM
  • Anyone else got any ideas?
    Tuesday, May 1, 2012 7:12 PM
  • Why are you including week in your report? Numbered weeks will always span months. There is no query syntax or method around this. It just IS. If that is a problem then just stop including week in your report. Just show months and days.

    From your query it looks like you are trying to report on timesheet administrative tasks. In that case the assignment views are not going to be very useful anyway. Use the Timesheet views. There are several that will use in combination depending on what you want.


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    Tuesday, May 1, 2012 7:44 PM
    Moderator
  • HI Project Learner,

    well a week does go over month, that is just the way it is. However you can utilise the Month to get monthly reporting or you could even use the fiscal year dimension to generate the report. What exactly are you after?


    Marc Soester [MVP] http://marcsoester.blogspot.com

    Tuesday, May 1, 2012 11:16 PM
    Moderator
  • I have the same issue.  I need another time hierarchy field in the Assignment Time Phased Cube that only has year, week and day and not month so that week will not split between months.  I found the following article (http://projectserverblogs.com/?p=2115) but I am wondering if there is any other easier way to do this.  The Excel report that I am trying to create is like a resource usage view in project pro but with conditional formatting which can be used to identify resource over allocations. 
    Monday, May 7, 2012 8:36 PM
  • This is quite easy to achieve within Excel.Drag the time dimension into your coloum ( via Pivot Tables) Right click one of the years and select "Show/hide fields". This will allow you to hide any node of the dimension ( e.g. month). This should do the trick

    Thanks


    Marc Soester [MVP] http://marcsoester.blogspot.com

    Monday, May 7, 2012 10:35 PM
    Moderator
  • I'm not sure why you haven't received an answer for this one yet - maybe not that many people use Excel for their reporting.  Here's the solution:

    Don't let SQL try to group weeks/months/years for you, just grab time by day in a query like this:

    SELECT
    MSP_EpmProject_UserView.ProjectOwnerName,
    MSP_EpmProject_UserView.ProjectName,
    MSP_EpmTask_UserView.TaskName,
    MSP_EpmAssignmentByDay_UserView.TimeByDay,
    MSP_EpmAssignmentByDay_UserView.AssignmentCost,
    MSP_EpmResource_UserView.ResourceIsActive,
    MSP_EpmResource_UserView.ResourceIsGeneric,
    MSP_EpmResource_UserView.RBS,

    MSP_EpmResource_UserView.ResourceName

    FROM
    MSP_EpmProject_UserView INNER JOIN
    MSP_EpmTask_UserView ON
    MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

    INNER JOIN
    MSP_EpmAssignment ON
    MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

    INNER JOIN
    MSP_EpmAssignmentByDay_UserView ON
    MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID

    INNER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

    WHERE
    (MSP_EpmResource_UserView.ResourceName IS NOT NULL) AND
    (MSP_EpmResource_UserView.ResourceIsActive <>0) AND
    (MSP_EpmAssignmentByDay_UserView.TimeByDay >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) ) AND
    MSP_EpmResource_UserView.RBS like '%staff%';

    (I love this query because it always starts on the Monday of the current week)

    Then in your pivot table, right click on one of the days in TimeByDay and click Group.  Select days, and tick the counter up to 7.  Make sure that the starting date is a Monday (or Sunday) and you should be golden.  We use this all the time and we even have conditional formatting set to show over allocated resources.  We use Excel Services and have page filters by department and user etc and this self-updating spreadsheet is a wonder.

    Now if only I could get it to sum the work by week and only return resources with more than 40 in any week...(that's what I was looking for when I happened across this thread)

    Hope that helps. 

    Elli

    Blog:  http://projectserverpants.wordpress.com/

    Tuesday, August 14, 2012 11:51 PM
  • That is great Elli, however the querie may take some time to run. The cube has the info pre-agregated and therefore is quicker to retrieve, but hey if it does the job great :)

    Marc Soester [MVP] http://marcsoester.blogspot.com

    Thursday, August 16, 2012 11:21 PM
    Moderator
  • Marc,

    It doesn't take long to run at all (less than a minute) - at least not in our environment.  You can always add more to the WHERE clause if you have a lot of data.

    I don't like using the cube because of the way that Excel deals with Analysis Services - the same problem is true of PowerPivot - it makes the data more difficult to work with, especially when the pre-agregated functions add extra layers (RBS, Tasks, Time) into the data. 

    I am finding that not a lot of people seem to be using Excel for reporting out of Project Server, so there is a dearth of information and tricks out there for how to make it work smoothly.  I'm trying to share my knowledge when I can, because I sure wish it had been out there already when I started this!

    Thanks for your feedback. 

    Thursday, August 16, 2012 11:29 PM
  • Hi Elli,

    I can see that this seem a good alternative for users that are not familiar with the cubes. In our experience we have many customers that use Excel and the Cube for anaytical purpose.

    I personally dont like the SQL queries in Excel due to the potential process time until you retrieve data. As the data increases so does the time to calculate. The quick data retrieval is the big plus for cubes and of course that lack of SQL skills that business users have often prevents them from utilsing the SQL queries. That is where i see the strengt of the cubes.

    However I also recognise that thinking multidimensional somethimes can confuse users :)

    I have just ran your querie in our environment and it was quick enough for me too :)

    Do you have a blog where you share your experience? may be worth including in your signature.

    thanks for sharing your knowledge.

    Marc


    Marc Soester [MVP] http://marcsoester.blogspot.com

    Thursday, August 16, 2012 11:37 PM
    Moderator
  • Marc,

    First of all, my preference isn't due to confusion or lack of understanding; these are real constraints I'm talking about.  The very poster of this original thread was himself dealing with the "week#" problem that exists in the cube.  I can understand that many customers are unaccustomed to writing SQL scripts and may have better luck using cubes, but I personally think that SQL queries to pivot tables work better about 90% of the time.  If you use any kind of sets in the cube type reports, every time you do anything it has to rerun the OLAP query, whereas with a regular pivot table, a calculated item or field will calculate without any pause.

    I could go on for quite a long time about the advantages and disadvantages of these two methods, and if you would like to go into more detail on the subject, please let me know.

    Also, I don't have a blog, but I really am starting to think about starting one.  I'd love to be able to provide some support and insight for anyone else who might be in the same boat as me.  Thanks for the recommendation.

    Friday, August 17, 2012 12:05 AM
  • Ok, on your recommendation I have invented my own blog, which is only starting out, but hopefully it will help someone!

    http://projectserverpants.wordpress.com/

    I'll start putting that link in my sig, what the heck. :)  Thanks for the suggestion.


    • Edited by ElliJ Friday, September 14, 2012 5:01 PM
    Monday, September 10, 2012 7:50 PM