Crow Canyon Systems,
Inc.
support@crowcanyon.com
REPORTING WITH CCS SERVICE REQUEST
Report Builder
To use Report Builder:
In Report Builder, choose which fields to
export by individually checking them or by checking “Select all fields”. Report
Builder is set to export from the Assigned Tickets folder by default; the
“Change Folder” button can be used to change to another folder.
Also, rather than export all tickets, Optional
Filters can be used to filter on Status, Priority, Date, and/or Specific Fields.
These filtering options allow tailoring of the exports to meet specific needs.
With the fields selected and filters in place,
Report Builder will output the ticket data to Excel, Email, or a file, based on
which of the “Export to:” buttons is pushed. This will read each ticket that
meets your criteria into an Excel spreadsheet, an Email, or a file of the type
specified in the file extension.
If exporting to Excel, Microsoft Excel needs
to be on the PC that is running Report Builder. Excel does not need to be
opened, but it does need to be installed.
Report Builder will not delete any data or
information from the tickets during and after the export.
If you want to create a new Report and save
it with the fields and filter choices you have selected, go to the Actions menu
and choose "New Report Builder". When this comes up, make your field
and filter selections, then give the Report a name. To save it, go to File/Save
to save it, then File/Move to Folder and choose the Reports folder.
The Export Utility exports the Assigned and
Completed tickets to a database.
Database: The ticket data is
exported to a database. We supply a Microsoft Access MDB file that can be used
as is (Access 2000 or 2003). This can go on any accessible drive, whether local
or a network share. The database consists of two tables: “Tickets”, which holds
the ticket data, and “ExportLastRun”, a one record/one field table that records
the time and date of the export for future reference. The database can also be
imported into a SQL database to create a table in SQL for the export.
The MDB file is in
the Reports public folder when you first download the program. It is named
.MD_. It needs to be moved to a file directory (local directory or a network
share) and renamed to .MDB before you can use it in the export.
Data Source Name (DSN): The Export Utility
uses a DSN to determine where the database is and what driver to use. This
provides the flexibility to use any ODBC-compliant database, as long as the
database has right table structure and the DSN points to it. The DSN is setup on
the PC where the export will be run. The Export Utility has a place to enter in
the DSN.
A
DSN is setup in Control Panel/Administrative Tools/Data Sources (ODBC). The DSN
can be File, User, or System. The driver is Microsoft Access Driver (if using
Access) or SQL Server (if using SQL) or whatever is appropriate, if using a
different database.
How it
works:
1.
The
Export Utility looks up the date and time of the last export in the database.
This is in the one record with one field named “Export Last Run” in the
“ExportLastRun” table. This is a date/time field. It should be populated with
the date of the last export. If not, then put in an appropriate date/time
there. If this is the first time the tickets have been exported, then put in
the date manually as something prior to the start of the tickets, such as Jan.
1, 2001.
2.
Once
it has retrieved the “Export Last Run” date/time from the database, the utility
then goes through the list of tickets in the selected folder. This must be
either the Assigned Tickets or Completed Tickets folders. If a ticket’s last
modified date is more recent than the “Export Last Run”, it marks it for
export. If not, it skips over it (because it has not changed since the last
export, so its data should already in the database.)
3.
For
the tickets marked for export, it looks up the ticket in the database by Case
ID. If the Ticket is NOT in the DB, it puts it there. If the Ticket is already
in the DB, it updates it.
Notes:
·
The
tickets are not deleted, moved or modified during the export process. The
tickets stay in the public folder in Exchange and only the data is copied out
to the database.
·
The
first export may be lengthy, since it needs to populate the DB and exports all
the tickets. On subsequent exports, it should run much faster since it only
exports modified tickets.
·
If
the Ticket is in the database but not in the folder, no action is taken. In
other words, the Export Utility does not delete any records from the database.
Running
Reports and Queries
Once the data is in the database, the database
tools can be used to build and run reports. Microsoft Access has extensive reporting
capabilities. SQL Server provides SQL Reporting Services for reporting.