How to Create Word Reports > Adding Formulas

Adding Formulas

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. We will explore formulas by building a simple report and then adding formulas. Please read the Quick Start how to add queries and fields to reports if you do not already know how to do this.

This sample is based on the AdventureWorks sample database that is shipped with Reporting Services.

1. Open Microsoft Word and create a new document.
   
   
2. Click OfficeWriter Designer's Add Query button.
   
   
3. Follow steps 2-12 of Create a Database Query in Create Your First Word Report.
   
   
4. In the Add Tables dialog box, find the SalesOrderHeader table in the list and select Add. Click Close.
   
   
5. Click the SQL button on the Microsoft Query toolbar, and enter the following query:
   
   
   
   
6. Click Ok.
   
   
7. From the File menu, select Return to OfficeWriter Designer.
   
   

Reporting Services Global Variables

Reporting Services contains global variables that you can access if you have OfficeWriter Enterprise Edition. To access the global variables:

1. On the OfficeWriter toolbar, click Insert Formula and select Build New Formula.
   
   
2. 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

   
   
   
3. Select Execution Time from the list.
   
   
4. Click Insert and Ok.
   
   
5. At the top of your document, type Execution Time: followed by a space.
   
   
6. Click Insert Formula and select =Globals!ExecutionTime. The formula will be inserted in the document.
   
   
7. Press Enter a few times to put some space between the formula and what we will do next.
   
   
8. Click Select Query on the OfficeWriter toolbar and select the query created above.
   
   
9. Insert a two-column, three-row table in the page.
   
   
10. In the left column's first row, type Sales Person ID.
    
    
11. In the right column's first row, type Sales Amount.
    
    
12. Place your cursor in the second row of the left column.
    
    
13. Select Insert Merge Field from the OfficeWriter toolbar and choose SalesPersonID.
    
    
14. Place your cursor in the second row of the right column.
    
    
15. Select Insert Merge Field from the OfficeWriter toolbar and choose SalesAmount.
    
    
16. In the third row of the left column, type Total.
    
    
    
    
17. Click Insert Formula on the OfficeWriter toolbar and select Build New Formula.
    
    
18. Click the + to expand the Formulas list.
    
    
19. Scroll down, select SUM, and click insert.
    
    
    
    
20. Click the + to expand the DataSets list.
    
    
21. Find your query and click its + to open it.
    
    
    
    
22. In the Expression list on the right, highlight everything inside the SUM parentheses, as shown.
    
    
    
    
23. Click SalesAmount and Insert. Your formula should now look like this:
    
    
    
    
24. Click Ok to return to your document.
    
    
25. Place your cursor in the third row of the right column of your table.
    
    
26. Select Insert Formula from the toolbar and choose your new formula.
    
    
    
    
27. Click Select Query on the OfficeWriter toolbar and select your query.
    
    
28. Highlight the second row of the table.
    
    
    
    
29. Click Query Range on the OfficeWriter toolbar and make sure Selection is chosen. Click Ok.
    
    
30. Publish the report.
    
    
31. Click View on the OfficeWriter toolbar. Your report should look like this:
    
    

Managing Formulas

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 Insert Formula button on the OfficeWriter toolbar and select Manage Formulas.

To build a new formula from the Formula Manager screen:

1. Click the Add New... button. This will bring you to the Formula Builder screen described in the previous section.
2. Follow the directions above to create and use a new formula.

To change an existing formula:

1. Highlight the formula you want to change.
2. Click the Edit button. This brings you to the Formulas Builder screen described above.
3. Make the changes to the formula.
4. Click Ok to save the updated formula.

To delete a formula:

1. Highlight the formula you want to delete.
2. Click the Delete button.
3. Click Yes to confirm deletion.
4. Click Close to return to the report document.
5. Important: You must manually review your report document and remove any reference to the deleted formula.

To rename a formula:

1. Highlight the formula you want to rename.
2. Click Rename.
3. On the Formula Name screen, type in a new name and click Ok.
   
   
   
   
4. Note: The formula name is only used to help you maintain formulas. It is not used anywhere in your report document.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.