Data graphic custom formula

[dasadler]

Wow, I just discovered shape data.  Can't believe I have used Visio for years for flowcharting and floor plans and never used shape data.

Okay, I have a bunch of shapes (an org chart), each with two relevant fields.  One is a boolean field (HIRED) and the other is a number field (SALARY).

I made a new text box and want it to display the total (sum) of all the SALARY fields for the shapes where HIRED=True.

If I understand correctly, this can be done with data graphics but I just don't know how to structure the formula.

Help please?

[aledlund]

you might try it with vba. This example adds the necessary master data to the page sheet and then calculates the page totals. You can then apply datagraphics against the page.

al

[dasadler]

Thanks Al, that is very instructive.  I have to remove the following line to make it work on my machine.

    If visApp Is Nothing Then
        initDoc
    End If

Your approach assumes consistent salaries for each level so you can multiply salary time # of positions.  The approach I need is to actually take the salary of each position and add it to a bucket so I can ultimately show the total salaries for that page.

[aledlund]

Between the two methods they demonstrate adding the custom properties to a shape (the pagesheet is technically a shape), adding a value to the shape, and then reading all of the shapes on a page for what type are they and then adding a value to an array to get a summation for the page. That's pretty much the list of what you need to do for what you're looking for. Add a salary to a shape and then add the shape salarys to get the total.
al

[dasadler]

Thank you for your response. I guess I have not done a good job of explaining my challenge.

I have an org chart that represents some some point in the future.  In other words, the org chart shows the expected structure when the company grows into it.  This means many of the positions, though labeled, will actually be vacant.

I would like to be able to examine various hiring possibilities by assigning a salary to each position and there will be variance among salaries even though they may appear, visually, to be at the same level.  The various hiring possibilities would be examined by changing the HIRED field to True or False.  If it is True, the SALARY would be summed.

I have a macro that runs without error but is not summing SALARY as expected.  The intent of the macro is to loop through each shape on the page and check for the existence of the HIRED and SALARY fields.  If those two fields exists, then check if HIRED is True and, if so, add the value of SALARY to a variable (pay).

The code is below... again, it runs without error but only give 0 (zero) as the SALARY sum.  I was hoping to learn what is wrong with the code?

Sub Paytotal()
Dim pag As Page
Dim shp As Shape
Dim pay As Long
'Set reference to correct page
Set pag = Application.ActivePage
pay = 0
'Loop through all shapes on page
For Each shp In pag.Shapes
    If shp.CellExistsU("Prop.HIRED", False) = True And shp.CellExistsU("Prop.SALARY", False) = True Then
        If shp.CellsU("Prop.HIRED").Result(visNone) = True Then
            pay = pay + shp.CellsU("Prop.SALARY").Result(visNone)
        End If
    End If

Next shp

MsgBox pay
End Sub

[aledlund]

can you share a simple drawing (since it really depends on also being able to look at the shapes you have created :-) ) ?
al

[Jumpy]

If you per chance have an german or other non english Version of Visio you should use ResultU instead of Result:

If shp.CellsU("Prop.HIRED").ResultU(visNone) = True Then

In my german Version Result is not "True" but "Wahr" and so in your code the If...Then is never true and so nothing gets added to pay. You could test this easily with placing a "stop" point in your code and move through your code with F8 in debugmode.