This article explains how to add calculated fields to a custom report in SI.
In this example, we will add 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.
1/ Open the Standard Report Designer. Click Start > Reports > Standard Report Designer.
2/ Click the New button.
The New Report Wizard will open.
3/ Click Next.
4/ Select the Proposal With Images report and click Next.
5/ Name your report, click Next through all the steps, and Finish on the last step.
6/ When the report opens, scroll down to the bottom of the report.
7/ Drag a label from the ToolBox and place it where you want it on the report.
In this example, we placed the label after the Project Summary (a subreport) and before the signature lines.
8/ Type your label text and format the field however you desire.
9/ Drag over a Text Box to be used for the calculated value.
Note which section you add the field to. In this example, it has been added to the "grpSummaryDetail" section.
10/ Name and format the Text Box.
In this example, we named the text box "txtMonthly", chose the right alignment, set the OutputFormat to "c" (for currency), and changed the SummaryType to "GrandTotal."
So now we have added the two fields that will be displayed on the report.
Next, we will add a field that will not be displayed 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 for the calculation.
11/ Drag over the dtr:CustomProperties/dtr:CP1/dtr:Value field to the body of the report.
12/ Name and format the Text Box. Here, we have given it a color so it is visible in the Report Designer, shrunk down the size, and set the Visible property to "False" so 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 the 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 the equipment in the project.
13/ Select the field and make a note of the name of the field and the section the field is in.
In this example, the name of the field is "TextBox1" and the section is "grpSubTotal."
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.
The formula will look like this: Total Price of Equipment * Project Custom Field 1 * / 100 / 12. The 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 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.
14/ Click the Script tab at the bottom of the Report Designer, scroll down to the bottom, and add the following script.
The script below is 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 and paste the script from there. Report utilities 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
15/ Publish the report.
16/ Run the report against a project where you have entered a value for the percentage desired in Custom Field 1.
The following is the output.
You can download and import the sample project built during this example: Proposal With Images w Monthly Maintenance - DToolsReports.dtr.
You can also download the help file reportutilitiessi15.zip, which lists out all of our available Report utilities. You may need to "unblock" this ZIP file before unzipping.
Once downloaded, right-click the file, select Properties, and click the Unblock button. Then, unzip the file.
This is a .chm help file.