ww w. bs as of twar e. co m
Questions? Contact support at (855) 272-7638 or [email protected]
1
Pe
rv
as
iv
e
B
ui
ld
in
g
D
ep
ar
tm
en
t
Creating an Excel Spreadsheet Report
If you track multiple jurisdictions, separate totals may be given for each of them.
Please note: Spreadsheets are limited to tabular formatting. In English, you will not be able to create a report that gives you sub-totals for (as an example) Building, Electrical, Mechanical, and Plumbing permits in the same report. Only grand totals of record count and, if included, fees, etc. are available (subject to records that fall within the specified query.
Spreadsheet reports can be created in the following Report Categories:
Audit Trail Enforcement Licensee Project
Bond Inspection Group Name Property
Certificate Inspections Occupancy Reminder
Contractor Land Permit Violation
This help doc uses a Permit category report as the example, but the methods are the same regardless of report category.
1. Open the Reports menu and select Permit. 2. Click the Insert button.
3. Type a name for your report; this appears in the list of reports in the current report category. Click Continue. 4. Select Create a new report based on an existing report.
5. Select Excel Spreadsheet from the list of reports. 6. Click the Insert button.
7. Select PerAmountGrandTotal from the list.
9. The Title – initially the field name – prints as a column heading in the spreadsheet. This name should represent the type of data appearing in the column.
This example is going to add the PerAmountGrandTotal field, so change the Title to Grand Total.
10. The Column Width field defaults to the field size. Columns can be resized once in the spreadsheet, but to avoid having to resize each time the spreadsheet is created, set this field to a width that matches your data more accurately.
11. The Alignment field defaults to the typical alignment of the field being added. PerAmountGrandTotal is a numeric field, and numeric fields are typically right-aligned.
12. Turn on the Show Totals field if you would like the spreadsheet to total up the data represented by the field you’re adding.
If the field is a “currency”-type field, this will be the sum of all dollar amounts. If the field is text, this will be a record count. The Excel Spreadsheet template for the Permit report category already has the record count set on the PerPermitNumber field.
13. The Commas, Money, and Decimals fields are only enabled for numeric fields. Set these fields as you want.
excelreport.docx 3 16. The columns on the spreadsheet appear left-to-right in the same order as on the Spreadsheet Definition window.
To move a field up or down, click it to highlight it, then click either the Move Up or Move Down button.
17. Continue moving fields until you are satisfied with the order. The order can be changed once in the spreadsheet, but to avoid having to do this each time the spreadsheet is created, set your order here.
To remove a field, click it to highlight it, then click the Delete button.
18. Turn on the Show the filter field if you would like your report filter to appear at the top of the spreadsheet. 19. Turn on the Create one sheet per jurisdiction field if you track multiple jurisdictions and would like a
separate sheet for each jurisdiction. This feature only works if you have set up unit codes (see the help doc on our website entitled County/Multi-Jurisdictional Unit Codes).
21. Click the Save and Close button to return to the Report Setup window.
An icon has appeared next to the Delete button, indicating that this is a spreadsheet report. This icon has no function, it’s just informational.
The Destination field for spreadsheet reports defaults to Excel Spreadsheet, which is the only destination allowed for these types of reports.
The Printer Settings button is disabled; printer settings from the Building Department program cannot be passed to Excel.
The Edit Format button takes you back into the Spreadsheet Definition window.
22. Click the Sort by button to select the initial sort order of the spreadsheet (this can be changed once in the spreadsheet).
23. Click the Report Query Options button and set up your filters.
excelreport.docx 5 Pictured is a spreadsheet for a multi-jurisdiction unit. The first sheet shows totals for each.
Click each subesquent sheet to view unit-specific information. Any properties not assigned to a unit in your database, if any, appear in the “Unknown Unit” tab.
If the Create one sheet per jurisdiction field is left off in the Spreadsheet Definition window, or if you are not set up as a multi-jurisdiction unit, all records print on one sheet.
excelreport.docx 7
CHANGING THE SORT ORDER
These instructions are not intended as a complete demonstration of working in Excel®. Familiarity with
Excel® is recommended if you intend to make use of spreadsheet reports.
1. Click the column heading of the data by which you want to sort.
2. Click the button in the toolbar to sort “ascending” (A to Z; 0 to 100; 1/1/06 to 12/31/06); click the
button to sort “descending” (Z – A; 100 to 0; 12/31/06 to 1/1/06).
PRINTING THE SPREADSHEET
These instructions are not intended as a complete demonstration of working in Excel®. Familiarity with
Excel® is recommended if you intend to make use of spreadsheet reports.
1. While in Excel®, open the File menu and select Print.
2. The Print What setting defaults to Active Sheet. If you want to print all sheets, be sure to click Entire
Workbook.
