This post will demonstrate how you can add calculated fields to a custom report in SI 2017.
In this example we will be adding a calculated field to a Proposal With Images report to display a "monthly maintenance fee". This fee will be a percentage based on the Equipment total and we will use Project Custom Field 1 to make the percentage variable.
Type your label text and format the field however you desire:
Name and format the Text Box. In this example we named the text box "txtMonthly", chose right alignment, set the OutputFormat to be "c" (for currency), and change the SummaryType to "GrandTotal":
So now we have added the two fields that will display on the report. Next we will add a field to the report that will be not display on the report but that we need for our calculation. In this example we will use Project Custom Field 1 to allow the user to enter a percentage to be used in the calculation.
Name and format the Text Box. Here we have given it a color so that it is visible here in the Report Designer, shrunk down the size, and set the Visible property to be "False" so that it won't display on the generated report.
Now we are just about ready to add some script to generate the "monthly fee" but first we have to get the name of field whose value we want to use in our calculation. In this example, we will use the "Project Subtotal" value since that will be the value of all of the Equipment in the Project.
Ok, now comes scripting. We will need to pull some values from fields on the report, build a formula, and return the result of the formula to the report. Formula will look something like this: Total Price of Equipment * Project Custom Field 1 * / 100 / 12. Formula is broken down below:
Total Price of Equipment - the value of TextBox1 in the grpSubTotal section
Project Custom Field 1 - the value entered in the Custom Field, entered as a whole number, e.g. 8% would be 8, not .08
100 - dividing by 100 to convert the Project Custom Field 1 value to a decimal. Yes, you could just have your users enter the value as a decimal...but I didn't do that.
12 - dividing by 12 because this is a monthly fee
Result - the calculated result needs to be returned to txtMonthly in the grpSummaryDetail section
Note: script below for copying/pasting. Of course you can modify the names of the text boxes and report sections based on your custom report. You can also download this sample report further down in this post and copy/paste the script from there. Report Utilites are used in this script. For a complete list of Report Utilities check out the Download section at the bottom of this page.
Sub grpSummaryDetail_BeforePrint
'CALCULATED FIELD SCRIPT
dim eqtotal as Double = ReportUtilities.ReturnTextBoxValue(rpt, "grpSubTotal", "TextBox1")
dim percentage As Double
Double.TryParse(ReportUtilities.ReturnTextBoxValue(rpt, "grpSummaryDetail", "txtCP1"),percentage)
dim result as Double = eqtotal * percentage / 100 / 12
ReportUtilities.SetTextBoxValue(rpt, "grpSummaryDetail", "txtMonthly", result)
End Sub
Run the report against a Project where you have entered a value for the percentage desired in the Custom Field 1:
Output below:
You can download and import the sample project that was built during this example.
You can also download a help file that lists out all of our available Report Utilities. This is a zip file that you may need to "unblock" before unzipping. Once downloaded, right-click the file and select Properties, then click the [Unblock] button. Then uzip the file.
This is a .chm help file: