Adding # of Nights to the On Site tab view

We've had an example before of using the Queries customizations to add a simple field to one of the tab view reports.  This example is very similar, but goes one step further.  rather than simply selecting a field, an Expression is used because a data field doesn't exist for the information we want.

In this case we want to show the number of nights in their stay, as a new column on the On Site tab.  Since this is always just the difference between the first night and last night, there's no need for the database to keep this as  a redundant data value.  Luckily it's a commonly needed value so a function is available to get it easily.

1. In the On Site tab view, click the Options button.

2. Make sure the selection for "Query to add with additional info" is blank (no query selected), and click Edit Query.  Click Yes on the prompt to enter Query Setup.

3. Click Add Query.

4. Click the "List Query" button.

5. Enter a query name, such as "Nights Stayed".

6. Make sure the Base Table selected is Reservations.

7. Click Add Column.

8. In the Field/Expression area, type this expression:

 ResvNights(ThisResv())

As with any expression, all characters are critical -- do not add any spaces, make sure the correct punctuation & parenthesis are used, and capitalize the specific letters shown.

9. Enter a column heading, e.g. "Nights".

10. Check the box "Show group totals", with "Sum" selected next to it.  This is optional, but it will let you see a total of the nights stayed for all reservations shown.

11. Change the Align Text option to "Right".

12. Click Save, then Save again, and then Close to get back to the On Site Report Options.

13. Now select this query you just added in the list for "Query to add with additional info", and click Save.

Now your new "Nights" column will be shown after all of the normal columns (selected in the Options).  As with any tab view add-on query, you could add as many columns to the query as you want, and they will all be added to the right of the normally selected columns.