none
Can you use SQL as a data source for a project in the same way you can in Excel? RRS feed

  • Question

  • Excel allows you to create a data source that executes a SQL stored procedure, display that data as a table in a spreadsheet and have that data automatically refresh each time you open the spreadsheet. Is it possible to do the same thing in MS Project, displaying the data from the stored procedure as a series of tasks?

    Here's what I'm trying to do - I have a stored procedure that pulls task data meeting a specific criteria from all projects in Project Server. We're currently displaying this data as an Excel report. However, the data includes start dates and durations so it would be nice to be able to display it as a Gantt Chart. I've played around with creating a Gantt chart in Excel and have been able to do a very basic one, but it doesn’t quite fit our needs.

    Wednesday, December 18, 2013 9:48 PM

Answers

  • So you say you "have a stored procedure that pulls task data meeting a specific criteria from all projects in Project Server".  So with that stored procedure you should be able to build a SQL Server Reporting Services report that creates a basic Gantt chart.   

    I've created a few basic gantt chart reports using these steps: http://blog.summitcloud.com/2009/11/ssrs-2008-gantt-charts/.   You will need to recreate the SQL to match the stored procedure but the steps should be the same after that.   

    • Marked as answer by Mike1388 Thursday, December 19, 2013 2:25 PM
    Wednesday, December 18, 2013 10:47 PM
  • No, You can not use sql as a data source for a project.

    You have 3 options to achieve it:

    1. You can create a Sharepoint list with desired column ,fill desired data in that list then you can create a MS project from Sharepoint List.

    2. You can create a SSRS report in which you can display grantt chart Joe has given you that link.

    3. You can write a macro in MPP which will take data from your excel. In excel you will fetch data from your stored procedure. write a schedule which will run every day to update your data or

    create an excel report in which will update automatically and write macro in mpp which will fetch the data then publish it so that it would be available to team members.


    kirtesh

    • Marked as answer by Mike1388 Thursday, December 19, 2013 2:25 PM
    Thursday, December 19, 2013 10:35 AM

All replies

  • So you say you "have a stored procedure that pulls task data meeting a specific criteria from all projects in Project Server".  So with that stored procedure you should be able to build a SQL Server Reporting Services report that creates a basic Gantt chart.   

    I've created a few basic gantt chart reports using these steps: http://blog.summitcloud.com/2009/11/ssrs-2008-gantt-charts/.   You will need to recreate the SQL to match the stored procedure but the steps should be the same after that.   

    • Marked as answer by Mike1388 Thursday, December 19, 2013 2:25 PM
    Wednesday, December 18, 2013 10:47 PM
  • No, You can not use sql as a data source for a project.

    You have 3 options to achieve it:

    1. You can create a Sharepoint list with desired column ,fill desired data in that list then you can create a MS project from Sharepoint List.

    2. You can create a SSRS report in which you can display grantt chart Joe has given you that link.

    3. You can write a macro in MPP which will take data from your excel. In excel you will fetch data from your stored procedure. write a schedule which will run every day to update your data or

    create an excel report in which will update automatically and write macro in mpp which will fetch the data then publish it so that it would be available to team members.


    kirtesh

    • Marked as answer by Mike1388 Thursday, December 19, 2013 2:25 PM
    Thursday, December 19, 2013 10:35 AM