none
Current Date Task RRS feed

  • Question

  • Hi,

    I want to know the possibility in a Report/SQL Query/Views that:

    In Project Server 2013, I want [TaskName], that is/are currently happening (in today's date) in Project Plan (of all Projects), whatever the [TaskStartDate] & [TaskFinishDate] would be.

    Regards,

    Noman Sohail



    • Edited by NomanSohail Wednesday, April 1, 2015 6:40 AM Update
    Wednesday, April 1, 2015 6:38 AM

All replies

  • Hi Noman,

    I guess the easiest way would be to use the out-of-the-box Excel reports with projects and tasks and make a filter in Excel. In the pivot table, clicking on the start and finish dates, you can use the dynamic filters (unfortunately in french in the image below). Otherwise it would be a simple SQL query.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, April 1, 2015 7:13 AM
    Moderator
  • Thanks Guilaume,

    First thing, I translated from French to English the Yellow highlighted in image above.

    It shows "Time" "Today"

    In my Excel: [TaskStartDate] column has "Date" filter, and when I select "Today" from it - It shows records whose [TaskStartDate] is Today.

    My question is: Whatever the [TaskStartDate] and [TaskFinishDate] are, Excel/Report/SQL should display which [TaskName] is "happening" (not started) today between these dates range.

    Regards,


    Noman Sohail

    Wednesday, April 1, 2015 7:57 AM
  • Just above "today" in my image, you'll have "before...", "after..." and "between" which allow you setting a date range for the task.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, April 1, 2015 8:19 AM
    Moderator
  • Yes it has other filters as well but when the criteria in is set and while selecting range, it shows dates available in the column, I cannot specific the range, I couldn't find the way to enter current date clause (of system) dynamically.

    It reflects like: When I open excel sheet, it shows "April 1" records nd when I open it tomorrow, it shows "April 2" records automatically.

    Regards,


    Noman Sohail

    Wednesday, April 1, 2015 8:42 AM
  • Either you have to adapt your need to the Excel time filters available, like filter on the tasks starting this week and tasks finishing this month. Or you can create an SSRS report which gives you much more possibilities since you'll be coding the whole report based on your specific need.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, April 1, 2015 8:53 AM
    Moderator
  • Norman,

    What you need is a multiple condition statement based on:

    • Is the Task Start Date <= Today
    • Is the Task Finish Date >= Today
    • Is TaskIsActive = True
    • Is %Complete < 1 (Need to verify this as I'm doing this from memory. The Health field may also be used instead if it is not equal to "Completed")
    • TaskIsSummary = False assuming you don't want to see summary tasks
    • TaskIsMilestone = False if you wish to omit milestones
    • TaskIsManuallyScheduled = False if you wish to omit manually scheduled tasks.

    Once you've got this list, you should see all Tasks that are in progress today across all projects. Note, this will also pick up tasks with no resources. You will have to join to the Assignment table to figure out which tasks have assigned work. I love these Business Intelligence questions!

    Hope this helps.

    Treb Gatte, Project MVP | Blog | Twitter | YouTube Channel

    Wednesday, April 1, 2015 3:54 PM
    Moderator
  • Ok Great - Thanks Treb,

    I have used these conditions in SQL Query under WHERE Clause:

    	and [TaskStartDate] <= GETDATE ()
    	and [TaskFinishDate] >= GETDATE ()
    	and [TaskIsActive] = 1

    But the records showing is of "[TaskStartDate] <= GETDATE ()" only, because it filters all data through this condition, rest of the conditions (step by step) are applied on the records I get from first clause.

    In your opinion, how & where to use these multiple condition statement stated in your reply above?

    I use SQL queries for records from back-end and MS Excel for reporting.

    Regards,


    Noman Sohail

    Thursday, April 2, 2015 7:58 AM