Sometimes there will be an existing summary "plan on a page" (POAP) in Visio which you want to replace with a SummaryPro POAP due to the increased ease and accuracy of the updating. With a simple POAP it is easy enough to recreate manually however with a complex one or if you have many to do it is nice to be able to automate the process.
This blog will walk you through the steps of extracting the positional data from Visio and then calculating the dates that each milestone or task are showing so that you can use this in SummaryPro.
For this example we will use a simple POAP:
To extract the positional data for each shape on the page click on the "Shape Reports" in the Reports section of the Review ribbon.
If this is the first time that you have done this you will need to set up a new report. To do this click on"New" in the dialogue that comes up
Then choose "shapes on the current page"
You then need to choose which attributes to include in the report:
- Displayed text
- Shape master
- Shape ID
- Shape name
- Width
- X location
- Y location
Give the report a title and choose Next. On the next screen give the report a name (which can be the same as the title if you want) and if needed a description of what it does.
Then you will be returned to the Reports dialogue where you choose to run your new report.
You will be asked how you want to output the report - choose Excel.
Now an excel report will open displaying the positional data for all shapes on the page. If you have a very complex report you can spend some time removing the items which you are not interested in, however, this isn't necessary.
Now we need to calculate what the position on the page means in terms of dates. To do this we need to enter a series of calculations based on the earliest date and the last date in the report. Eventually, you will end up with the sheet below,; the following steps will talk you through the process of creating this sheet. You can save this sheet to reuse with future reports.
First, we need to work out how many mm each day is on the source POAP. To do this you will enter the following calculations and then name some of the cells to make them easier to reference in the main calculations.
In each of the cells below copy the calculations:
T2 - enter the first date in the source POAP, in this case, 1st Jan 2022
T3 - enter the last date in the source POAP, in this case, 31st May 2022
V2 =MIN(M:M) - i.e. the left-most shape
V3 =MAX(N:N) - I..e the right-most shape
T5 =IF(T4="yes",T3-T2,NETWORKDAYS(T2,T3)) - works out the number of days covered by the date range
U6 =U3-U2 - how many mm are there between the left and right most points
U7 =T5/U6 = how many mm is each day
To make it easier to reference these numbers in the main calculation set you should name some of the ranges as below. To do this select the cell and then type the new name in the address box at the top left.
Now you can use the mm/day calculation to work out the dates for each task or milestone. Note that you need to tell the calculation if an item is a milestone as there is no way for Visio or Excel to know. You do this in column I.
Enter the following calculations into J3 to P3 and then copy down to cover the data:
J3 =VALUE(TRIM(LEFT(E3,FIND(" ",E3)-1)))
K3 =VALUE(TRIM(LEFT(F3,FIND(" ",F3)-1)))
L3 =VALUE(TRIM(LEFT(G3,FIND(" ",G3)-1)))
M3 =K3-(J3/2)
N3 =K3+(J3/2)
O3 =IF(I3="yes",start+((K3-Start_X)*day_inc)+IF(K3=Start_X,0,1),start+((M3-Start_X)*day_inc)+IF(M3=Start_X,0,1))
P3 =IF(I3="yes",O3,start+((N3-Start_X)*day_inc))
Now you have the information needed to set up a SummaryPro Plan on a Page thatwill be a lot easier and more accurate to maintain going forwards.