How to Create Excel 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 SQL Server Reporting Services 2005.

1. Open Microsoft Excel.
   
   
2. Click OfficeWriter Designer's Add Query button.
   
   
3. Follow steps 2-11 of Create a Database Query in Create Your First Excel Report.
   
   
4. In the Add Tables dialog box, find the Sales.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. Click Insert Formula and select Build New Formula.
   
   
6. Expand the Globals list.
   
   
7. Select Report Server URL from the list.
   
   
8. Click Insert and Ok.
   
   
9. Repeat the steps above until you have added all six global variables. When you select Insert Formula, you should see:
   
   
   
   
10. In cell A1, type Execution Time.
    
    
11. Place your cursor in cell B1, click Insert Formula, and select =Globals!ExecutionTime. The formula will be inserted in cell B1.
    
    
12. Type labels for each of the other variables (as in step 10) and insert each formula after its label.
    
    
    
    
13. Click Select Query on the OfficeWriter toolbar and select the query created above.
    
    
14. In cell A8, type Sales Person ID.
    
    
15. In cell B8, type Sales Amount.
    
    
16. Place your cursor in cell A9, select Insert Field from the OfficeWriter toolbar and choose SalesPersonID.
    
    
17. Place your cursor in cell B9, select Insert Field from the OfficeWriter toolbar and choose SalesAmount.
    
    
18. In cell A10, type Total.
    
    
    
    
19. Click Insert Formula on the OfficeWriter toolbar and select Build New Formula.
    
    
20. Click the + to expand the Formulas list.
    
    
21. Scroll down, select SUM, and click insert.
    
    
    
    
22. Click the + to expand the DataSets list.
    
    
23. Find your query and click its + to open it.
    
    
    
    
24. In the Expression list on the right, highlight everything inside the SUM parentheses, as shown.
    
    
    
    
25. Click SalesAmount and Insert. Your formula should now look like this:
    
    
    
    
26. Click Ok to return to your worksheet.
    
    
27. Place your cursor in cell B10, select Insert Formula from the toolbar and choose your new formula.
    
    
    
    
28. Publish the report.
    
    
29. 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.