We see lots of Jet Reports tutorials and instructions state that Jet uses Column A and Row 1 for its own processing needs when running a report. Whilst this is true, most people leave these columns alone and let Jet ‘do its thing’ - but what CAN we use this row and column for? Let’s explore a few options that are available enhance our reports.
Auto+Hide
Jet will put this into cell A1 when the report is first run (putting it back into design mode will now show this in A1), so you don’t need to do this yourself. It is fundamental to the report as it defines that row 1 and column A will become functional to allow specific rows or columns to be hidden when the report runs. The AUTO aspect tells jet to perform all the other keyword commands in row 1 and column A by itself (ie. automatically).
+Values
This tells Jet to convert its functions to values which will allow anyone without a Jet Designer license to view the report properly. 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). When the report is put back into “design mode”, Jet restores its functions.
This keyword can be apended to the end of the AUTO+HIDE keyword in cell A1 in any Jet Report (Auto+Hide+Values).
+Lock
Locking a worksheet prevents viewers from accidentally changing the formulas. When using +Lock, Jet saves all formulas to a hidden worksheet and does not recalculate them when the workbook opens. This is the most restrictive version of a report for a non Jet viewer as it does not allow any editing of the worksheets that are locked. You can allow the user to have some freedom if you use this with Report Options. If you do, you need to ensure that the Options worksheet is not locked and does not contain any Jet functions. It is probably best to use +Values instead, but if you really don’t want people mucking around with your report, this is the one to use.
This keyword can be apended to the end of the AUTO+HIDE keyword in cell A1 in any Jet Report (Auto+Hide+Lock).
Fit
More often than not, some data returned from your ERP will be longer than the cell width. Obviously users can manually resize the columns to fit the data, but there’s no need to do that every time when you can get Jet to do that automatically when the report is run. This is made possible by putting Fit in row 1 in any column that you would like Jet to resize to fit the longest field returned upon report refresh.
This also works for resizing the height of rows. Just put Fit into column A on any row you want automatically adjusted.
Hide
This keyword allows you to automatically hide any row or column upon report refresh. This is useful if you’re using helper columns in your report that other columns are dependant on but you don’t neccesarily need showing. It can also be useful to use this for any column that most viewers do not need, but might be helpful to have in the background just in case.
You can also use +Hidesheet to hide the entire worksheet upon report refresh - this is useful if you have a reference table your report is referencing to such when using a VLOOKUP function.
Hide+?
This is an extension of the Hide keyword above, but this time it acts as a conditional hide where rows or columns can be hidden dependant on the value or output of another cell. For example, if you had a report that returned a list of customers you can use this to hide any customer row where their outstanding balance is zero. This is good if you still would like to have the hidden data within the report.
Below shows how Hide+? is used:
Avoid using conditional hides to eliminate large amounts of data in a spreadsheet if it is definitely not needed. In many cases, if you use an appropriate filter it will eliminate the data you do not want to see. No need to bring in data that’s only going to be hidden as it’s irrelevant, filter it out from the query at the start! For more tips on report performance, check out Jet Reports 101 - Improving Performance
Advanced Keywords
Data, Key, Keyname
When Jet reports run, they empty and remove all data added to the report by a user when it is in report mode. However, this trio of keywords is a powerful tool that allows users to add their own data into a designated are of a report (for example, in a “comments” column) and have it remembered by Jet when the report is re-run.
A blog post is in the making that fully explores how stored user data works in Jet Reports.
Report Options worksheet
There are others that are specific for use with Report Options (blog post on this soon) which are Option, Title, Value, Lookup, Tooltip, Valid, and Message.
❤️ Enjoyed this article?
Forward to a friend and let them know where they can subscribe (hint: it's here).
