Using Visio 2003
There is a complex batch process (around 600 programs more or less sequential in nature) which runs each day. Would like to be able to automatically draw the entire process each day using the runtimes from each of the 600 process. That way can at a glance see where things ran long by either the length of the programs box in Visio or the line connecting one box to another as that would signify how long a program ran.
The programs have been extracted into an excel spreadsheet with their dependencies (order of run) and so can create a site map using the process outlined in the site below, using database wizard.
http://www.boxesandarrows.com/view/the_lazy_ia_s_guide_to_making_sitemaps
The run time for each program can be extracted from stats and added to the layout spreadsheet as an extra column matching each program with its runtime.
Is there a way to automatically draw the batch process in Vision 2003 and have each block start with a certain verticle height say 1cm and then for every 5 mins of runtime the height increases by 1mm? (or have the line between each process increase by 1mm).
The final objective being the ability to look at what ran in the batch process and visually pick out if any particluar program ran long? This could be repeated every day for each particular run.
first thought on reading it, is that the 1cm + 1mm per 5 mins is simple shapesheet formulas from what i can see.
you would need auto refresh from database (in this case the excel file) to be able to see the progress, and unfortunatly that can't be done in visio 2003
you need visio 2007 or newer for that
So it is doable which is good.
The shapesheet idea sounds ideal so looks like an upgrade to 2007. Can work out the details later but would the refresh cause the drawing to be redrawn from scratch or will this just be resizing the shapes in an existing drawing.
What I mean it I could create a nice looking batch map then each day the shapes could change their size according to the information on a spreadsheet.
Just saying this as when doing the autodraw in 2003 found that sometimes the drawing was a bit squished up in some bits and needed a bit of manual tweeking. If no need to redraw from scratch each time then a basic drawing would be fine and this could all be automatic.
(2007 may not actually have this issue, just wondering)
Ok have upgraded to 2007. Is there an article or guide anywhere that would help in what I would visio to do.
My idea is
- draw the bath diagram in visio
- extract run times of jobs and put them into excel. Excel would have start and end times of each job and elapsed time
- use link of visio to excel and have the visio shapes resize themselves depending on start and end time
- output would be a visio diagram of batch which at a glance any long running processes can be seen
ie The initial diagram would be vis_example1.jpg which shows the logic. There are 10 processes with a main backbone 1,2,3,4 and 5 and 2 parallel offshoots which run processes 6,7 and 8 and 9 and 10.
What is desired is example2. this shows the start time and end time of the whole batch and the shapes that represent the processes are streched to indicate their start and end times. From example 2 it can be seen very easily that process 10 runs for a long time.
Is this something that viso can do with smart shapes? The real batch process diagram has nearly 800 individual processes but can be simpified down into groups making around 150 groups.
You could to it all with VBA, where you need to open the ExcelSheet, go through its values, calculate where to place your shapes from those values,...
Or you need a slightly modified shape for this, and Visio 2007 Pro's Linking to an external DataSource feature:
1. The Shape needs 3 ShapeData Properties: Start-Time, End-Time or Duration, Colum or Thread in which the Process belongs
For example: Prop.Start, Prop.Duration, Prop.Colum
You may eventually need additional ShapeData Rows for info which shape has to be connected to which shape
Those ShapeData could than be linked to an Excel Sheed, which has the information.
2. You have to define a timeline startpoint on the Page, for example at y=200mm which coraletes to your StartingTime of 18 h.
You could store those values in ShapeData or User defined cells oh ThePage, fo example: Prop.StartTime, Prop.StartHeight
And the info, that for example 10mm equals 1 hour in the diagramm.
And last the info how far colums are spaced in Prop.Space and at which X coord the first colum starts in Prop.Xcoord
3. The modification of the shape, could be like (in the ShapeSheet):
a) LocPinY = Height
b) PinY = ThePage!Prop.StartHeight-(ThePage!Prop.StartTime-Prop.Start)*10 mm
c) Height = Prop.Duration*10mm or (Prop.Start-Prop.End)*10mm
d) PinX = ThePage!Prop.Xcoord+(prop.Colum-1)*ThePage!Prop.Space
That should place your shapes automatically, depending on the Shape Data.
But your connectors will have to be set with VBA i fear.
Tried using the organisational chart (in visio 2003) with import of data from excel which had all the logic and the process run order however that took a few hours at the end to make the diagram look ordered as the links sometimes went to the side of shapes aswell as the top.
Mentioning this as it sounds like the Excel way in 2007 could redraw each time and could also take a while to order.
The VBA method does sound better. Guessing that the logic and links would be set up in VBA, so essentially the drawing could be created in a way that looks good.
Once the logic drawing is set up then can link this to the database source to get the placings (wrt time) and sizes (for runtimes).
Is that interpretation correct? (The fiddly bit bieng the VBA for connectors but would only have to do this once.)
OK if the processes are always the same and in the same order and the same "colum" the its truly easy and you won't need VBA at all.
You set up the page and a shape like a mentioned in my last post. Position that shape by manually changing the ShapeData in the Shape.
Copy that shape, position the new shape,... This is some work, but you only have to do it one time.
When you have all shapes positioned and placed, you can connect them manually with connecots. That too, is work, that only will have to be done once.
Than you can link the drawing to your ecxel source (that has nothing to do with the orgchart wizard) and get the Data displayed in the so called External Data Window.
Now you can link every DataRow with the assosiated shape and you are done.
Whenever you update your Excel Sheet and then update the external date link, your Shapes will position themselfs new. All without VBA.
-------
You could use VBA to do the work of placing the Shapes at the right Position the first time you create the diagramm.
In the drawing is an example of such a shape and page.
It separetes hours and minutes because I don't like to calculate with date and time formats, so the shape should certainly be improved.
Maybe seconds should be added, too, don't know how exactly you are counting the time.
The timeline could be made smart, too.
Problems could accure when passing into a new day (batch jobs are often run through the night I think?).
And many more ways to improve, but it's a start
Thnks for all that help, will have a start and let you know, may take a week or two.
The batch suite is (should) be static with minimal changes unless new processes added or omitted and there are variations due to end of week/month etc but changes should be minimal.
The time wil be overnight but can work on this. As for elapsed time this is extracted from stats and can be calculated in a numbe rof different ways but would probably want to go for a more user/manager friendly speak like hours and minutes. Again this all sounds relatively straightforward.
Getting somewhere now however now have to upgrade to Visio 2007 PROFESSIONAL in order to link shapes to Excel data as that option not available in 'normal' 2007.
In meantime got a shape responding to resizing but finding a problem with shapes drawing over other shapes. (this may not happen in 2007 pro, will take a day or 2 to upgrade)
What is happening is say there are 3 processes run sequentially, redraw1.jpg. The shapes redrawn with the correct times but looks like the second (or third) shape does not move (pinned maybe). If the first shape resizes its height too much it just draws over the next shape, redraw2.jog. What would be ideal is for the second shape to be pushed down the page. (sounds like a minor thing or a step I've missed)
Should mention that for now the height is just based on the elapsed time field, teh begin/end times not used. Similar to article in link http://www.visguy.com/2009/02/23/trilogy-meter-data-graphics-part-i/
To push down another shape it has to know what's with the previous shape. You could write in the second shapes PinY cell:
Shape1.PinY - Shape1.Height/2 - X mm
Shape1 must be the name of the first shape and the LocPinY of all Shapes have to be at Height*0.5 to work.
X mm is the distance you want to have between the two shapes.
-----
Another method could be to place each shape at the right hight depending on the start time. See my shape from previous post.
Put =Shape1.PinY - Shape1.height/2 - 5 mm into the PinY cell of shape2. Made sure that LocPinY = height*0.5 but just got error in formula. (tired a few permutations but similar problem)
Shape 1 has its pin position set to top-centre.
Hmm did I miss something?
Is Shape1 truly the name of your upper shape?
With the following formula it sould not matter where the Pin shapes lies:
I assume again, that the upper shape is Sheet.1
(In your case, you must make shure to use the correct names of your shapes. You can see them in the top line of the ShapeSheet of a shape or in the "drawing explorer window" that you can find in Menu "View").
In PinY of the second Shape:
=Sheet1.PinY-Sheet1.LocPinY-5 mm-Height+LocPinY
Got the shapes moving around now. Had a quick tutorial (handy guide here http://www.visguy.com/2009/07/15/whats-my-shapes-id/) to learn about shape names and as you guessed my shape names were not as I thought so the reference was failing.
Slight change to the formula replacing '.' with '!' and using full name Sheet.1 rather than Sheet1
=Sheet1.PinY-Sheet1.LocPinY-5 mm-Height+LocPinY
becomes
=Sheet.1!PinY-Sheet.1!LocPinY-5 mm-Height+LocPinY
Now upgraded to 2007 Pro so on with the original plan and linking data.
Thanks for the help so far Jumpy and being so patient. I'll post progress on the auto drawing.
Sorry for the '!' error. If I only write in in the editor here, I often make this wrong. Good that Visio informs me, when I make the same mistake in the ShapeSheet ;-)
-------
Even if the formula now works. I would suggest to use the Start time to place each Shape (PinY) individually, like u use the duration to change the height.
Story so far
- I have a batch suite diagram in 2 formats. 1) Simple with 96 (hence 96 shapes required) subroutines only showing the subroutine names, 2) Detailed with the same 96 subroutines but this time each subroutine shows the processes that run in each making around 800 in total (800 shapes required)
- Obviously going to go for the simple version first
- Can use the Simple diagram but would need to redraw using modified modified shapes which can redraw themselves dependig on linked data.
Basic shapes now moving around and resizing so going to progress to next stage.
Is there an easy way to create the shapesheet references or will these all have to be done individually, all be it only need to be done once. Things like to allow for the redraw by referencing the start time would need to reference sheet1 or sheet3 etc. This would need to be done 96 times for the simple drawing. Just asking before getting stuck in.
You will have to do it individually for each shape, because you always have to reference another shape.
Therefore my suggestion to use the start time each shape has in it's shape data, to place that shape independent of other shapes.
If you want to stick with the "referencing the previous shape" solution and want to do it in good time, you will have to use VBA to place your shapes or change their formulas.
You could place sth. like this in a VBA modul of your solution and start it with the Callthis function in the EventDrop cell of your shape:
Public Sub Place_me(dropee As Visio.Shape)
Dim shp As Visio.Shape
Dim shpName, Formel as String
For Each shp In ActivePage.Shapes
If shp.DistanceFrom(dropee, 0) <= 0 And shp <> dropee And shp.OneD = False Then
shpName = shp.NameID
Formel = shpName & "!PinY-" & shpName & "!LocPinY-5 mm-Height+LocPinY"
dropee.Cells("PinY").formula = Formel
dropee.Cells("PinX").formula = shp.Cells("PinX").Resultstr("")
Exit Sub
End If
Next
End Sub
Didn't test the part, where the formula is set, but the rest should work. So if you save a shape with the Callthis function in the shapesheet in a stencil and drop a shape based on that new master on another shape, it will be placed belov the first shape accordingly to the formula.
Getting there but having issues with placement of the shapes. They are disappearing when placed, think its something to do with the scale.
For the shape the PinY is set to ThePage!Prop.StartHeight-(ThePage!Prop.StartTime-Prop.Row_2)*10 mm
Which means pin the shape at the point corresponding to the top of page minus the distance corresponding to the time that you started.
On the Page shapesheet 2 properties were created in Shape Data
Prop.StartTime - Type 5 - Format "{{HH:MM}}" - Value = 1800
Prop.StartHeight - Type 2 - Format "" - Value = 400 mm
In User-defined Cells there is
User.msvDGCalloutGap - Value DraxingScale/PageScale*0.0625 - No Formula
On the shapesheet for the shape for start time there is a Shape Data used to extract infor from an external table
Prop.Row_3 - Lable Start_time - Type 5 - Format "{{HH:mm}}" - Value DATETIME(0.7847)
When the formula was applied the shape was placed at -17580 mm. Not quite the right place as the start time was meant to correspond to 18:35...
Where would the scale of the page be found. Want to be able to match the top of the page (at 400mm) to be 18:00 and make say 10mm = 1 hour. (previous post from Jumpy 3rd post in this thread)
Hi and welcome back again.
In one of the post I placed an example .vsd with a shape that has roughly the behaviour you need.
When creating it I encountered the same problem you are having now and I didn't found a direct solution.
It has to do with the Date and Time format. When you look behind the really stored numbers behind a date and a time you now what I mean.
My way arround that was to store the start hour and the start minute as simple numbers in seperate ShapeData fields and so could base my formulas on numbers and not on "spacetime 23456.23 this is are the adventures of..." ;D
Yes the numbers can be fiddly especially if trying to go past midnight as the run does. Trying to work out some sense of the timing. In the meantime found this little explanation of time on the microsoft website. 18:00 would 0.75 going by this...
"Date and time values in Visio are stored internally as a 64-bit floating point number. The value to the left of the decimal represents the number of days since December 30, 1899. The value to the right of the decimal represents the fraction of a day since midnight. Noon is represented by .5."
See all very straightforward....erm....!?
Update
OK playing with the microsoft number format and it can be used to do all the dates and positioning/resizing once few things understood.
Input times for processes will need to have a date part yyyy/mm/dd hh:mm so the excel sheet will require a bit of formatting too. The Page will also need to have a startdate property associated with it to define the top of the page. I created a sheet 2 in the linked data source (excel sheet) to link a date variable to the page data property StartDate.
For example 6th June 2010 corresponds to 40335 (days since Dec 30 1899). Add the start time of 18:00 and you get the number 40335.075 as 18:00 corresponds to 0.75.
If a process runs for 10 mins starting at 18:00 on 6th June 2010 its end time will be 40335.7569 making 0.0069 = 10 mins.
If a process runs past midnight then the big number will increment from 40335 to 40336. The elapsed time can be easily calculated (well by Visio) going past midnight, month/year ends.
All sounds good but still fiddly.
Next bit is to match up the page scale of say 1 hour = 25 mm to the time format where things are worked out in parts of a day. This means that once the start and end time numbers are calculated they need to multiplied by something to make the scale work as DATETIMEs are given as part of a day.
In DATETIME 12:00 is 0.5 and 18:00 is 0.75 giving 6 hours = 0.25
In my page 1 hour = 25mm so 6 hours = 150mm
So 150 mm = 0.25 in DATETIME = 6 hours
Making 150/0.25 = 600 the factor the DATETIME numbers need to be multiplied by to put them onto the scale.
From above
6 hours = 0.25 which is 0.25 *600 mm = 150 mm
Another one
if 10 mins = 0.0069 then that equates to 4.14 mm which is about right as there are rounding errors. (25/6 is actually 4.166666666666666 mm)
PinY now equals GUARD(ThePage!Prop.StartHeight+(ThePage!Prop.StartTime-(Prop.Row_2-ThePage!Prop.StartDate))*600 mm)
Still a work in progress, any thoughts?
Quite a research you have done there and I appreciate the update, because I now know how to decipher the timeformat and maybe one day I need ecactly that.
At this moment I don't have any good ideas for you, but you seem to be on the right way. Keep us in the loop.
Although one think comes to mind. It could eventually be nessecary to keep the scale (that is the mm per hour) variable. Because in a normal night the whole process runs for example two hours and you space your scale accordingly to fit to the page. Now there comes a night, where the network is slow, and your process takes 5 hours. Staying with your normal scale it won't fit on a page anymore...
On the other hand a scale that is always the same makes it easy to compare runtimes of process parts on different days. You only have to compare their lenght.
Because both reasons are (imho) good ones, it may be good to apply both. Make a fixed scale and a variable one and give the page a prop.cell where you can choose which one shall apply.
Used the variable for the scale (page property Prop.Scale), makes things lot easier as only need to change a number in the page properties to change the scale. Same for the page height (Prop.Startheight=Pageheight) as if things run long then can easily change the page size to accomodate the extended run time.
Bit of pain creating the page as unable to store the shape as template as it refences a page. Fairly easy to duplicate the shapes and rename them but as no data and the PINY is guarded all the shapes can get bunched up fairly quickly. Can fix this by using ficticous data in the driving spreadsheet to more the shapes vertically on the page while setting things up.
This will be more tricky if I try to do this with all the subroutines as there will be approx 800 of them.Is there a way to run a macro to copy a shape and then past and rename it. This would make the creation easy as I have a list of all the required subroutines. Either that or its manual cut and paste (just being lazy)
Apart for those minor niggles the batch process is now autodrawning nicely on the page. Its really easy to see where the long runs are located.
On a more technical note is there a way to not draw a particular shape if it hasn't run. What I mean is, the batch is a generic process but this runs for 3 different systems. The smaller systems run only a portion of the processes and there is a list of proecsses that are omitted. Easy to add an omit column to the spreadsheet.
Is it possible to use this omit column so that if omit is set to Y then the shape is not drawn on the page?
Quote from: visio on June 14, 2010, 10:29:58 AM
Bit of pain creating the page as unable to store the shape as template as it refences a page. Fairly easy to duplicate the shapes and rename them but as no data and the PINY is guarded all the shapes can get bunched up fairly quickly. Can fix this by using ficticous data in the driving spreadsheet to more the shapes vertically on the page while setting things up.
If you copy a so created shape (that contains references to the page) in a stencil it will eventually show REF() errors in it's ShapeSheet, when you look at it in the stencil. But when you drop it on a page, that has the referenced Prop.cells and User.Cells, the references will still work. Most of the time, if you drop such a shape on a page that even doesn't have the referenced Prop. and user.cells they will be created in the ShapeSheet of the page automatically and filled with the values the shape knew last (not with eventual formulas of the original page, only values).
This will make it later easy to fetch such shapes from the stencil via macro and fill them with fictive data to place them the first time.
Quote
This will be more tricky if I try to do this with all the subroutines as there will be approx 800 of them.Is there a way to run a macro to copy a shape and then past and rename it. This would make the creation easy as I have a list of all the required subroutines. Either that or its manual cut and paste (just being lazy)
See above. I think it can be done programatically.
Quote
On a more technical note is there a way to not draw a particular shape if it hasn't run. What I mean is, the batch is a generic process but this runs for 3 different systems. The smaller systems run only a portion of the processes and there is a list of proecsses that are omitted. Easy to add an omit column to the spreadsheet.
Is it possible to use this omit column so that if omit is set to Y then the shape is not drawn on the page?
In the geometry section off the shape is a cell Geometry1.NoShow. If its value is set to true, it is invisible. Say you have a ShapeData cell "Prop.Draw" with Y or N in it, you could write =If(STRSAME(Prop.Draw,"Y"),False,True) in the NoShow cell.
Are there connectors between your shapes? They would eventually have to get invisible as well. and a longer replacement connector may be needed:
S1-->S2-->S3 could become
S1-------->S3
Ah ha, actually did save as stencil but only the values were showin which is why I thought the formulas were duff. When view changed in ShapeSheet to formula then everything clear, also shape remembered its last value too.
So far got auto drawing of groups of reports (a group being called a Session). In order to see more detail there are 2 options, either put everythng onto 1 visio sheet which would require approx 800 shapes on a sheet or create mini visio sheets using smartshapes again to represent each session and link these to each session shape on the main page. With the visio sheet saved as HTML it should be possible to double click on a SESSION and that would jump to a hidden mini visio sheet showing the run times of the processes in that individual session. Think that would be handy as you would look at the batch run at a high level, you would see what bit ran long then drill down to see if any individual process was runing long (or there was dead time) in that session.
As with the main sheet this would be linked to a data source like excel and the time formatting would be the same as for the main sheet.
Macro maybe to create the individual process shapes then group them together as required.
The noshow cell option looks like the way forward to make this all genric for any system, thanks Jumpy.
Not using connectors as they clutter diagram and the actual times things ran show pretty good logic anyway. Will create a not smart logic diagram with connectors to show dependencies or use the mini visio session diagrams.
All looking good though.
Ok drawing of Sessions getting there. If a process is set to be ommitted then can set Noshow cell to True to not draw the shape, also need to set Hidetext in miscellaneous to True to remove labels. Finally as connectors not being used in the view menu the show connnectors should be unchecked. Doing all this will make the shape effectively invisible.
Just to complicate things there are certain processes that run weekly or monthly. These are not ommitted and the automation on the machine takes care of when these run. When run times are extracted there will be no entry for these processes in the extracted spreadsheet (as they didn't run, ommitted entries do run even though for a very short time and will have spreadsheet entries). This means that when the Visio diagrams external data is updated there won't be links for certain shapes.
Is it possible to 'make a shape invisble' as above if there the link to the data source is not present?
What I'm trying to do is make a generic Session diagram with say 10 processess in it. The omitted ones can be picked up by the omit Y/N flag. The weekly/monthly ones could be picked up by their absence in the linked data. If, in the session, 2 processes are omitted and 3 are monthly then only 5 processes would need to be drawn. If this can be done then there would be no need to create separate diagrams to allow for weekly and monthly etc.
Make sense? Can this be done?
Hello.
I think that a shape doesn't know, that its link doesn't exist. It will simply retain its previous Data. Is just a guess. Will try it out tomorrow as a test, because it's an interesting question.
Edit: But Visio knows and gives a message, where you can choose to ignore this or to delete not linked shapes.
A way to make it work could be to give your shapes an additional ShapeData entry. Create a suitable entry in excel, too. For example "monthly". In excel it shows only "True" in the monthly/weekly processes (when they are there), and "Normal" (not False!!!) in the normal processes.
In the linked shapes you got your additional ShapeData cell that either shows "Normal" or "True". When it's normal, you can ignore it, because it's a normal process. Where it is "true", you create your shapes so, that they are only visible, when that value is true (and if they are not omitted (don't know if that can apply to the monthly/weekly shapes, too)).
And now comes the trick: Prior to updating the ShapeData you run a small macro, that iterates through your shapes and looks for those new ShapeData Row. If the value is "Normal" it does nothing, if it is "True" it sets the value to "False", thus hiding all such shapes. Should be 10 lines of code, max.
Then you update your link and the "False" will be set to "True" again, if the monthly/weekly shapes got updated, too.
Could work...
Edit: Tried it out, worked. But when you update and have no Data for montly/weekly shapes, the link to those shape breaks. You get an error message in the DataGrafic Window, but you can choose to ignore this. Later, when you update again when you have Data for those shapes, the link is still broken and the shapes are not updated. But you can choose to link shapes automatic to the ExternalData and you got all links back with one mouse click. But it must be possible to identify the right Data for each shape, that means your Data (=processes) must have sth. like a unique key, for example a process name or number (when its unique).
Had another idea about this. This would require a bit of manipulation of data in the driving excel (or other) database and exploiting time information.
- Create a Session diagram in Visio with the max number of shapes, this would cover omitted and weekly/monthly processes.
- In the excel sheet every process should have a row entry whether run or not. Spreadsheet would look like below, colums only
Process_Name Start_time End_time Elapsed_time Omit
- In the spreadsheet add an extra column to indicate the previous days run start time, easy to do as data extracted daily just need to copy the column sideways. Also add a column to indicate Run_Notrun. This column is the todays start time minus yesterdays start time. If his equals zero then the start time has not been updated for today and so that process did not run as dates between runs will always increase.
Process_Name Start_time_yesterday Start_time_today End_time Elapsed_time Omit Run_Notrun
- Can make the the Run_Notrun equal to N if the answer is zero
All that is needed then is an extra property added to the the shape to look for Run_Notrun being N. If this is set to N then make invisible and place the shape at a default location which would be equal to the start time of the Session.
That means that if the shape is drawn its not put far away on the page making the diagram look weird.
This does mean mucking about with data but it this done then it can be done in a macro in excel which is fairly easy. Also means a generic diagram and data as number of processes are faily static even if not run. As only concerned with the actual visual output from Visio the result should be fairly clear and only show the bits that are of concern.
For this to work would need to know how to set the default if Run_Norun set to N. ie if Run_Norun set to N then set PinY=same PinY as the first process in a Session (always present), also make invisible as mentioned earlier. Could use OR logic to cover similar for OMITed processes.
Make sense again? Too fiddly?
I would use 1 for run and 0 for not run because N would be a string and comparing strings makes formulas longer.
The same with omitted.
So you could write in PinY for example:
=If(Prop.no_run*Prop.Omitted=1,"Then place shape correct","Place shape in default poistion")
because Visio interprets 1 as True (hope it's not the other way round?) it is possible even shorter:
=If(Prop.no_run*Prop.Omitted,"Then place shape correct","Place shape in default poistion")
The same with N:
=If(OR(STRSAME(Prop.Omitted,"N"),STRSAME(Prop.no_run,"N")),"Place shape in default poistion","Then place shape correct")
Been a while, was off getting the data input into a useful format for Viso. Anywas sucessfully did that. Format is now as below (column headers) using '1' for Norun as Jumpy recommended.
Session Uproc Start_time End_time Elapsed_time Failures Run_notrun
The plan as mentioned before is to create a mini visio page which represents each of the session. Each session in the main session diagram would be hyperlinked to its corresponding session diagram. In the session diagram the detail of the session runtimes can be see. Basically a cut down version of the Session sheet.
Each Session always has a Begin process called <something>_B. It would be good to make the top of the page equal to the start time of this process. This means all the mini diagrams start drawing from the top of their respective pages. (Similar to Session diagram each diagram would be unique and not change much so each page would be hardcoded to reference its corresponding start process). How is this done?
The page has a property Prop.StartTime and this on the Session diagram is set to DATETIME(0.75) to make it 18:00. Want to map this instead to Prop.Start_time of Shape corresponding to the beginning process, something like ThePage!PropStart_TIME =Prop.Uproc(=Session1_B)!Start_time
Dispite numerous attempts seems unable to get ThePage properties to map to the value of the properties of a shape, though this can be done the other way round. After a rethink the solution was to put the _B process at the top of the mini sheet and make its
PinY=(ThePage!Prop.StartHeight).
This puts the first process at the top of the page regardless of what time it started.
Subsequent process will have to be placed on the page a locations relative to this start shape. Relatively simple. The start shape had its name changed to something sensible (in this case the actual name of the process) as easier to work with than shape.14.
The PinY for subsequent shapes is similar to the big diagram only time is relative to the start of a session rather than the start of the batch. With the new data format there is now a Run_notrun property on each shape so the shapes that don't run can be made invisible and placed at default position at the top of the diagram (thus the if part in the PinY below). As Jumpy mentioned before 1 is in fact TRUE so the if statement is quite simple.
PinY=GUARD(IF(Prop.Run_notrun,ThePage!Prop.StartHeight,ThePage!Prop.StartHeight+(START_PROCESS_B!Prop.Start_time-(Prop.Start_time))*ThePage!Prop.Scale))
To make shape invisible when not run
In Geometry1 section NoShow cell =IF(Prop.Run_notrun,TRUE,FALSE)
and
in Miscellaneous section HideText=IF(Prop.Run_notrun,TRUE,FALSE)
To complete the invisibility go to View menu then uncheck show connectors and Robert is your fathers brother.
Next bit the repetive task of creating 95 mini diagrams and hyperlinking them to the main batch diagram. Shouldn't be too bad as the tricky stuff should be done now.
Quote from: visio on July 07, 2010, 10:13:00 AM
Dispite numerous attempts seems unable to get ThePage properties to map to the value of the properties of a shape, though this can be done the other way round.
Why is this a problem? I tested it with a simple Box shape. Gave it the Prop.Test with value 20.
Then looked up it's true name wich was Box.1.
Then I made an entry in the ShapeSheet of the page Prop.Testdisplay and put in the value column the formula =Box.1!Prop.Test
And that worked.
In your version it would be:
ThePage!PropStart_TIME: =Uproc!Prop.Start_time (with (Uproc=Session1_B)
Maybe you have to look up true name of the shape.
-------------
Or didn't I understand the true problem?
Maybe you could post a picture of the current state of things.
---------------
Do you have sth. like a timeline shape on one side as a kind of legend?
No you understood the problem fine. The solution I came up with does the job and also forces me to ensure that the Begin and End Shapes are named correctly making future troubleshooting easy and also means that I now have Templates for start, end and normal processes. All very future proof.
Working on the remaining mini visio diagrams and hyperlinks working a treat.
Also got a line shape on the mini diagrams to indicate the start and end times on the text of the line, derived from the value of a shape property. No obvious to do this but found a way. Need to create a section called Text Fields in the shapesheet. Easy way is to do the following
-ensure that the name of the shape to be referenced is know (check format special on that shape and remember or change name to something sensible)
-make a line
-select the line
-menu (at top of visio) insert > field (a window opens)
-select custom formula (as using time referenced from other shape)
-make formula equal to desired value in this case =(EndProcessName!Prop.End_time)
-make sure format is correct for the property selected in this case {{HH:mm}}
The time will now be displayed as a label on the line. Now on the shapesheet of the line you will notice a new section called 'Text Fields' with this infomation.
Will post something soon, probably a cut down version for simplicity.
Quote from: visio on July 07, 2010, 10:13:00 AM
In Geometry1 section NoShow cell =IF(Prop.Run_notrun,TRUE,FALSE)
and
in Miscellaneous section HideText=IF(Prop.Run_notrun,TRUE,FALSE)
Just seen, this statement could although be easier with
NoShow cell = Prop.Run_notrun
and
HideText = Prop.Run_notrun
because as you said yourself, Prop.Run_notrun is either interpreted as True or False.
Retried mapping page properties to shape properties but still getting formula error.
Wanted to adjust the scale of the drawing to fit a page 400mm in height.
(End time of end process - Start Time of begin Process) X Scale = 400 mm
400
Scale = -----------------------------------------------------
(End time of end process - Start Time of begin Process)
So in the scale property the value should be =Prop.pageHeight/(End_process!Prop.End_time-Start_process!Prop.Start_time)
However all that is happening is a formula error. Even tried to map this to just the value of a shape property.
scale = Start_process!Prop.Start_time
But to no avail. Checked the correct name of the shape being referenced in the page shapesheet and the name was correct. (from before the _B and _E names were deliberately changed to make referencing easier)
Its either a bug or quirk of Visio or I've missed something fundamental.
Just to be sure not going mad too, tried the same reference formula and used this on a property in a different shape and the value worked out fine, its just in the page shapesheet...hmmmm (head scratch)
Not really that big a deal just more for visibility and in the webpage there are no scale marking (squared background) and the zoom function will do the needfull.
Quote from: visio on July 09, 2010, 02:21:32 PM
But to no avail. Checked the correct name of the shape being referenced in the page shapesheet and the name was correct. (from before the _B and _E names were deliberately changed to make referencing easier)
That means, it won't work, even if you simply put in a cell of the PageSheet:
=End_process!Prop.End_time
?
Then it can only be a problem with the name, I think. There is often trouble with the name. For example, if you change the name first time, the nameU (the universal name) will be changed, too. When you change it a second or third time, the universal name will not be changed. Maybe that is the problem, because for example in my german localisation of Visio, I have to either use the universal name of a shape in the ShapeSheet or use the ihe ID-Name, like Sheet.123.
So even if you think you have the right name, I would test it out a bit.
Select a shape and start this VBA code:
Sub Namentest()
Dim shp As Visio.Shape
Set shp = ActiveWindow.Selection.Item(1)
MsgBox "Name: " & shp.Name
MsgBox "NameU: " & shp.NameU
MsgBox "NameID: " & shp.NameID
MsgBox "ID: " & shp.ID
End Sub
----------
Are you saving your details-processes in the same drawing (only on different pages)? Then you have to look, that the start and esd process has another name everytim. If you don't Visio will give it another name with adding (.123) a number.
Success!!!!
Ran the VBA against shape LFAQ3300_B and found that the Name and NameU were both set to LFAQ3300_B as expected. The NameID however was set to Sheet.105 and the ID 105. This id was also the same as seen in the format special of the shape.
Tried the mapping on the page shapesheet and used the NameID, Sheet.105, and as if by magic the reference worked!
Bit annoying that Name and NameU didn't reference to the page but will to other shapes, but good that now know how to do the page reference bit, use the NameID of the shape.
Thanks Jumpy, now on with completing the diagram and posting a demo.
Sorry been away for a while, finally got this done, just about to post an example but cant get (remember) data source to link to field in thepage.
Have excel source with 2 Columns. Name of the Excel sheet is Batch_date. Name of the Columns are Start and Date. Value for Start is StartDate and Value for Date is Date.
On the sheet have a user defined field called Prop.StartDate. Label is "Date" Type=5. Value should be equal to the current date for example DATETIME(400407) which is 17/08/2010.
Basically what I did was copy the sheet from my completed master and paste onto a new Visio diagram in order to create a cut down version of the solution to post here. When this happened the link to the page disappeared and don't know how to get back. All that is being offered is link to shapes rather than link to page...
If this bit gets sorted then can post solution. Alternative is copy big master and remove extra info until a simple solutionl Think 100 Sessions with around 1000 sub-processes in linked pages bit much.
Hi visio,
congratulations. I think that was a big piece of work, especially the "detail" version.
Nice to know that it works.
Jumpy
Here is the final solution (cut down version). This has 3 sessions each with sub processes. The run times are supplied in the attached spreadsheet. The times can be manipulated to change the diagram. In this case the connectors are shown (view menu) as this gives visibility to the position of processes which are not run. To make these truely invisible turn connector points off.
If the visio diagram is saved as a webpage then the hyperlinks will show the detail of the Sessions diagram. Pressing Ctrl when clicking on the mouse will show some information on the processes such as runtime and failures.
(if anyone can spot how I managed to link thepage to the batchdate external data then let me know as still can't figure out how I did this - look at linked shapes on the batch_date in the external data window and you will see thepage is linked. This is important as the date is used as an anchor point for the drawing of the Sessions diagram)
One final note on this. Noticed that a few times if data is refreshed (from excel) certain shapes do not update their data instead taking default values from the original master. Other shapes on the same page which are clones of this master do update their info.
It looks like if one day there is no runtime data as a report is notrun visio puts in default data from the master. The next time an update happens and there is data the master data will not overwrite so the shape does not draw correctly.
The problem with this is that when saved as a webpage the position of the process is so far up the page (using old data) that the page does not look right.
To fix this all that is required is to unlink the shape and relink the shape to the data and everything goes back to normal. Bit of a pain as in order to fix completely would mean unlinking all the shapes and then relinking each time data is refreshed. Thats 1000 shapes spread over 100 pages...
Is this a bug in Visio?
Think got a workaround for this.
Problem was if a process did not run then no start or end times. This was passed to Visio by excel and it looks like visio just took the default values set in the master sheet. It also looks like that as no data had been passed to Visio the link was incomplete. Next time when the same process ran and had a start and end time Visio did not update as the link to that data had not been established. Unlinking and relinking established the link which is a bit fiddly.
Workaround was to supply dummy data. As there is a not_run field, which will set the position of the shape if it didn't run, it doesn't matter what the data is as the shapes not_run parameter will override any dodgy positioning. By passing dummy data the link to Visio is maintained as Visio is always getting something even if it meaninless. Next time an update is run as in the situation above the link is maintained and visio draws things as expected.
Bit of a flaff but seems to working for now, will update in a few days if everything remains stable.
I remembered to have thought about that problem somewhen. Some posts back in this thread I found it. Maybe the solution helps you.
The DataGrafic window is mentioned. It can be used, even if your linked shapes don't display DataGrafics.
Quote from: Jumpy on June 22, 2010, 07:54:14 PM
Edit: Tried it out, worked. But when you update and have no Data for montly/weekly shapes, the link to those shape breaks. You get an error message in the DataGrafic Window, but you can choose to ignore this. Later, when you update again when you have Data for those shapes, the link is still broken and the shapes are not updated. But you can choose to link shapes automatic to the ExternalData and you got all links back with one mouse click. But it must be possible to identify the right Data for each shape, that means your Data (=processes) must have sth. like a unique key, for example a process name or number (when its unique).
Relinking is fairly simple but wanting to make this an automated process. The dummy data is working well. Dummy data would only be passed to shapes linked to processes that did not run and have not_run set to 1. The not_run parameter sorts the placement problem and all links are maintained.
Also as the dummy data is put in at source (code added to the extract data script) so the Visio diagram is uneffected.
Drawing the bacth process now takes a couple of mins but that is mostly dur to manually moving the data around. Once fully automated should take less than a minute with the Visio update of the diagram itself taking seconds.
One final question (for extra credit)
Want to automate the update process and the save as webpage. Got the update part done and teh save as webpage but would like to have a timestamp added to the webpage name. The obvious date is actually from the linked data, the date of the batch run.
It is possible to refer to the linked data as part of a vba macro to save the visio disgram as a webpage? Currently just got it saving as the time from the PC but the batch run date more useful. (This could also be extracted from the property of a shape if this is could be referenced)
Application.Addons("SaveAsWeb").Run "/quiet=True /target=c:\Batch_diagrams\batch_diagram_" & Format(Now(), "ddmmyy") & ".htm"
would be better as below but how?
Application.Addons("SaveAsWeb").Run "/quiet=True /target=c:\Batch_diagrams\batch_diagram_" & <date from Visio linked data - sheet, batch_date column Date Row 1 > & ".htm"
You say the Timestamp is after the update stored in a shape, too.
If it is always the same shape, you know it's name, or even better it's ID.
You could search in your VBA code for that shape and read the ShapeData Value from it, that you need.
Store it in a variable and use that instaead of the NOW().
The last word on this..no really.
Got some code that does the trick for the autosave. This can be used to scan any visio page for a shape and extract a property. The tricky bit was working out what pages and shapes are actually called. This code is called as a Visio macro.
After the diagram data is updated it scans the current page (set to the Sessions page) for a particular shape (by shape name as these are unique in this case a shape called upr_sten - look at format > special to find shape name). Once found it checks if a property is valid for that shape and if it is then sets a variable, as a string, equal to the value. In this case its the date in the format dd/mm/yyyy which is no use as a filename extension as the spashes mean directories. To get round this the mid command is used extract the relevent fields of the string so the output is in the form ddmmyyyy. This is used as a timestamp for saving the diagram as a webpage.
Finally the Visio sheet is saved as a webpage, silent is set to true to prevent the saved page being automatically opened in a browser following its creation.
Sub Update()
'Set variables to be used to scan shapes on page
'Visio Page object
Dim pagObj As Visio.Page
'Visio Shapes collection
Dim shpsObj As Visio.Shapes
'Visio Shape object
Dim shpObj As Visio.Shape
'Visio Cell object
Dim celObj As Visio.Cell
'Array to hold purchase order info
Dim OrderInfo() As String
'Counter
Dim iShapeCount As Integer
'Counter
Dim i As Integer
'Update the Visio diagram using data from the Excel spreadsheet (data source)
Dim UndoScopeID1 As Long
UndoScopeID1 = Application.BeginUndoScope("Refresh Data")
Application.ActiveDocument.DataRecordsets.ItemFromID(5).Refresh
Application.EndUndoScope UndoScopeID1, True
Dim UndoScopeID2 As Long
UndoScopeID2 = Application.BeginUndoScope("Refresh Data")
Application.ActiveDocument.DataRecordsets.ItemFromID(9).Refresh
Application.EndUndoScope UndoScopeID2, True
Dim UndoScopeID3 As Long
UndoScopeID3 = Application.BeginUndoScope("Refresh Data")
Application.ActiveDocument.DataRecordsets.ItemFromID(10).Refresh
Application.EndUndoScope UndoScopeID3, True
Application.ActiveDocument.Save
'Update of data now complete, now find the batch date from data stored in a shape property - shape is called upr_sten
'Extract the batch run date from the Visio diagram and use as date to save webpage
'Get the active page
Application.ActiveWindow.Page = Application.ActiveDocument.Pages.ItemU("Sessions")
Set pagObj = ActivePage
'Get the Shapes collection of the page.
Set shpsObj = pagObj.Shapes
'Total number of shapes.
iShapeCount = shpsObj.Count
'For each shape on the page check if the shape name matches the target shape
For i = 1 To iShapeCount
'Get the i'th shape.
Set shpObj = shpsObj(i)
If shpObj.Name = "upr_sten" Then
'Get the date from shape upr_sten as a string.
If shpObj.CellExists("Prop._VisDM_Date", visExistsLocally) Then
Set celObj = shpObj.Cells("Prop._VisDM_Date")
End If
End If
'Release Shape object.
Set shpObj = Nothing
Next
'Save as webpage, the cadate changes the date number to dd/mm/yyyy and the mid bits print the date without the '/'
'Silent mode used to prevent webpage starting up automatically
Application.Addons("SaveAsWeb").Run "/silent=True /target=c:\batch_time_master_" & Mid(CDate(celObj), 1, 2) & Mid(CDate(celObj), 4, 2) & Mid(CDate(celObj), 7, 4) & ".htm"
End Sub
Good to see, that it works. So my following tipps may not neccessary, but maybe you can use them anyway:
Quote
Application.ActiveWindow.Page = Application.ActiveDocument.Pages.ItemU("Sessions")
Set pagObj = ActivePage
could be shorter with
Set pagObj = Application.ActiveDocument.Pages.ItemU("Sessions")
unless it is neccessary to change the ActivePage for the later saving as Web.
Because you have many shapes on your page it could be conveniant, to leave the For-Next loop, with "Exit For", when you hav found the right shape. So your code won't have to make many unneccessary loops.
Also it may be better to loop through the shapes with sth. like
For Each shaObj in shpsObj
Next
I read in an Excel-Book, that it is recommended to use this technik (in Excel) because it is faster. Maybe that's true for Visio, too.
And it has the benefit, that you need less variables.
By the way?What is the Orderinfo() as String for?
Just my 2 cents.
The order variable is a red herring and has been removed.
Changed the line as you suggested. Couldn't figure syntax out at first, and the 2 lines ended up being a workaround.
The If startement now has extra line so if the correct shape is found then i is set to iShapecount essentially stopping the search.
For i = 1 To iShapeCount
'Get the i'th shape.
Set shpObj = shpsObj(i)
If shpObj.Name = "upr_sten" Then
'Get the date from shape upr_sten as a string.
If shpObj.CellExists("Prop._VisDM_Date", visExistsLocally) Then
Set celObj = shpObj.Cells("Prop._VisDM_Date")
i = iShapeCount
End If
End If
'Release Shape object.
Set shpObj = Nothing
Next
Last bit now.
Writing an external vbs script to open excel, run a macro to update data from source then open visio and run the macro to update visio and save as webpage.
Both macros work fine when run manually. The code I have for the external vbs sript will do the excel bit and for Visio it will open visio then open the doc but when it cones to run the macro having difficulty. Looks like syntax is not the same as for Excel. The ideal scanario would be the schedule the vbs script to run daily and produce a daily webpage from updated visio drawing based on the updated excel data.
My code below, relatively simple. The excel macro is called "Visio_data" and the Visio macro is called "Update"
Dim excel
Dim visio
set excel = createobject("Excel.Application") '##create excel object
set visio = createobject("Visio.Application") '##create visio object
excel.visible = true
excel.workbooks.open "c:\batch\Visio_data.xls"
excel.Run "Visio_Data"
excel.quit
visio.visible = true
visio.documents.open "c:\batch\batch_time_master.vsd"
visio.run "Update"
visio.quit
The visio.run command is not being liked by Visio, error is 'does not support this property or method:run'. Tried other commands like visio.addons("RunMacro").Run ("Update") or visio.cmdrun but to no avail. Any ideas?
I don't know, how to trigger Macros in Visio from another app, but maybe the following is a workarround:
You could place a Macro in the ThisDocument Modul of the Visio drawing. It is reacts to the document opened event and starts your real macro:
Private Sub Document_DocumentOpened(ByVal doc As IVDocument)
'Eventually you could place a small timeout here before you start the macro
'to enable Excel to be fully updated and closed again
'and Visio to be completly started
'Only if neccessary
YourMacroNameHere
End Sub
Didn't try it out, but should work. At least if document_opened event is triggered, when Visio is opened via VB.
--------
Another thing came to mind, concerning the search for the right shape loop. If you always use the same drawing or template for this, it may be that the Item number of that shape is always the same. You could test this with going to the right page and starting the following proc.
Sub Nr_Test()
Dim i As Integer
For i = 1 to ActivePage.Shapes.Count
If ActivePage.Shapes(i).Name = "urp_sten" Then
MsgBox i
End If
Next i
End Sub
If i is always the same you could simply adress your shape in the code without any loop at all, with sth. like (if i is for example 5):
Set celObj = shpsObj(5).Cells("Prop._VisDM_Date")
In this case you could mabe do much from the above in a One-Liner:
Dim Dat as String
Dat = ActiveDocument.Pages("Name of the right page").Shapes(TheNumberFromAbove).Cells("Prop._VisDM_Date").ResultStr("")
Than you could use a String-Function to cut out the "/":
Dat = Replace(Dat,"/","")
and even that could be put in one line but would not be easy to look at any longer...
The run macro on Doc open does the trick. The external vb script opens Visio then opens the doc which automaticlly runs the update macro, the effect is the same as if the external vb script did everything. Thanks