Home › Forums › Discussion › Project Talk! Cracking the Microsoft Project Data Model: Project Reporting with Access › Reply To: Project Talk! Cracking the Microsoft Project Data Model: Project Reporting with Access
We had a great response to our last show topic on using Microsoft Access to analyze project data! So here’s a step-by-step of the Resource Usage Report we created on the show!
1. From Microsoft Project, open your project file and use the Project Ribbon and click on Visual Reports. The Visual Reports Dialog box will be displayed.
2. Click on the Save Data… button on the bottom of the Visual Reports dialog box. The Save Reporting Data dialog box will be displayed.
3. Click on the Save Database… button. The Save As dialog box will be displayed. Name and save the .mdb (Microsoft Access) file. The Save Reporting Data dialog will display ‘Save completed successfully.’ under the Save Database… button.
4. Locate the .mdb file and open the file with Microsoft Access. Microsoft Access will be displayed.
5. From Microsoft Access, use the Create ribbon and click on the Query Design button. A new Query window will be displayed with the Show Table dialog box displayed.
6. Select the Queries tab on the Show Table dialog box and use your CTRL key to select the MSP_EpmAssignment_OlapView, MSP_EpmResource_OlapView, and MSP_EpmTask_OlapView queries and click the Add button.
7. Click on the MSP_EpmTask_OlapView and click the Add button to insert another instance of the MSP_EpmTask_OlapView query.
8. Click the Close button on the Show Table dialog box. You will now have four ‘tables’ in your query design window.
9. Arrange the ‘tables’ in your query window and connect the queries together by dragging from the field in one table to the other table as shown below.
10. Use the grid on the bottom to create the output fields for your query. You can use the drop-down lists in the grid cells to select the Table, Field, and Sort for each column in the grid as shown above. Also note that we have created an ‘alias’ for fields 2, 3, 4, 5, and 6 by typing the alias name followed by :FieldName. For example, typing Task Name: Name in the fourth column will ‘rename’ the Name field from the MSP_EpmTask_OlapView table to ‘Task Name.’
11. Click on the Run button on the Design ribbon to see the results of your query.
12. Click the Save button to Save your Query. Best practice is to use the Qry_ prefix when saving queries.
13. Now we can use the new query to create a report that can be modified and formatted as desired. From the Create ribbon use the Report Wizard button. The Report Wizard dialog box will be displayed.
14. Use the Tables/Queries drop-down list to select new the query we created, then use the >> button to move all the available fields to the selected fields list box. Then click Next.
15. Click through the Report Wizard using the following selections:
o Group by Resource Name
o Sort by TaskIntID
o Set the Layout to Landscape orientation.
o Title the report Resource Usage
16. Click Finish. Your new Report will be displayed!
17. To change to formatting, resize and move, create bold, italic and other text formatting for the fields and labels, use the View button from the Design tab and choose Design View.
Matthew T. Davis, PMP, MCTS, MCITP is Vice-President for Consulting Services at PM Providers. Mr. Davis has been an active member of the Project Management Community for more than 20 years. In addition to his background in project management and consulting, Mr. Davis has managed development and delivery of PMO’s, PPM deployments, project delivery methodologies, strategic change programs, training seminars, organizational maturity assessments, and other project and portfolio management programs.
Project Talk is brought to you by PM Providers – Driving Results with Project Management Solutions. Find out how PM Providers can drive results for you at http://www.pm-providers.com.