Author Topic: HELP! Heat Map / Status Map / Not sure what to call it!  (Read 12179 times)

0 Members and 1 Guest are viewing this topic.

ack57b87

  • Newbie
  • *
  • Posts: 2
HELP! Heat Map / Status Map / Not sure what to call it!
« on: September 12, 2011, 06:16:30 AM »
I wonder if you could help. Forgive me also if I posted this in the wrong category!!!

I am looking to creat a heat map for compliance. Sounds easy and in basic terms it is.

What I want is for the worst case scenario to displayed... let me explain!

I have a list of Buildings... lets say Building A, B, C
I have a list of compliance issues... lets say 1, 2, 3, 4, 5

Each building will have all 5 compliance elements pertaining to the building.

Easiest scenario first... if all 5 elements are compliant, ie GREEN, then the building will be coloured GREEN.

If any element is out of compliance, therefore RED, I want the building colour to be RED.

I appreciate that the individual compliance elements will need to be altered manually, probably through an excel sheet if I am honest, but is there any way to provide the function above in Visio?

I have done all sorts of search engine searches but without the correct terminology I have drawn a blank. I have tried Status maps, compliance map, heat map, SCADA etc...

I have attached an excel sheet which I have had to colour manually... hopefully it will better explain my point!

Any help or pointers gratefully received :o)

vojo

  • Hero Member
  • *****
  • Posts: 1576
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #1 on: September 12, 2011, 07:58:21 AM »
open shape sheet

user.comp1 = <1 or 0 for compliance item 1>
user.comp2=
user.comp3=
user.comp4=
user.comp5=

fillforegnd = if (user.comp1+user.comp2+user.comp3+user.comp4+user.comp5>0,RGB(255,0,0),RGB(0,255,0))

If you use RGB, you can actually give degrees of redness to the non compliance (or other colors like yellow).

If you want some sort of "5 rung ladder" bar....a bit more complicated, but basically this idea for 5 "rung" shapes grouped with the building
(math a bit more complicated....but play with it and you will get the idea).

user.comp cells can be props cells if you want to edit on main screen in vision
(Might want eventdblclk = docmd(1312) if you want to double click shape to update comps manually).

ack57b87

  • Newbie
  • *
  • Posts: 2
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #2 on: September 12, 2011, 08:32:56 AM »
Thank you for your response. Please forgive my newness to Visio... but none of your response made any sense at all!

I am using Visio 2003 so maybe that is why?

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #3 on: September 12, 2011, 02:25:56 PM »
Your addition of the v2003 is a necessary piece of the puzzle, in my response I would have just suggested using data graphics (assumes you are at least at v2007 pro or higher). Vojo's response means that you have to do a little shape engineering in the shapesheet which is explained over here...

http://msdn.microsoft.com/en-us/library/aa245244(v=office.10).aspx

and not for the usual newby....
al

vojo

  • Hero Member
  • *****
  • Posts: 1576
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #4 on: September 12, 2011, 11:42:44 PM »
Al is correct....takes some practice to do this....so

1.   make a square
2.   shape==>custom properties
3.   name the first one testdata1   (make top 2 entries testdata1,....as you get better, you can mess with the other fields)
4.   repeat for say 2 others...testdata2....testdata3
5.   done/finish
6.   right click the square
7.   open shape sheet  (notice a section 2 down called props or custom properites....your 3 testdata fields are there...specifically  props.testdata1, props.testdata2, etc....dont touch them)
8.   page down to fill section
9    in the cell called fillforegrnd, type = if(props.testdata1+props.testdata2+props.testdata3>0,RGB(255,0,0),RGB(0,255,0)
10. farther down is a section called events....in eventsdblclk type = docmd(1312)
11. done / finish

now double click square....your 3 props come up.....change testdata2 to a 2....done/finish....see the square turn red
repeat....testdata2 = 0....done/finish...see square turn green

study this...and you are on your way

From here, you can look at how you want testdatas populated from spreadsheet and such.....this takes even more effort....wizards and VBA
(I dont do any sort of automatic link to excell...I use visio strictly for drawing stuff....but I am sure Al and others can point on your way)

good luck

deepakmalik

  • Newbie
  • *
  • Posts: 3
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #5 on: October 29, 2012, 06:26:13 PM »
Hi

Can some one please advise on how to link excel data to visio shapes...as mentioned in the last post...
Basically i am trying to generate a heat map..

I have got a visio structure (very basic, just set of linked squares) i want to colour those boxes based on the status of items in those box in an excel file...

For example..

The box in visio file says X PLan
In the excel file the Status of the X Plan is submitted

I want to have the box coloured as Yellow

Now the status of X plan in Approved
I want the colour of the box to be Green

Note: I am using visio 2007

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #6 on: October 30, 2012, 02:55:58 AM »
Standard or Professional Version?
Because linking shapes (ShapeData) to Excel needs Professional version.
Otherwise you have to code it yourself with VBA.

deepakmalik

  • Newbie
  • *
  • Posts: 3
Re: HELP! Heat Map / Status Map / Not sure what to call it!
« Reply #7 on: October 30, 2012, 05:25:30 PM »
I am using Visio 2007 standard...
Can i please get some help with coding to link shape data in visio to excel.
I am a novice coder and would seriously have no idea on how to do it in visio.
Thanks