ReportBuilder Hacks Using Formulas

Rounding Numbers

Create the report as you want it, and then insert a formula column to do the rounding. When using the the ROUND function, set the Num Format to Cents if you want to see decimal places, or to Negative Sign if you don't want decimal places.

• To round to hundreds:  Round(([reference lines])/100)*100 
• To round to thousands:  Round(([reference lines])/1000)*1000

Rounding Examples:

• Round to Hundreds column:  Round([Col 1 2024: 2024 Budget YTD-24]/100) *100

• Round to Thousands column:  Round([Col 1 2024: 2024 Budget YTD-24]/1000) *1000

• Round to Hundreds (No Cents) column:  Round(([Col 1 2024: 2024 Budget YTD-24])/100)*100

Rounding Percentages

Create the report as you want it, and then insert a formula column to do the rounding.

• To remove decimal places:  Round(([reference lines]) *100)/100

Rounding Example:

• Round([Col 3 Difference ]/[Col 2 Budget YTD-24]*100)/100

Highlighting Rows Above A Certain Threshold

Use Case: A customer wants to see which departments have a variance above $2,000.

Create a column for the variance, and then create a formula column that references the variance column. You can hide the variance column on the report (shown below), so that it doesn't show when viewed. Notice in this example the Style is set to Separator Column to draw attention to the column in the report.

Threshold Example:

• If([Col 3 Difference]>=2000, [Col 3 Difference], 0)