Home > SI 2016 Documentation > User Guide > Reports > Report Designers > Custom Report Examples > Adding Calculated Fields

Adding Calculated Fields

Table of contents

Details

 

This post will demonstrate how you can add calculated fields to a custom report in SI 2016.

Example

 

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.

 

  1. Open the Standard Report Designer. Click Start->Reports->Standard Report Designer:

 

 

  1. Click the [New] button:

 

 

  1. The New Report Wizard (NPR) will open. Click [Next]:

 

 

  1.  Select the Proposal With Images report and click [Next]:

 

 

  1. Name your report and then click [Next] through all of the rest of the steps, then click [Finish] on the last step of the NPR:

 

 

  1. When the Report opens, scroll down to the bottom of the report. Drag a Label from the ToolBox and place where you want it on the report. In this example we placed the label after the Project Summary (which is a subreport) and before the signature lines:

 

 

Type your label text and format the field however you desire:

 

formatted label.png

 

  1. Drag over a Text Box to be used for the calculated value. Note what section you add the field to. In this example it has been added ot the "grpSummaryDetail" section:

 

drag text box.png

 

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":

 

formatted text box.png

 

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.

 

  1. Drag over the dtr:CustomProperties/dtr:CP1/dtr:Value field to the body of the report:

 

 

 

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.

 

  1. Select the field and make a note of the Name of the field and of the section the field is in. In this example the name of the field is "TextBox1" and the section is "grpSubTotal". 

 

subtotal box.png

 

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

 

  1. Click the Script tab at the bottom of the Report Designer, scroll down to the bottom and add the following script:

 

 

script added.png

 

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

 

  1. Publish the report.

 

 

Run the report against a Project where you have entered a value for the percentage desired in the Custom Field 1:

 

project custom field.png

 

Output below:

 

results.png

Download

 

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:

 

report utilities chm file.png

Last modified

Tags

This page has no custom tags.

Classifications

This page has no classifications.