Prepare Reports for use by Report Viewers

design reports for Viewers

A report Viewer is an Excel user that has the full edition of the Jet Excel add-in installed but is not licensed to design reports.  A report Viewer is allowed to change report options and use a Jet report, but may not use any other Jet features.  A report Viewer may not enter Jet functions into cell formulas or make any changes to the report design.

To design reports that a Viewer can use, the Designer must do one of two things: lock all worksheets that contain Jet formulas or convert the Jet functions to values.

1. Locked Worksheets

   Locking a worksheet prevents Viewers from accidentally changing the formulas.  When you select Report mode in a workbook with locked sheets, Jet saves all formulas to a hidden worksheet and does not recalculate them when the workbook opens.  You create a locked worksheet by entering Auto+Hide+Lock in cell A1. The worksheet will be locked after the report is generated by clicking Run on the Jet ribbon and can be unlocked (by a licensed Designer) with Go to Design

   If you want to create a report that Viewers can use, you should create an Options worksheet that is not locked.  The Options worksheet should be used for all the report options, such as the dates, that might change.  This worksheet cannot contain any Jet formulas.  The actual report should be on other worksheets which must have Auto+Hide+Lock in cell A1.  You must choose Run to lock the formulas and then save the workbook before giving it to Viewers.

2. Viewer Editable Worksheets

   If you would like to design a report that Viewers can use without locking any of the worksheets, you can instead convert the workbook to values. You do this by placing Auto+Hide+Values in cell A1 of any worksheet.

   Upon doing this, all Jet functions will be converted to values while all other Excel formulas will remain intact and the worksheet will be editable (although changes to Excel formulas will be lost when the report is run).  The Jet functions can be restored (by a licensed Designer) by selecting Go to Design

   We designed most of the sample workbooks for use by Viewers so you can see examples of how to do this.

Sharing Reports with Excel Users Who Do Not Have Jet Installed

In the previous two sections we introduced the +Lock and +Values keywords, which allow Jet  Viewers to use your reports.  Both of these features are also useful for allowing people - who have Excel but do *not* have Jet installed - to view reports, but there are several advantages to using +Values

The first advantage is that +Values does not lock the report so the person receiving the report can edit it.  The second advantage is that using +Values eliminates any link to the Jet Excel add-in so Excel will not display a warning message asking the user if they want to update data from external sources upon opening the report.  This message can be confusing to the person who receives your report, so save your reports in report mode with +Values to eliminate this problem.