Exporting to Excel for Robust Reporting

The Excel export can be used as an alternative to generating reports through the Report Designer, and is available on most tables. Instead of editing a report and choosing the data fields to include, exporting to Excel allows you to easily select the data to export, and allows you to group and sum fields to see totals. Excel gives you even more flexibility and control over how your data is represented. Reports created using this method can be exported for use in other Building Department databases.

The software manual – located through your Help menu (View Documentation>Manual) – provides an instructional topic on how to export to Excel. This blog article will highlight some of the features.

Tables can be filtered prior to running the export, and edited once you’ve launched the export. Filters are addressed in a previous blog article by Adam Bengal (http://bsasoftware.com/Blog/articleType/ArticleView/articleId/720/Help-with-Filters). Your software manual also provides information on filters.

Titles can be saved/edited for the export:

A large variety of fields are available for selection, and may be ordered for export in any manner you wish (there are rules for ordering fields when exporting for charting purposes; this is addressed in the software manual):

If you choose to Group By a particular field, a summary sheet will be provided along with a sheet for each type. For example, if you “Group By” Permit Type, and four permit types are exported, you will have five sheets: one for each type, and a summary of all:

Field names can be changed to appear less cryptic on the Excel spreadsheet:

Totals can be shown (i.e., total number of permits issued or total amount of permit fees collected):

Jeremy Latuszek

Help with Filters

In order to pull specific data out of the Building Department program (tables, reports, Excel spreadsheets, or charts), it is necessary to run a filter.

Creating a filter is the program’s way of sifting through ALL of its records and displaying ONLY the info you need. Think of a filter as a sieve: it lets some information through while blocking out the rest.

Applying a filter has the same look and feel in every area of the program. The first tab shows “common” report filters and gives the most used options for a given table. The second tab shows more advanced filter options, allowing you to pull more detailed information or data from other sources (depending on the nature of the filter).

Edit Filter Condition gives you the ability to change the operator for a given value: Equals will show only records that match exactly; Not Equals will show all records BUT that value; Between allows you to pull a range of information (effective for dates); Starts With will pull only records that begin with the value (very useful when searching by record number); and so on.

On the Advanced Filter Options tab, you can also change the adjoining operators from ALL to ANY to control what the filter must match for more control over the data you receive.

When you have created a filter, you can save it to your database to use again later. The filter can be loaded from anywhere in the program, saving you time and allowing other users to view the same results.

While simple filters are generally easy enough to input and understand, more advanced filters can pose difficulties. If you are filtering data and receiving “No records exist” messages, it is possible that a condition or operator is incorrect. Please feel free to contact tech support for assistance.

Adam Bengal

The Importance of Backing Up Your Files

Like anything stored on a computer, it is important to protect your data.

Your Building Department database should be backed up frequently to protect the information you have entered (we recommend at least once a day). Most units have a network backup system to facilitate this (ask your IT department to see if you can utilize this system).

Having backups of your BD database is an important measure to take in order to avoid issues with the information entered into the program. You will occasionally perform tasks that have the potential to affect large amounts of data (renewing certificates, running billing statements, generating letters en masse, voiding/adjusting invoices and transactions, etc.). In cases such as these, it is extremely beneficial to create a backup before beginning. While some actions can be reversed within the program, others may only be done manually. Some actions – such as those concerning financial records – cannot be reversed at all.

When you create a backup of your database, you are in effect creating a copy of it. If necessary, that backup can be restored, “undoing” any work that was entered in error post-backup. It is very important to understand the implications of this backup/restore process:

  • A backup is made at 10:00 a.m., prior to the task of generating a large amount of letters. This task commences at 10:15. At 10:30, an issue occurs, resulting in the necessity of restoring the backup made at 10:00. Once the backup is restored, it is as if those letters were never generated; that task will need to be started again.
  • Backups and restores affect all users: if Joe’s backup gets restored, Mary’s work is written over and she will need to begin again.
  • IMPORTANT: if payments were posted to GL before a backup was restored over the existing data, those payments remain posted to GL.

Therefore, while creating a backup to keep a saved copy of the database is beneficial, it is not without some risk. It is imperative that users understand how restoring a backed up database impacts the program and other users, and that restores are not to be used lightly, as they can result in loss of data. If you have any questions concerning this process, please contact customer support.

For help on creating or restoring backups, please consult your software manual, located in your program’s Help menu. Do a search for “backup” or “restore”.

Adam Bengal