3

I am trying to create a KPI for the tasks schedule we have on a calendar.

The formula I have is:

"<div><img src='/_layouts/images/KPIDefault-"&IF(Completed="Yes",0,2)&".gif'/></div>"

This works however I would like to add that if a task is (Completed="In Progress") that it changes to the yellow triangle icon.

The formula I have tried is:

=<div><img src='/_layouts/images/KPIDefault-"&IF(Completed="Yes",0,2)&".gif'/><img src='/_layouts/images/KPIDefault-"IF(Completed="In Progress",0,1)&".gif'/></div>

I realize that this is probably dead easy but I am new to calculated columns.

The default icon wants to be red.

Eric Alexander
  • 43,293
  • 10
  • 53
  • 93
Craig
  • 31
  • 1
  • 2

2 Answers2

4
="<center>"&IF([Completed]="", "<img src='/_layouts/images/KPIDefault-2.GIF' border='0'/>",(IF([Completed]="In Progress","<img src='/_layouts/images/KPIDefault-1.GIF' border='0'/>", (IF([Completed]="Yes","<img src='/_layouts/images/KPIDefault-0.GIF' border='0'/>")))&"</center>"

Remember to set the calculated column to type "Number" for the calculation to work.

enter image description here

Christoffer
  • 9,801
  • 2
  • 36
  • 53
  • Under my KPI column i now get "FAlSE" and no icons. – Craig Feb 11 '15 at 16:24
  • Are you using SharePoint 2010 or SharePoint 2013? Have you sett the column to "number" type? – Christoffer Feb 12 '15 at 07:59
  • 2007 unfortunatley. – Craig Feb 12 '15 at 08:04
  • Updated my answer so the folders points to the same directory as in your working example. Mine pointed to the SharePoint 2013 directory.. So does Dannys answer do, if you try to remove the "15" from the directory adress, he's answer should work as well. – Christoffer Feb 12 '15 at 08:10
  • Sorted removed the /15.. must be something to the setup of sharepoint we have. Thanks for your help! – Craig Feb 12 '15 at 08:34
  • Every version of sharepoint uses differents directorys.. so /14/ for SharePoint 2010, /15/ for SharePoint 2013.. Glad it worked out. – Christoffer Feb 12 '15 at 09:03
  • 1
    If you are using SharePoint 2007 I think (I am not sure) you are out of luck. The datatype=number trick to use HTML works since 2010. On 2007 you have to add an extra Javascript to a page to convert the Text to HTML; see http://blog.pathtosharepoint.com/2009/06/04/html-calculated-column-updated-script/ – Danny '365CSI' Engelman Feb 12 '15 at 17:38
1

The other answer is correct.

I suggest to rewrite it a bit, more like your original formula

If you are starting out with calculated columns it helps to keep them as short as possible and spread them over multiple lines.

="<img src=""/_layouts/images/kpidefault-"
  &IF([Completed]="In Progress","1", IF([Completed]="Yes","0","2"))
&".gif"">"

Note

  • the default value is 2 , no need to use an extra IF for that
  • I replaced the single quotes that open/close the src attribute with TWO double quotes, this results in a single double quote when the Formula is calculated and makes it better readable when you output the Formula as Text to verify the HTML
  • the border attribute for an IMG is 0 by default, so no need to include it

More at: https://www.365csi.nl/vm365com/#/How

Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79