0

I am trying to work my way around, adding KPIs next to a desired value.

I have tried this so far by using this in the calculated column formula field

<img src=" "/_Layouts/images/kpidefault-" & If ([Product cost] - [Materials cost] > 0

Basically i want to say a Green KPI should appear if the difference is > 0.

Any help appreciated. Many thanks.

  • "<img src=" "/_Layouts/images/kpidefault-" & If ([Product cost] - [Materials cost] > 0" this is what I tried – Oranges_Oranges Jun 14 '16 at 8:02
  • formula is incomplete – Gaurravs Jun 14 '16 at 8:11
  • explain what exctly your poutput is expected, url img src seems to incomplete – Gaurravs Jun 14 '16 at 8:19
1

You can use the following formula:

=IF([Product cost] - [Materials cost]>0,[Product cost] - [Materials cost]&"<img src='https://cdn2.iconfinder.com/data/icons/splashyIcons/arrow_large_up.png'>","")

(since your icon URL is invalid, I changed it to the URL of an icon on the internet)

Don't forget to set the data type of the calculated column to Number, otherwise it won't work.

  • Formula won't save because there is an extra ( To output HTML Datatype can be anything but Text. Number will add an extra DIV and align the HTML to the Right. Other datatypes have different alignment – Danny '365CSI' Engelman Jun 14 '16 at 9:41
  • Thanks for pointing out that extra bracket. I removed it. – Daniel Stölzner Jun 14 '16 at 9:47
0

=If ([Product cost] - [Materials cost] > 0,[Product cost] - [Materials cost],"")

This formula will give you the difference of [Product cost], [Materials cost] if it is greater than zero, else it will be blank.

To combine what you want, try this formula.

   = '<img src="/_Layouts/images/kpidefault-"' & If ([Product cost] - [Materials cost] > 0,[Product cost] - [Materials cost],"") & '/>'

PS: Source is used as is from question. Make sure your image source is valid.

0

The syntax for displaying an Image with a Calculated Column (in a View only) is:

="<img src='image.gif'>"

(you need to set the Datatype to output as anything but Text, different datatype will output different Alignments)

There are 3 default images available by SharePoint

as

="<img src='/_layouts/images/kpidefault-0.gif'>"
="<img src='/_layouts/images/kpidefault-1.gif'>"
="<img src='/_layouts/images/kpidefault-2.gif'>"

You want the green image to be displayed based on a calculation: this is pseudo code:

IF( [Product cost]-[Materials cost] > 0 , GREEN , NOTHING )

So the full Calculated Column Formula is:

=IF([Product cost]-[Materials cost]>0,"<img src='/_layouts/images/kpidefault-0.gif'>","")

To add the difference as label:

=[Product cost]-[Materials cost] & 
 IF( [Product cost]-[Materials cost]>0 
     , "<img src='/_layouts/images/kpidefault-0.gif'>"
     , ""
   )
  • It is perfectly valid to add linebreaks and spaces, SharePoint will ignore them when you paste the Formula in the Calculated Column and save it.
    If you always start from a texteditor (Notepad) like this, you have more insight in the flow of your Formula.

  • Excel can be helpfull in building Formulas, as most basic Functions are the same See http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List

  • It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula (with an undescriptive message) and you end up having to click multiple times to get back to your Formula. I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback. See: https://github.com/Danny-Engelman/CalcMaster

created from a 0, 1 ... Number value

If you want to create an image based on a Number field,

The Formula is (pseudo code):

="<img src='/_layouts/images/kpidefault-" & NUMBER & ".gif'>"

CSR

With (SP2013+) Client Side Rendering, all this can be simplified with some minor JavaScript code and the OpenSource iCSR.Github.io Library

        iCSR.Me({
            Fields: {
              "Priority":{
                View : iCSR.Priority
       }});

Produces an interactive View:

Update #1 HTML only

Here is HTML code to color the profit as text using CSS

="<span style='"
 & IF( [Product cost]-[Materials cost]>0 
     , "color:green;font-weight:bold"
     , IF( [Product cost]-[Materials cost]<0 
           , "color:red"
           , "color:black"
         )
   )
 & "'>"
 & [Product cost]-[Materials cost]
 & "</span>"
  • How can I choose your answer as the best answer. Thanks alot. It solves my query. Very good tutorial – Oranges_Oranges Jun 14 '16 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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