Jet Excel add-in Keywords
Overview
One of the most powerful aspects of using Jet Reports is that not only are you able to retrieve and analyze your data, but you are also able to format that data in ways that best suit your needs.
The Jet Excel add-in allows you perform such advanced steps as to auto-fit columns, hide rows and columns, allow the user to specify report options, etc.
Many of these capabilities are achieved through the use of Jet Keywords.
Please note that most of these keywords (with the exception of LOOKUP) are also supported by Jet Basics.
These keywords are used in Row 1 and/or Column A - which are reserved in a Jet report for that specific use
Let's take a look at the handful of keywords that provide these advanced features.
Jet Keywords
-
The most fundamental of the Jet Keywords is the AUTO+HIDE that (when the report is run) is automatically placed in cell A1 of any worksheet containing Jet functions. AUTO+HIDE combines the functionality of the hide keyword to hide a row or column with the keyword of AUTO which tell Jet to automatically perform other keyword commands in row 1 and column A. As a designer, you will never need to add the keyword of AUTO.
-
+VALUES
This keyword can be added to the AUTO+HIDE that always resides in the A1 cells for the sheets in any Jet Report. If you would like to design a report that can be available to people who are not licensed Designers, you can convert the workbook to values by placing "Auto+Hide+Values" in cell A1 of any worksheet. When a report containing this keyword is run, all Jet functions will be converted to values while all other Excel formulas will remain intact and the worksheet will be editable. The Jet functions can be restored (by a licensed Designer) by selecting the Design button on the Jet ribbon.
More information on this feature can be found in the article Introducing Report Viewers
-
+LOCK
You can create a locked worksheet by entering "Auto+Hide+Lock" in cell A1. Locking a worksheet prevents non-Designers from accidentally changing the formulas.
When a report with locked sheets is run, any worksheets that include the +Lock keyword in cell A1 will be locked.
More information on this feature can be found in the article Introducing Report Viewers
Sharing Reports with Excel Users Who Do Not Have the Jet Excel add-inUsing either +LOCK or +VALUES allows Jet Viewers to use reports. Both of these features are also useful for sharing your reports with people who use Excel and don't have the Jet Excel add-in. In this situation, there are several advantages to using +VALUES instead of +LOCK. 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 when opening the report. This message can be confusing to the person who receives your report. So it is recommended that you save in report mode with +VALUES to eliminate this potential issue.
-
Some data values, such as customer names, are of variable length. So while you are designing the report, you do not know how wide the column should be. If the column is not wide enough to hold the data, Excel will either display ######## in the cell, or it will only show the section of data that will fit in the cell.
The Jet Excel add-in can automatically format the column width or row height for the widest or tallest piece of data that you want to display. This operation is very similar to the manual process of using Excel's Autofit feature. The whole column will resize to fit the widest cell.
To do this with the Jet Excel add-in, you can put the keyword "Fit" in row 1 of the column. For example, if you have a list of customer names in column C, you can put "Fit" in cell C1 to automatically resize the column for the longest customer name when the report is run. If you want to resize the height on a row, you can put "Fit" in Column A of that row.
-
HIDE / HIDESHEET
Sometimes, it can be very convenient to include information in your report, but not actually display it.
The HIDE keyword can be used to hide an entire column or entire row once the report is run. Likewise, the HIDESHEET keyword can hide an entire worksheet when the report is run.
More information on this feature can be found in the article Automatic Hidden Columns Sheets and Rows
-
HIDE+?
Sometimes you will want some data returned in your report to be hidden - depending on some aspect of that date. For example, maybe when a particular value returned is zero.
This is accomplished through the use of the 'conditional hiding" keyword of HIDE+? in either cell B1 (if you wish to hide certain rows) or cell A2 (if you want to hide certain columns).
More information on this feature can be found in the article Conditionally Hiding Rows, Columns, or Sheets
-
OPTION, TITLE, VALUE, LOOKUP, TOOLTIP, VALID, MESSAGE
All of these keywords are used together to allow you do define "Report Options".
These keywords allow you to create a report options window from which - when the report is run - you can choose specific filters to narrow down the information you want to view in the report.
Detailed instructions for doing this can be found in the following articles:
-
DATA, KEY, KEYNAME
Please note: Stored User Data is a highly advanced featureNormally any changes made to a report while it is in Report mode will be lost when the report is run again.
However, with the stored user data feature, it is possible to designate sections of a report where users may enter and change data which will be stored and kept by the Jet Excel add-in when the report is run.
Detailed instructions for doing this can be found in the following articles:
Related Articles
- Automatic Hidden Columns Sheets and Rows
- Conditionally Hiding Rows, Columns, or Sheets
- How to create a Report Options Windows
- Introducing Report Options with Database Lookup
- Creating Report Options Lookups with Multiple Columns
- Report Options Tooltips
- Report Option Validation
- Introducing Stored User Data in Reports
- Advanced Stored User Data