Can’t find a certain field in your database to use in your report? Maybe the result comes from a calculation between two or more fields in your data source. If you’re not familiar with SQL you can use a Microsoft® Excel® formula to add a calculated field to your source data.
In this example we will add a Gross Profit field to a Sales Details container using an Excel formula. The formula uses the TotalCost and TotalSale fields from the container. We will also show you how to include this field in your report.
Keep in mind that when adding calculated fields to a container, it’s a good idea to identify the fields used in your calculation beforehand and make sure they exist in your container, or add them if need be.
- In the Connector module select the Sales Details container.
- Right-click on the container and select Add Expressions from the context menu.
- Select Excel Formula then click on OK.
- Enter the name of GrossProfit in the name dialogue box that appears.
- Enter the Excel formula of TotalSale – TotalCost in the formula dialogue box to calculate Gross Profit. Then click on OK. This formula subtracts the values in TotalCost field from the values TotalSale field.You probably know that when entering formulas in Excel you start with a minus sign. However in the connector you need to leave the minus sign out. Also, if there are any spaces in the names of the fields you are using, you need to replace them with an underscore, for example, Total Cost would be Total_Cost.
You have now succeeded in adding the GrossProfit field to the Sales Details container. You will see the expression included in the list of expressions for the container and can see the formula in the Expression Source field of the expression’s properties. You can also identify that this is an Excel Formula by the green icon next to the expression’s name.
- Now, in the Report Manager, select the report that you would like to use the calculation in. This report must be using the container that you added the Excel formula to and must include the TotalSale and TotalCost columns. This is because the formula is only calculated once the report is run out into Excel.
- Click on the Columns tab. All the columns included in the report will be listed.
- Click on Add to the right of the window and select the GrossProfit field from the fields list that appears. Then click on OK.
The GrossProfit field will be added as one of the Columns in your report. When you run out the report now you will see the values for Gross Profit included in your source data.
