Forum Discussion

LijaHarris's avatar
LijaHarris
Qrew Cadet
6 months ago
Solved

Document Template not showing Rich Text Formula Field Pie Chart

Hello everyone,

I have a rich text formula field that I would like to show on the Document Template - what is interesting is that the "legend" is showing but not the pie chart itself.  I am wondering if the style is conflicting with Word essentially.  I tried already wrapping in a div to see if that would solve the issue but it is persisting.  The Code is here and the image of the Document template is below the code.  Thank you so much!

Code:

var text SZ = "200"; //Adjust this number to make the pie bigger or smaller

//include a space after each color
var text ACOL = "LightSkyBlue "; //Heating All Fuels
var text BCOL = "Gold "; //Cooling All Fuels
var text CCOL = "Hotpink "; //Ventilation All Fuels
var text DCOL = "MediumOrchid "; //Domestic Hot Water
var text ECOL = "CornflowerBlue "; //Lighting
var text FCOL = "Lime "; //Cooking
var text GCOL = "yellow "; //Refrigeration
var text HCOL = "DarkViolet "; //Office Equipment
var text ICOL = "red "; //Computer
var text JCOL = "orange "; //Misc

// Define Labels
var text ALABEL = "Heating " & ToText(Round([Heating Electric Use Percentage]*100,1)) & "%";
var text BLABEL = "Cooling " & ToText(Round([Cooling Electric Use Percentage]*100,1)) & "%";
var text CLABEL = "Ventilation " & ToText(Round([Ventilation Electric Use Percentage]*100,1)) & "%";
var text DLABEL = "Domestic Hot Water " & ToText(Round([Domestic Hot Water Electric Use Percentage]*100,1)) & "%";
var text ELABEL = "Lighting " & ToText(Round([Lighting Electric Use Percentage]*100,1)) & "%";
var text FLABEL = "Cooking " & ToText(Round([Cooking Electric Use Percentage]*100,1)) & "%";
var text GLABEL = "Refrigeration " & ToText(Round([Refrigeration Electric Use Percentage]*100,1)) & "%";
var text HLABEL = "Office Equipment " & ToText(Round([Office Equipment Electric Use Percentage]*100,1)) & "%";
var text ILABEL = "Computer " & ToText(Round([Computer Electric Use Percentage]*100,1))& "%";
var text JLABEL = "Miscellaneous " & ToText(Round([Miscellaneous Electric Use Percentage]*100,1)) & "%";

//Skip first total

var number B = [Cooling kWh]/[Total Electric Use Chart];
var number C = [Ventilation kWh]/[Total Electric Use Chart];
var number D = [Domestic Hot Water kWh]/[Total Electric Use Chart];
var number E = [Lighting kWh]/[Total Electric Use Chart];
var number F = [Cooking kWh]/[Total Electric Use Chart];
var number G = [Refrigeration kWh]/[Total Electric Use Chart];
var number H = [Office Equipment kWh]/[Total Electric Use Chart];
var number I = [Computer kWh]/[Total Electric Use Chart];
var number J = [Miscellaneous kWh]/[Total Electric Use Chart];

//Displays values from top clockwise
var text pieChartHTML =
"<div style='display:inline-block; margin-left: 30px; margin-top: 0px; vertical-align: middle;'>" &
"<span style='display:inline-block;margin-top: 10px; width:200px; height:200px;" &
 "background: conic-gradient(" &
 $JCOL & " 0turn " & $J & "turn, " &
 $ICOL & $J & "turn " & ($J+$I) & "turn, " &
 $HCOL & ($J+$I) & "turn " & ($J+$I+$H) & "turn, " &
 $GCOL & ($J+$I+$H) & "turn " & ($J+$I+$H+$G) & "turn, " &
 $FCOL & ($J+$I+$H+$G) & "turn " & ($J+$I+$H+$G+$F) & "turn, " &
 $ECOL & ($J+$I+$H+$G+$F) & "turn " & ($J+$I+$H+$G+$F+$E) & "turn, " &
 $DCOL & ($J+$I+$H+$G+$F+$E) & "turn " & ($J+$I+$H+$G+$F+$E+$D) & "turn, " &
 $CCOL & ($J+$I+$H+$G+$F+$E+$D) & "turn " & ($J+$I+$H+$G+$F+$E+$D+$C) & "turn, " &
 $BCOL & ($J+$I+$H+$G+$F+$E+$D+$C) & "turn " & ($J+$I+$H+$G+$F+$E+$D+$C+$B) & "turn, " &
 $ACOL & ($J+$I+$H+$G+$F+$E+$D+$C+$B) & "turn 1turn) !Important;" &
 "border-radius: 50%'>" &
"</span>"&
"</div>";
// --- Manual Key Generation ---
var text keyHTML = "<div style='display:inline-block; margin-left: 30px; margin-top: -10px; vertical-align: middle;'>" &
    "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $ACOL & "; margin-right:5px; border-radius: 2px;'></span>" & $ALABEL & "</div>" &
    "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $BCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $BLABEL & "</div>" &
    "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $CCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $CLABEL & "</div>" &
    
       "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $DCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $DLABEL & "</div>" & 
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $ECOL & "; margin-right:5px; border-radius: 2px;'></span>" & $ELABEL & "</div>" &
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $FCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $FLABEL & "</div>" &
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $GCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $GLABEL & "</div>" &
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $HCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $HLABEL & "</div>" &
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $ICOL & "; margin-right:5px; border-radius: 2px;'></span>" & $ILABEL & "</div>" &
        "<div style='line-height: 1.5;'>" & "<span style='display:inline-block; width:10px; height:10px; background-color:" & $JCOL & "; margin-right:5px; border-radius: 2px;'></span>" & $JLABEL & "</div>" &
    
    
    "</div>";

// Combine the chart and the key
$pieChartHTML & $keyHTML

Here is what it looks like in the Document Template:

Here is what the field shows on the form (The pie is showing):

Thanks so much again!

  • Denin's avatar
    Denin
    6 months ago

    Replace the color section with this one:

    var text ACOL = "#87CEFA";  // LightSkyBlue
    var text BCOL = "#FFD700";  // Gold
    var text CCOL = "#FF69B4";  // HotPink
    var text DCOL = "#BA55D3";  // MediumOrchid
    var text ECOL = "#6495ED";  // CornflowerBlue
    var text FCOL = "#32CD32";  // LimeGreen
    var text GCOL = "#FFFF00";  // Yellow
    var text HCOL = "#9400D3";  // DarkViolet
    var text ICOL = "#FF0000";  // Red
    var text JCOL = "#FFA500";  // Orange

    Problem in existing formula is space after each color name.

7 Replies

  • I've never actually used a rich text field to create a pie chart, but this is super cool. Thanks for sharing!

  • Denin's avatar
    Denin
    Qrew Captain

    Try this:

    var text SZ = "200"; //Adjust this number to make the pie bigger or smaller
    
    var text ACOL = "LightSkyBlue "; //Heating All Fuels
    var text BCOL = "Gold "; //Cooling All Fuels
    var text CCOL = "Hotpink "; //Ventilation All Fuels
    var text DCOL = "MediumOrchid "; //Domestic Hot Water
    var text ECOL = "CornflowerBlue "; //Lighting
    var text FCOL = "Lime "; //Cooking
    var text GCOL = "yellow "; //Refrigeration
    var text HCOL = "DarkViolet "; //Office Equipment
    var text ICOL = "red "; //Computer
    var text JCOL = "orange "; //Misc
    
    var text ALABEL = "Heating " & ToText(Round([Heating Electric Use Percentage]*100,1)) & "%";
    var text BLABEL = "Cooling " & ToText(Round([Cooling Electric Use Percentage]*100,1)) & "%";
    var text CLABEL = "Ventilation " & ToText(Round([Ventilation Electric Use Percentage]*100,1)) & "%";
    var text DLABEL = "Domestic Hot Water " & ToText(Round([Domestic Hot Water Electric Use Percentage]*100,1)) & "%";
    var text ELABEL = "Lighting " & ToText(Round([Lighting Electric Use Percentage]*100,1)) & "%";
    var text FLABEL = "Cooking " & ToText(Round([Cooking Electric Use Percentage]*100,1)) & "%";
    var text GLABEL = "Refrigeration " & ToText(Round([Refrigeration Electric Use Percentage]*100,1)) & "%";
    var text HLABEL = "Office Equipment " & ToText(Round([Office Equipment Electric Use Percentage]*100,1)) & "%";
    var text ILABEL = "Computer " & ToText(Round([Computer Electric Use Percentage]*100,1)) & "%";
    var text JLABEL = "Miscellaneous " & ToText(Round([Miscellaneous Electric Use Percentage]*100,1)) & "%";
    
    
    var number denom = If([Total Electric Use Chart] = 0, 0, [Total Electric Use Chart]);
    var number A = If(denom = 0, 0, [Heating kWh] / denom);
    var number B = If(denom = 0, 0, [Cooling kWh] / denom);
    var number C = If(denom = 0, 0, [Ventilation kWh] / denom);
    var number D = If(denom = 0, 0, [Domestic Hot Water kWh] / denom);
    var number E = If(denom = 0, 0, [Lighting kWh] / denom);
    var number F = If(denom = 0, 0, [Cooking kWh] / denom);
    var number G = If(denom = 0, 0, [Refrigeration kWh] / denom);
    var number H = If(denom = 0, 0, [Office Equipment kWh] / denom);
    var number I = If(denom = 0, 0, [Computer kWh] / denom);
    var number J = If(denom = 0, 0, [Miscellaneous kWh] / denom);
    
    var text data =
      ToText($A) & "," &
      ToText($B) & "," &
      ToText($C) & "," &
      ToText($D) & "," &
      ToText($E) & "," &
      ToText($F) & "," &
      ToText($G) & "," &
      ToText($H) & "," &
      ToText($I) & "," &
      ToText($J);
    
    var text colors =
      "\"" & $ACOL & "\"," &
      "\"" & $BCOL & "\"," &
      "\"" & $CCOL & "\"," &
      "\"" & $DCOL & "\"," &
      "\"" & $ECOL & "\"," &
      "\"" & $FCOL & "\"," &
      "\"" & $GCOL & "\"," &
      "\"" & $HCOL & "\"," &
      "\"" & $ICOL & "\"," &
      "\"" & $JCOL & "\"";
    
    var text chartConfig =
      URLEncode(
        "{\"type\":\"pie\"," &
        "\"options\":{\"legend\":{\"display\":false}," &
        "\"plugins\":{\"legend\":{\"display\":false},\"datalabels\":{\"display\":false}},\"responsive\":false}," &
        "\"data\":{" &
          "\"labels\":[]," &
          "\"datasets\":[{" &
            "\"data\":[" & $data & "]," &
            "\"backgroundColor\":[" & $colors & "]" &
          "}]" &
        "}" &
      "}"
      );
    
    
    var text chartURL =
      "https://quickchart.io/chart?width=" & $SZ & "&height=" & $SZ & "&c=" & $chartConfig;
    
    
    var text pieChartHTML =
      "<div style='display:inline-block; vertical-align:top; margin-right: 30px;'>" &
      "<img src='" & $chartURL & "' width='" & $SZ & "' height='" & $SZ & "' style='border-radius:50%;'/>" &
      "</div>";
    
    var text keyHTML =
      "<div style='display:inline-block; vertical-align:top;'>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $ACOL & ";margin-right:5px;'></span>" & $ALABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $BCOL & ";margin-right:5px;'></span>" & $BLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $CCOL & ";margin-right:5px;'></span>" & $CLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $DCOL & ";margin-right:5px;'></span>" & $DLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $ECOL & ";margin-right:5px;'></span>" & $ELABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $FCOL & ";margin-right:5px;'></span>" & $FLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $GCOL & ";margin-right:5px;'></span>" & $GLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $HCOL & ";margin-right:5px;'></span>" & $HLABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $ICOL & ";margin-right:5px;'></span>" & $ILABEL & "</div>" &
      "<div><span style='display:inline-block;width:10px;height:10px;background-color:" & $JCOL & ";margin-right:5px;'></span>" & $JLABEL & "</div>" &
      "</div>";
    
    $pieChartHTML & $keyHTML

    I tested it in my account with static values and it produces a pie, and the pie is displayed in a generated document (PDF).

    I think the problem with your formula is this CSS:

    > background: conic-gradient(...)

    Is not supported by the PDF rendering engine.

    What you can do instead is generate an image using some third party tool and instead embed that image in the document. This is what I did in my formula above, I'm using the third-party service Quickcharts to produce an image for the chart.

    • LijaHarris's avatar
      LijaHarris
      Qrew Cadet

      My sincerest apologies for applying so late.  Thank you so much for your advice.  The first error I ran into was the case formula:

      However, I remedied this by using the field instead.  However, now the pie chart is coming out black :(: 

      The good news is that it IS shwowing on the document template so yay! But yea, any idea why it is black for the pie chart?

      • Denin's avatar
        Denin
        Qrew Captain

        Can you post the formula you have?