OfficeWriter Enterprise Edition allows you to create custom formulas. The formulas can be based on query results, report parameters, or they may access a set of global variables that Reporting Services contains.
Reporting Services Global Variables
Reporting Services contains global variables that you can access if you have OfficeWriter Enterprise Edition. To access the global variables:
- On the OfficeWriter toolbar, click Formulas and select Add Formula.
- In the Insert Formula dialog, click the + next to Globals to open the list of Reporting Services global variables.
You will see six global variables:
The report's execution time
Report Server URL
The URL of the server running the report
The folder on the Report Server containing the report
The Name of the report
The ID of the user logged on
The report language
- Select Execution Time from the list.
- Click Insert and Ok.
- Click Formulas and select Add Formula.
- Expand the Globals list.
- Select Report Server URL from the list.
- Click Insert and Ok.
- Repeat the steps above until you have added all six global variables. When you select Formulas, you should see:
Parameters in Reporting Services Formulas
You can also use this approach to build formulas that use the parameter values. These formulas can be inserted into the report to display the parameter values selected when running the report.
- Select Formulas > Add Formula
- Expand the Parameters section
- Select a parameter and click the Insert button
- Click OK.
Building Formulas from DataSets
This section will cover how to create a formula using data from a data set. This particular example will use the SUM function:
- Click Formulas on the OfficeWriter toolbar and select Add Formula.
- Click the + to expand the Formulas list.
- Scroll down, select SUM, and click insert.
- Click the + to expand the DataSets list.
- Find your query and click its + to open it.
- In the Expression list on the right, highlight everything inside the SUM parentheses, as shown.
- Click SalesAmount and Insert. Your formula should now look like this:
- Click Ok to return to your worksheet.
Inserting Formulas into an Excel Report
To insert a formula into a cell in an Excel report:
- Place your cursor in the desired cell
- Go to Formulas in the OfficeWriter Designer toolbar
- Under the Insert Formula section, select the formula to insert into cell
Below is an example of a template that has formulas for all the global variables and a formula made from a data set:
Here is what that example template looks like when it's been populated with data:
Officewriter Designer gives you the ability to manage and maintain formulas you've built. The Formula Manager allows you to change, delete, and build new formulas. To start the Formula Manager, click the Formulas button on the OfficeWriter toolbar and select Edit Formulas.
To build a new formula:
- Click the Add New... button. This will bring you to the Formula Builder screen described in the previous section.
- Follow the directions above to create and use a new formula.
To change an existing formula:
- Highlight the formula you want to change.
- Click the Edit button. This brings you to the Formulas Builder screen described above.
- Make the changes to the formula.
- Click Ok to save the updated formula.
To delete a formula:
- Highlight the formula you want to delete.
- Click the Delete button.
- Click Yes to confirm deletion.
- Click Close to return to the report document.
- Important: You must manually review your report document and remove any reference to the deleted formula.
To rename a formula:
- Highlight the formula you want to rename.
- Click Rename.
- On the Formula Name screen, type in a new name and click Ok.