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.

Improve this question

2 Answers 2

Reset to default
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

Improve this answer
7
  • Under my KPI column i now get "FAlSE" and no icons.
    – Craig
    Commented Feb 11, 2015 at 16:24
  • Are you using SharePoint 2010 or SharePoint 2013? Have you sett the column to "number" type?
    – Christoffer
    Commented Feb 12, 2015 at 7:59
  • 2007 unfortunatley.
    – Craig
    Commented Feb 12, 2015 at 8: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
    Commented Feb 12, 2015 at 8:10
  • 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 blog.pathtosharepoint.com/2009/06/04/…
    – Danny '365CSI' Engelman
    Commented Feb 12, 2015 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

Improve this answer
4
  • i tried this also but i get the following under my KPI column <img src="/_layouts/15/images/kpidefault-2.gif">
    – Craig
    Commented Feb 11, 2015 at 16:30
  • You have to set the datatype of the Calculated Column to Number to make the browser execute the HTML, Single line of text will onl display the HTML as text.
    – Danny '365CSI' Engelman
    Commented Feb 11, 2015 at 21:35
  • Yeah it is set as number but i am using Sharepoint 2007
    – Craig
    Commented Feb 12, 2015 at 8:12
  • Sorted removed the /15.. must be something to the setup of sharepoint we have. Thanks for your help!
    – Craig
    Commented Feb 12, 2015 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.