Formulas

You may wish to add your own formulas to a report. For example, suppose you pay commissions of 5% of the sales amount. You could output a report showing sales amounts to a Microsoft Excel document and then add a formula that calculates 5% of the amount, but it would be easier and more convenient to do it in Stonefield Query. You can do that by defining your own formula.

There are two types of formulas: normal and grouping. A normal formula is one that calculates a value by evaluating an expression on each record. For example, a formula that calculates a 5% commission would have an expression something like "Amount * 0.05" and it would calculate that value for every record in the result set for a report.

A grouping formula is more complicated. It calculates a value but it goes through a set of records, often from a different table than the formula goes in, matching a filter and performing a summarization. For example, suppose you want columns in a report that show the sales for last year and the current year-to-date for each customer. Those aren't single values retrieved from the database. Instead, for the first column, use a grouping formula that sums up the sales records for each customer for last year, and use a similar one but for the current year for the second column.

To create your own formulas, click the Formula button in Step 2 of the report wizards or choose Formulas from the Tools menu and click Add in the Formulas dialog to bring up the Formula Editor.