Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Sparklines / Add Sparklines using Formulas / Gauge KPI Sparkline
In This Topic
    Gauge KPI Sparkline
    In This Topic

    Spread for Winforms provides gauge KPI sparkline which can be used to visualize the performance of metrics with respect to KPI values. It can be used to indicate the effectiveness of management work or present sales targets etc.

    gauge KPI sparkline metric labels

    Gauge KPI sparkline uses the following values to display metrics:

    Types of Gauge KPI Sparklines

    There are three types of Gauge KPI sparklines, namely, Circle, Vertical, and Horizontal which can be set by using the gaugeType option in the GaugeKPISparkline function.

    gauge KPI sparkline types

    The gauge KPI sparkline formula has the following syntax:

    =GAUGEKPISPARKLINE (targetValue, currentValue, minValue, maxValue, [showLabel, targetValueLabel, currentValueLabel, minValueLabel, maxValueLabel, fontArray, minAngle, maxAngle, radiusRatio, gaugeType, colorRange])

    The formula options are described below:

    Argument Type Description
    targetValue Number The target value of the gauge KPI sparkline. The target value is between minValue and maxValue.
    currentValue Number The current value of the gauge KPI sparkline. The current value is between minValue and maxValue.
    minValue Number The minimum value of the gauge KPI sparkline. The minValue is less than maxValue.
    maxValue Number The maximum value of the gauge KPI sparkline. The maxValue is more than minValue.

    showLabel

    Optional

    		 Boolean Specifies whether to show the label of all the values provided in the sparkline.

    If false, it will not show labels.

    If true, it will only show the labels which fit inside the cell width and height. The cell should have enough width and height to show both graph and labels.

    The default value is true.

    targetValueLabel

    Optional

    		 String

    The string to display as target value label.

    The default value is targetValue.

    currentValueLabel

    Optional

    		 String

    The string to display as current value label.

    The default value is currentValue.

    minValueLabel

    Optional

    		 String

    The string to display as minimum value label.

    The default value is minValue.

    maxValueLabel

    Optional

    		 String

    The string to display as maximum value label.

    The default value is maxValue.

    fontArray

    Optional

    		 CalcArray Array contains font format as string items for the four label types -
    • Target value label: Default value "16px Calibri"
    • Current value label: Default value "bold 22px Calibri"
    • Minimum and Maximum value labels: Default value "12px Calibri"

    showLabel option must be true.

    minAngle

    Optional

    		 Number

    The minimum angle value of circle type. The minAnigle should be less than maxAngle.

    The angle values correspond to the time in the clock. 0 is 12 o'clock, -90 is 9 o'clock, 90 is 3 o'clock, and -180/180 is 6 o'clock.

    The default value is -90.

    gaugeType option must be 0 (circle type).

    maxAngle

    Optional

    		 Number

    The maximum angle value of circle type. The maxAngle should be bigger than minAngle.

    The angle values correspond to the time in the clock. 0 is 12 o'clock, -90 is 9 o'clock, 90 is 3 o'clock, and -180/180 is 6 o'clock.

    The default value is 90.

    gaugeType option must be 0 (circle type).

    radiusRatio

    Optional

    		 Number

    The value is calculated as the ratio of inner circle radius and outer circle radius. The outer circle radius value is decided by the cell size.

    The value ranges between 0 and 1. Default is 0.

    gaugeType option must be 0 (circle type).

    gaugeType

    Optional

    		 Number

    The KPI sparkline type.

    0 - Circle
    1 - Vertical Bar
    2 - Horizontal Bar

    The default type is circle.

    colorRange

    Optional

    Repeatable

    		 CalcArray

    The color range between specified values.

    {startValue, endValue, color_string}

    Where startValue is the starting value in a range, endValue is the ending value in a range, and the color_string is the color of the range between the two specified value.

    The startValue must be less than the endValue and both the values should be between minValue and maxValue.

    Default color range from minValue to maxValue is filled with light gray color.

    Usage Scenario

    Consider a scenario where a company wants to monitor its annual sales performance. A gauge KPI sparkline can be used to measure the revenue, profit, and sales metrics against their target and actual values. It helps to visualize the data efficiently and make meaningful deductions.

    Gauge KPI Sparkline

    Gauge KPI Sparkline

    C#
    Copy Code 
    // get sheet
var worksheet1 = fpSpread1.Sheets[0].AsWorksheet();
var worksheet2 = fpSpread1.Sheets[1].AsWorksheet();

// set data
worksheet2.SetValue(0, 0, new object[,]
{
    {"Parameters", "Target", "Current", "Min", "Max" },
    { "Revenue", 900, 1200, 0, 2000 },
    { "Profit", 1200, 1100, 0, 2000},
    {"Sales", 1600, 1800, 0, 2000}
});

// set GaugeKPISparkline formula
worksheet1.Cells["A2"].Formula = "GAUGEKPISPARKLINE(Sheet2!B2,Sheet2!C2,Sheet2!D2,Sheet2!E2,TRUE,TEXT(Sheet2!B2/1000,\"$0.0K\"),Sheet2!A2,TEXT(Sheet2!D2/1000,\"$0.0K\"),TEXT(Sheet2!E2/1000,\"$0.0K\"),,-90,90,0.4,0,{0,1200,\"#FFB2BD\"},{1200,1500,\"#FFDFB0\"},{1500,2000,\"#BCEAB4\"})";
worksheet1.Cells["B2"].Formula = "GAUGEKPISPARKLINE(Sheet2!B3,Sheet2!C3,Sheet2!D3,Sheet2!E3,TRUE,TEXT(Sheet2!B3/1000,\"$0.0K\"),Sheet2!A3,TEXT(Sheet2!D3/1000,\"$0.0K\"),TEXT(Sheet2!E3/1000,\"$0.0K\"),,-90,90,0.4,0,{0,1200,\"#FFB2BD\"},{1200,1500,\"#FFDFB0\"},{1500,2000,\"#BCEAB4\"})";
worksheet1.Cells["C2"].Formula = "GAUGEKPISPARKLINE(Sheet2!B4,Sheet2!C4,Sheet2!D4,Sheet2!E4,TRUE,TEXT(Sheet2!B4/1000,\"$0.0K\"),Sheet2!A4,TEXT(Sheet2!D4/1000,\"$0.0K\"),TEXT(Sheet2!E4/1000,\"$0.0K\"),,-90,90,0.4,0,{0,1200,\"#FFB2BD\"},{1200,1500,\"#FFDFB0\"},{1500,2000,\"#BCEAB4\"})";
    Visual Basic
    Copy Code 
    'Get sheet
Dim worksheet1 = FpSpread1.Sheets(0).AsWorksheet()
Dim worksheet2 = FpSpread1.Sheets(1).AsWorksheet()
        
'Set data
worksheet2.SetValue(0, 0, New Object(,) {
    {"Parameters", "Target", "Current", "Min", "Max"},
    {"Revenue", 900, 1200, 0, 2000},
    {"Profit", 1200, 1100, 0, 2000},
    {"Sales", 1600, 1800, 0, 2000}
})

'Set GaugeKPISparkline formula
worksheet1.Cells("A2").Formula = "GAUGEKPISPARKLINE(Sheet2!B2,Sheet2!C2,Sheet2!D2,Sheet2!E2,TRUE,TEXT(Sheet2!B2/1000,""$0.0K""),Sheet2!A2,TEXT(Sheet2!D2/1000,""$0.0K""),TEXT(Sheet2!E2/1000,""$0.0K""),,-90,90,0.4,0,{0,1200,""#FFB2BD""},{1200,1500,""#FFDFB0""},{1500,2000,""#BCEAB4""})"
worksheet1.Cells("B2").Formula = "GAUGEKPISPARKLINE(Sheet2!B3,Sheet2!C3,Sheet2!D3,Sheet2!E3,True,TEXT(Sheet2!B3/1000,""$0.0K""),Sheet2!A3,TEXT(Sheet2!D3/1000,""$0.0K""),TEXT(Sheet2!E3/1000,""$0.0K""),,-90,90,0.4,0,{0,1200,""#FFB2BD""},{1200,1500,""#FFDFB0""},{1500,2000,""#BCEAB4""})"
worksheet1.Cells("C2").Formula = "GAUGEKPISPARKLINE(Sheet2!B4,Sheet2!C4,Sheet2!D4,Sheet2!E4,True,TEXT(Sheet2!B4/1000,""$0.0K""),Sheet2!A4,TEXT(Sheet2!D4/1000,""$0.0K""),TEXT(Sheet2!E4/1000,""$0.0K""),,-90,90,0.4,0,{0,1200,""#FFB2BD""},{1200,1500,""#FFDFB0""},{1500,2000,""#BCEAB4""})"

    Using the Spread Designer

    1. Type the different values in cells or a column or row of cells in the designer.
    2. Select a cell for the sparkline.
    3. Select the Insert menu.
    4. Select a sparkline type.
    5. Set the Values in the Sparkline Setting dialog.
      Alternatively, set the values by selecting the cells in the worksheet using the pointer.

      GaugeKPI sparkline setting dialog in Spread Designer

      Set the additional sparkline settings as shown in the image above.

    6. Select OK.
    7. Select Apply and Exit from the File menu to save your changes and close the designer.