Author: Raphael Santos

Raphael Santos is a PPM enthusiast with expertise in providing Microsoft Project, Project Online, and Project Server solutions. He has worked in several projects to implement PPM tools, including projects located in Latin America (Brazil, Peru, and Argentina), in the United States, and in Africa. He is also a trainer with more than 10 years of experience teaching users how to use Project Management tools in a more productive way. In 2016, Raphael was awarded the MVP title by Microsoft in recognition of his contributions to the Project Management community. Raphael is a PPM Consultant at Sensei Project Solutions, a certified Microsoft partner specializing in project and portfolio management deployments. Sensei offers a complete set of services to help organizations succeed with their Microsoft PPM deployments. Services include full implementation and training as well as pre-configured solutions and report packs. Visit senseiprojectsolutions.com or contact info@senseiprojectsolutions.com for more information.

Webinar: Create a One-Page Status Report with Project, Access and Visio

Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical Project Management talent triangle category. If you are claiming this session, you must submit it to your MPUG Webinar History after it has been completed in its entirety. Event Description: With Project 2013, Microsoft introduced the new Reports feature, which gives Project Managers an entire set of reports and dashboards. However, sometimes it can be hard for you to create a simple, intuitive one-page status reports. Through the combination of Microsoft Project, Access and Visio, this event offers a new way for Project Managers in creating beautiful, one-page status reports for their projects. Main points for the presentation: Knowing how to export data from Project to Access and how to keep it updated Knowing how to import data from Access to Visio Knowing how to create links between the imported data and shapes in Visio Knowing how to convert the imported data into icons in Visio About the Presenter: Raphael Santos is a PPM enthusiast, with expertise in providing Microsoft Project and Project Server solutions. He has worked in several projects to implement PPM tools, including countries in Latin America (Brazil, Peru and Argentina) and Africa. He is also a trainer, with more than 10 years of experience in teaching users on how to use Project Management tools in a more productive way. Raphael works at Sensei Project Solutions, a company that offers a set of services to help an organization make their Microsoft PPM deployment successful, including full implementation and services, training as well as pre-configured solutions and Report Packs. You may contact him at raphael.santos@senseiprojectsolutions.com Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]

Create a Killer One-Page Status Report with Project & Visio

Note: the images in this article are based on a Portuguese installation of the software used. However, they will be translated to the English language in the text. One question I always get when I visit my customers is: “Is there a way to create a link between Project and Visio?”  In most cases, you have to use code or additional add-ins. However, I wanted to explore a different way. I started trying to think of a way to create a one-page status report, containing only summary-level tasks, which should show the progress of each phase of the project as well as if the schedule has any variance. And I wanted to create it in Visio. As I explained in this article, I can export data from Project to Access while maintaining the data linked. Based on this, I figured out how to make that report, which I will explain below. Before starting There are a couple files that will be used in this example: The WBS containing the summary-level tasks of the project in the .vsd format, The schedule in a .mpp file. Working with the data In order to have a database with all the information about your project,  first thing you have to do is to export the data from Project to Access,  After completing the export of the data, you will have to open the WBS that was created in Visio. With the Visio file open, click Data > Link Data to Shapes. On the Data Selector wizard, select Microsoft Access Database and click next. Then, you will have to indicate where your Access database is located. After that, you have to specify which table of the database you want to use. Select MSP_EpmTask: Click next. The next step allows you to make filters on the data that will be imported, granting you the ability to work only with the data you want. Click on the Select Columns button, and then select all the columns that will be used on the report: TaskIntUID Name WBS Percent Completed Finish Variance Click OK. Now click on the Select Rows button, in order to apply a filter that will bring only the summary tasks of the project. To do this, on the Filter rows dialog box, click on the first dropdown and select Custom: Define the following criteria: Field: Is Summary Comparison: Equals to Compare to: True Click OK and follow the instructions of the wizard until the import process is completed. Now you will be able to see the External Data Window, which shows all the tasks that were imported from the Access database: Building the Report Once you have Visio reading the data from Access, you can start building your report. Visio offers a large variety of ways for creating stunning visual Dashboards, allowing you to establish a connection between the imported data and the shapes of your diagram, as Chris Hopkins shows in this amazing video. If the names inserted in the shapes of your diagram are the same as the imported data, you can easily click on the Automatically Link button and let Visio do the job. If they aren´t, you can drag-and-drop them from the External Data Window into each shape. In this case, click Automatically Link and create a link between the Name column of the imported data and the Title field in the shape: Follow the steps until the links are created. You will see your WBS something like this: Now, all you have to do is to adjust the way you want to present the information. First, select all shapes and then click on Data Graphics > Create New Data Graphics. Click on the New Item button; you will have to choose the fields you want to show on the shapes – and how they have to be displayed. Click on More fields and select the Shape Data category: Select the Percent Completed field and click OK. Then, on the Show as option, select the Data Bar style. Change the Maximum Value option to 1 (number one): Click OK. Click on the New Item button again, and select the Finish Variance field. On the Show as option, select the Graphical Icons style. You have to define the rules that must be applied for the icons you want to use. In this case, I will create a simple rule: If Finish Variance is Greater than 0 > show the Red flag If Finish Variance is Equals to or Lower than 0 > show the Green flag Click OK and then OK again to see the result:   The only problem here is that by default, Visio always shows the values aligned to the right of the shape. To change that, select the shapes Initiation, Installation and Training. Then, click on Data Graphics > Edit Data Graphics. On the dialog box, change the horizontal position to the left in both fields. Don´t forget to select the Only to the selected shapes option: Now you have your killer one-page status report:   Once you have your Project updated, all you have to do is to export from Project to Access and replace the old database. Then, on Visio, click on Data > Refresh All. Hope you liked this!  

Create Beautiful Reports WITHOUT Project 2013

Note: the prints of this article are based on a Portuguese installation of the software used. However, they will be translated to the English language. The Problem With the launch of Microsoft Project 2013, many new features were introduced to help project managers maximize their experience in managing their projects. The feature that I liked most was the new Reports session, which gives you an entire set of reports and dashboards where you can see, understand, and share information about different aspects of your projects. The problem here is that as many corporations, as well as individuals, are still using old versions of Microsoft Project. Consequently, they do not have access to this feature – and they get a bit frustrated. The Solution The good news is that with the use of two complementary tools – Microsoft Access and Microsoft Excel – it is possible to create stunning visual reports and dashboards without Project 2013. Additionally, you can create the reports while maintaining the connection with the original data, which means that as soon as you update the schedule you can also refresh the report. At the end of the day, you can have a dashboard that looks like this:   First step – exporting the data to access Few people know, but ever since the 2007 version of Microsoft Project, it is possible to export data to a Microsoft Access database. As soon as the information is exported, you can use Microsoft Excel to connect to the database and then build the dashboard based upon your needs. I´ve created a simple schedule in Microsoft Project 2007, which I will use as the example for this article: To export the information to Microsoft Access, follow these steps: If you are using Project 2007: click on Report > Visual Reports If you are using Project 2010: click on Project > Visual Reports If you are using Project 2013: click on Reports > Visual Reports After choosing the option that applies to your version of Microsoft Project, you will see the Visual Reports dialog box. At the bottom of the dialog, click on Save Data: After that, click on Save Database: Select the place where you want to save the file, as well as its name. Make sure that you are saving the file with the extension Microsoft Access Database – (.mdb) Creating the dashboard Once you have exported the data, you have to switch to Microsoft Excel to import the data from the saved database. After following the required steps, the wizard will show the list of available tables. The schedule information is stored at the MSP_EpmTask table. Select the table and then click OK: To finalize the process, you will have to choose the option to show the data as a Pivot Table: Now you can see that all the information related with the schedule of your project is available on the fields of the Pivot Table: Now it is up to you and your creativity! You can make as many combinations and as many Pivot Tables as you need in order to create the dashboards you want. Here is the first example of the article: Next time, when there are updates to the schedule, the only thing you will have to do is to export the data from Microsoft Project to Microsoft Access again, in order to replace the old database. Then, when you open the spreadsheet, it will read the new data and your dashboard will be updated automatically. I hope this article is useful!

Grouping Projects by Month in Project Server 2010

Grouping projects by their month of finish Microsoft Project Server offers users many different views to resume projects in the Project Center area. Each one of these views is dedicated to organize the information accordingly with its subject,  allowing users to understand their portfolio from different angles. In addition, Microsoft Project Server allows users to create their own customized views, in order to organize the information they need. When talking about creating customized views, a good idea would be to create a view that groups projects by their month of finish. This view may help Project Portfolio Managers to understand the projects that need to be finished in the coming months, as well as the effort that will have to be made. Trying to use the native functionality Before creating the view, it is necessary to know that there is no way to create it without using a formula. If you try to use the customized option ‘Group by’ that Microsoft Project Server offers, using the Finish field as the first group level, you will see that the projects will be grouped by the day they finish – instead of the month:   Creating the formula and adding it to the view As soon as we know that the native functionality does not meet our needs, we have to create a formula that extracts only the month and the year of the projects – based on their Finish. To start creating the formula, go to Server Settings > Enterprise Custom Fields and Lookup Tables and click the New Field button. In this example, I will give the name Month of Go Live to the field. It will be a Project Entity and its type will be Text. In the Custom Attributes session, change the option to Formula. At first, I would create a simple formula to extract the year and then combine it with the month of the finish date. The formula would be: Year([Finish])&”/”&Month([Finish]) – where: Year([Finish]) – extracts only the year of the Finish date; &”/”& – adds the / character between the year and the month; Month([Finish] – extracts only the month of the Finish date. After creating the formula, all I have to do is create a new view (or edit an existing one), insert the Month of Go Live field, and group the view using that field. After publishing some projects, the result would be like this: Note that we do have a problem: projects that finish from February to September are organized after those who finish from October to December. This happens because Microsoft Project Server uses the first number as a key to classify the information and, using this criteria, 1 (the first number for October, November and December) will always come before 2, 3, 4 and so on. To fix this issue, we have to edit the formula and use conditional criteria: when the size of the Month (in number of characters) is equal to 1, the formula needs to add the 0 (zero) before the character. If not, it can just use the previous expression. The combination will be like this: IIf(Len(Month([Finish])) = 1; Year([Finish]) & “/” & 0 & Month([Finish]); Year([Finish]) & “/” & Month([Finish])) – where: IIf(Len(Month([Finish]))=1 – if the size of the Month (based on Finish date) is equal to one, than; Year([Finish])&”/”&0&Month([Finish]) – extracts the year of the Finish date, concatenate with  “/”, concatenate with 0 and concatenate with the Month of Finish date. Else; Year([Finish]) & “/” & Month([Finish])) – extracts the year of the Finish date, concatenate with “/” and concatenate with the Month of Finish date. After publishing the projects, the result would be like this: