Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

Mini Pie Charts for Table Reports in Quickbase

formulas html reports simple solutions Mar 13, 2024

Data visualization is kind of a big deal, which is why Quickbase dashboards are so useful!

However, the most commonly used area of Quickbase is table reports.

So, how do you have your cake and eat it too? 🤔

Or rather, how can you visualize your table reports?

With Rich Text Formulas and little HTML/CSS, that's how!

If you're not familiar with HTML & CSS that's okay! It's the code the provides the structure (HTML) and the style (CSS) to webpages, including those used for Quickbase.

In the image below, just glancing at the numbers, you probably can't tell the ratio of sales to the total.

But if you look at the Mini Pie you can instantly see the ratios!

I'll show you how to create these amazing mini pies.

I mean who does't love pie 🥧😋!

Mini Pie Chart in Quickbase Table Report

The Setup

For this example let's imagine a Toy Manufacturing company with 3 product lines:

🧸 Stuffed Toys

🥁 Plastic Toys

🚜 Motorized Toys

In any single purchase, items from one or more of these product lines may be included.

The table setup is Purchases (parent) < Line item (child). Where each line item indicates the product line it belongs to, the total for that line item, and other details. The line items are then summarized up to the parent Purchase record as a single "Total Sales" field and 3 product line fields: "Stuffed Sales", "Plastic Sales", and "Motorized Sales".

Implementing this for your company the setup will likely be different, but the idea is the same. You would have 2 or more numeric fields (probably less than 8) that belong on the same record and are part of a larger total.

Next, to visualize the ratio of Stuffed, Plastic, and Motorized sales by purchase we'll add the Rich Text Formula field.

The Rich Text Formula

If you haven't used much HTML in Rich Text formulas, this mini pie chart formula can be overwhelming... which is why I've included it with only your numeric field references needing to be updated.

In simple terms, we use HTML & CSS to create a block with a background which is colored using a "conic-gradient" (or color sections that pivot around the center). Each section is determined by a turn ratio starting a 0 and ending at 1. So the first "turn" of color may go from 0 to .3, and the next from .3 to .8, and the last from .8 to 1. In this formula, the turns start at the top of the pie and go clockwise. ⏰

The 4 field references in green below (variables B & C) should be replaced with your fields. Note that the first total, in the example "Stuffed Sales" is skipped. This is because it is not needed. It will always be equal to the remaining portion of the pie.

Example:

Mini Pie Example

Rich Text Formula (Mini Pie):

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

//include a space after each color
var text ACOL = "LightSkyBlue "; //Stuffed
var text BCOL = "Gold "; //Plastic
var text CCOL = "Hotpink "; //Motorized

//Skip first total
var number B = [Plastic Sales]/[Total Sales];
var number C = [Motorized Sales]/[Total Sales];

//Displays values from top clockwise
"<span style='display:inline-block; width:" & $SZ & "px; height:" & $SZ & "px;" &
 "background: conic-gradient(" &
 $CCOL & "0turn " & $C & "turn, " & 
 $BCOL & $C & "turn " & ($C+$B) & "turn, " &
 $ACOL & ($C+$B) & "turn 1turn) !Important;" &
 "border-radius: 50%'>" &
"</span> "

That's it! You've added mini pie charts to your table reports. 🤩

Now that you've been able to put HTML/CSS into action using Quickbase's rich text formulas, why not learn about all the ways you can enrich your apps with Rich Text Formulas?

Quick Base Junkie has courses & tools that will supercharge your journey to Quickbase mastery:

HTML Formula Crash Course: The missing piece for optimizing the display of information, driving focus for action, and creating visually pleasing user experiences! This course takes the complex topic of HTML & CSS and makes it approachable for any Quickbase builder looking to take their apps to the next level.
Super Fast HTML Formula Converter Tool: A must-have for anyone using a lot of HTML in Quickbase. This tool instantly converts any block of HTML text into a Rich Text Formula friendly text string. Don't believe me? Watch the demo

Visit Quick Base Junkie Training to learn more and start today!

Mini Pie Chart Variations

In the event some adjustments are needed to optimize your Mini Pie Charts for your use case, here is some additional information to get you started.

Change the pie colors

To change the colors, you can replace the use of "LightSkyBlue", "Gold", etc with another named color or with a color Hex Code. If you are using a Hex Code, just be sure to include the # sign before the 6 digit code. Such as var text ACOL = "#FFA6B6 "; //Pink. And be sure to leave a space after the color name or code.

Include a key

It can be helpful to include a key for the colors. The simplest method is to use the report description and make sure it set to display in the report's settings.

Another option is to add a hover title that will be shown when you hover the mouse over the pie. Replace the second to last line with this formula, updated for your labels & colors:

Hover Key Example:

Pie with title for key

"border-radius: 50%' title='Stuffed (Blue)\nPlastic (Yellow)\nMotorized (Pink)'>" &

The use of "\n" creates a line break so each item is on its own line.

Add a second pie

To display two, or more, mini pie charts side by side in the same field, simply repeat the formula (with the exception of the first SZ variable) with the second set of variables, colors, and values. To join the two formulas you'll need to include a "&" between them after the last "</span>".

Center the pie

If your pie appears off center, use the column properties menu to change the alignment. The menu can be accessed from the column header's 3 dots when hovering over it. Note, this option is not available for report formulas or from the table's home page report.

Quickbase column alignment properties

Change the direction

Depending on the order you want to display the values/colors in you may want to show them in reverse. To do that we add in variable "A" and swap out the us of "$C" for "$A".

Example:

Mini Pie Reverse Example

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

//include a space after each color
var text ACOL = "LightSkyBlue "; //Stuffed
var text BCOL = "Gold "; //Plastic
var text CCOL = "Hotpink "; //Motorized

//Skip last total
var number A = [Stuffed Sales]/[Total Sales];
var number B = [Plastic Sales]/[Total Sales];

//Displays values from top counter-clockwise
"<span style='display:inline-block; width:" & $SZ & "px; height:" & $SZ & "px;" &
 "background: conic-gradient(" &
 $ACOL & "0turn " & $A & "turn, " & 
 $BCOL & $A & "turn " & ($A+$B) & "turn, " &
 $CCOL & ($A+$B) & "turn 1turn) !Important;" &
 "border-radius: 50%'>" &
"</span> "

Add more pie slices

If you need more pie slices, add the additional colors and values. Then build out the conic-gradient turns staring at 0 working through each value, adding the prior one to the next in reverse order until you get to 1.

5 Slice Example:

Mini Pie 5 Slice Example

var text SZ = "50";

//include a space after each color
var text DCOL = "MediumOrchid "; //Classic Cars
var text ECOL = "CornflowerBlue "; //Motorcycles
var text FCOL = "Lime "; //Trains
var text GCOL = "yellow "; //Trucks & Buses
var text HCOL = "Coral "; //Vintage Cars

//Skip first metric
var number E = [Motorcycle Sales]/[Total Sales];
var number F = [Train Sales]/[Total Sales];
var number G = [Truck and Bus Sales]/[Total Sales];
var number H = [Vintage Car Sales]/[Total Sales];

//Displays values from top clockwise
"<span style='display:inline-block; width:" & $SZ & "px; height:" & $SZ & "px;" &
 "background: conic-gradient(" &
 $HCOL & " 0turn " & $H & "turn, " &
 $GCOL & $H & "turn " & ($H+$G) & "turn, " &
 $FCOL & ($H+$G) & "turn " & ($H+$G+$F) & "turn, " &
 $ECOL & ($H+$G+$F) & "turn " & ($H+$G+$F+$E) & "turn, " &
 $DCOL & ($H+$G+$F+$E) & "turn 1turn) !Important;" &
 "border-radius: 50%'>" &
"</span>"

Additional Notes & Cautions

Mini Charts aren't just for Table Reports, they look great on Kanban Reports, and on forms too!

🚧 However, there are a few areas where using Rich Text can be problematic.

#1 when exporting data from a table

Sadly these mini pie charts will not display in exported data. What you will get is the underlying HTML/CSS and not the pretty charts.

#2 when emailing the report or form

In many cases simple HTML/CSS will look great when it is emailed from Quickbase as either a field on a report or a form. HOWEVER, in this case, the "conic-gradient" property that is used to create the pie effect does not render in email. So I would not advise using these if they need to be displayed in an email.