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 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:
Execution Time The report's execution time Report Server URL The URL of the server running the report Report Folder The folder on the Report Server containing the report Report Name The Name of the report User ID The ID of the user logged on Language 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:
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.
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.
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.
- 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.
- 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.
- 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.
- Highlight the formula you want to rename.
- Click Rename.
- On the Formula Name screen, type in a new name and click Ok.
The formula name is only used to help you maintain formulas. It is not used anywhere in your report document.