How to create custom reports.

Problem:

How to create custom reports.

Solution:

In order for you to create a custom report you need to purchase full version of Crystal Reports. Having a full version of Crystal Reports will allow you to modify existing reports or create new reports.

Reports are standard Crystal Reports .rpt files, and as such can be modified with Seagate Crystal Reports. When modifying reports, first make a copy of the report and place it in the Custom directory (without changing its name), then make the modifications. If Pharos Reports detects a report in the Custom directory with the same name as one in the Standard directory, the Custom report is used instead of the Standard one. In an upgrade, only reports in the Standard directory will be overwritten; any customizations will not be affected.

The following information is taken from the Pharos TechNote "Pharos Database Structure: Reports Tables":

Pharos Database Structure: Reports Tables

Pharos uses a SQL Server database to hold both configuration information about the system and historical information recording how the system is being used. With the release of Pharos Version 5.0 and the introduction of the new Pharos Reports application, a series of SQL views were created that expose the core tables in the Pharos database. The purpose of this paper is to document the interface to these new SQL views and suggest how clients may use them to generate their own custom reports with Seagate Crystal Reports.

SQL Views

The core table in the Pharos database that records historical information about how the system is being used is the “transactions” table. The information that is recorded in this table varies according to the type of transaction i.e. credit, print, transfer and so forth. For example, a print transaction records the printer where a print job is released, the number of pages/sheets printed and the associated cost of the job. A credit transaction records the Cashier responsible for raising the transaction and the amount of money credited to the user. Some information in the transactions table is common to all transaction types, such as the server where the transaction was raised, the user associated with the transaction and the Pharos bank used for charging.

The SQL views on the transactions table resolve the foreign key relationships for the different transaction types and expose a structure that can be queried in much the same way as queries against ordinary tables. The SQL views created for Pharos Reports V5.0 include, in order of importance:

® rpt_transactions

® rpt_print_transactions

® rpt_credit_transactions

® rpt_copy_transactions

® rpt_printop_transactions

® rpt_transfer_transactions

® rpt_users

® rpt_groups

® rpt_alerts

These views are described later in this document.

Notes

® The SQL views use column names that contain spaces, which means that SQL queries against these views need to “qualify” the column names. For example, the following query that can be run through SQL Query Analyzer uses square brackets [] to identify the column names.

select

[User ID], [Quantity], [Amount Charged]

from rpt_transactions

® Double quotes can also be used to identify column names so long as the connection being used to access the database has the “Use ANSI quoted identifiers” configuration option checked ON. This is the setting required for the Seagate Crystal Reports that use the Pharos Reports ODBC data source.

® The SQL views exposed for Pharos Reports are built up by joining across multiple tables from the underlying database and should only be used to retrieve data for reports. They should not be used in INSERT, UPDATE or DELETE statements.

® When generating custom reports Pharos recommends that as much processing of the results as possible is carried out in the query that is executed on the SQL Server. This reduces the information passed down to the client running the report. If creating custom reports using Seagate Crystal Reports, we recommend that the option “Perform Grouping on Server” is checked ON: found under File > Options > Database > Advanced Options.

Accessing the SQL Server

Pharos Reports uses the same SQL logon and password that is used by the Pharos Database Server to connect to the SQL Server. This SQL logon is the ‘dbo’ for the ‘pharos’ database and has full access to all objects in the database. Any custom report application that uses a different method of authentication against the SQL Server will need to have appropriate access and permissions configured against the ‘pharos’ database.

Creating a Specific Reporting Login

If required, a specific reporting login can be created using SQL Server Enterprise Manager under the Security > Logins context of the SQL Server running the 'pharos' database.

When creating the Login, set the default database to the 'pharos' database and select the 'pharos' database under Database Access. At this point the Login is automatically assigned the 'public' role in the 'pharos' database, which allows the user to connect to the database but does not allow the user to query any of the tables or views. If the new login is only going to query against the SQL views created for PHAROS Reports then we recommend that the login is given the 'db_datareader' role under Database Access. Alternatively, set up SELECT permissions for the new login against the specific tables or views that are to be accessed by the custom reports, but note that these permissions need to be reset after any upgrade to the 'pharos' database.

Detailed View Descriptions

rpt_transactions

Description: Report view on transactions table, with common foreign key references resolved

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Cost Center varchar(649) Null The expanded name of the Cost Center associated with the transaction

Transaction Type varchar(67) Null Transaction type with Resource Name resolved from resources table: Credit Print Computer (Resource Name) Application (Resource Name) Transfer Funds Copy PrintOpJob

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(11) Null Flag indicating where the money was debited: Internal, External, or Cost Center

External Billing varchar(7) Null Flag indicating whether external billing was online or offline

Quantity int Null The number of units associated with the transaction (pages, prints)

Quantity Name varchar(32) Null The display name for the transaction quantity: Pages Prints Minutes

Amount Charged money Not Null The amount charged against the transaction

rpt_print_transactions

Description: Report view on transactions table filtered against 'Print' transactions

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Cost Center varchar(649) Null The expanded name of the Cost Center associated with the transaction

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(11) Null Flag indicating where the money was credited: Internal, External or Cost Center

External Billing varchar(7) Null Indicates whether external billing system was Online or Offline when transaction was raised.

\\Server\Printer varchar(67) Not Null The name of the Printer or MFD associated with the transaction

Pages int Null The number of pages recorded against the transaction

Sheets int Null The number of sheets recorded against the transaction

Amount Charged money Not Null The amount charged against the transaction

rpt_credit_transactions

Description: Report view on transactions table filtered against 'Credit' transactions

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Cashier varchar(255) Not Null The name of the Cashier User that raised the transaction

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(8) Null Flag indicating where the money was credited: Internal or External

Purse Name varchar(32) Null The name of the Purse against which the credit transaction was raised

Amount Charged money Not Null The amount charged against the transaction

rpt_copy_transactions

Description: Report view on transactions table filtered against 'Copy' transactions

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Cost Center varchar(649) Null The expanded name of the Cost Center associated with the transaction

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(11) Null Flag indicating where the money was credited: Internal, External or Cost Center

External Billing varchar(7) Null Indicates whether external billing system was Online or Offline when transaction was raised

Copier varchar(32) Null The name of the Copier or MFD associated with the transaction

Prints int Null The number of prints recorded against the transaction

Amount Charged money Not Null The amount charged against the transaction

rpt_printop_transactions

Description: Report view on transactions table filtered against 'PrintOp' transactions

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Cost Center varchar(649) Null The expanded name of the Cost Center associated with the transaction

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(11) Null Flag indicating where the money was credited: Internal, External or Cost Center

PrintOp Station varchar(32) Not Null The name of the PrintOp Station where the PrintOp job was processed

Spool Queue varchar(32) Not Null The name of the PrintOp Spool Queue to which the original PrintOp Job was passed

Job Name varchar(255) Null The name of the PrintOp Job that was passed to PrintOp Spool Queue

Number of Copies int Not Null The number of copies of the PrintOp Job that were printed

Pages int Null The number of pages recorded against the transaction (all copies)

Sheets int Null The number of sheets

Amount Charged money Not Null The amount charged against the transaction

rpt_transfer_transactions

Description: Report view on transactions table filtered against 'Transfer' transactions

Column Type Nullable Description

Transaction ID int Not Null Unique identifier of transaction

Date/Time datetime Not Null Date and time when the transaction was recorded

User ID varchar(255) Null The logon ID of the User associated with the transaction

User Group varchar(255) Null The name of the User Group to which User belongs

Pharos Station varchar(32) Not Null The name of the Pharos Station where the Transfer transaction was generated

Server varchar(32) Not Null The name of the Server on which the transaction was recorded

Bank varchar(64) Null The name of the Bank against which the transaction was recorded

Charging Type varchar(8) Null Flag indicating where the money was credited: Internal (only)

Purse Name varchar(32) Not Null The name of the Purse against which the transfer transaction was raised. (Always against Purse 3)

Amount Charged money Not Null The amount charged against the transaction

rpt_users

Description: Report view on people table filtered by 'User' type

Column Type Nullable Description

User ID varchar(255) Not Null User’s logon ID

First Names varchar(50) Null User’s first names

Last Name varchar(30) Null User’s last name

Middle Initial char(1) Null User’s middle initial

User Alias varchar(32) Not Null Alternative identifier for User displayed on Pharos Clients and Pharos Station

User Group varchar(255) Null The name of the User Group to which the User belongs

Access Level varchar(20) Not Null The User’s access level:Administrator, Proctor, Cashier or User

Billing Option varchar(10) Not Null The User’s billing option: Advance or Arrears

Discount Rate varchar(77) Null The name of the discount rate that applies to the User. The name is followed by (User) if the rate is directly associated with the User, or (Group) if rate is inherited from the Users User Group

Active? tinyint Not Null Whether the User’s account is active: Yes or No

Address varchar(255) Null User’s address

Phone varchar(30) Null User’s phone number

Purse 1 Balance money Not Null User balance in Purse 1

Purse 2 Balance money Not Null User balance in Purse 2

Purse 3 Balance money Not Null User balance in Purse 3

Purse 1 Name varchar(32) Null Display name for Purse 1

Purse 2 Name varchar(32) Null Display name for Purse 2

Purse 3 Name varchar(32) Null Display name for Purse 3

rpt_groups

Description: Report view on people table filtered by 'Group' type

Column Type Nullable Description

Group Name varchar(255) Not Null The name of the User Group

Discount Rate varchar(64) Null The name of the discount rate that applies to members of the User Group

Number of Members int Null The number of Users that belong to the User Group

rpt_alerts

Description: Report view on alerts table

Column Type Nullable Description

Server varchar(32) Not Null The name of the server that raised the Alert

Date/Time datetime Not Null The date and time when the Alert was raised

Severity varchar(32) Not Null The severity of the Alert: Information, Auditing, Warning, Error or Fatal

Service Type varchar(32) Not Null The type of service on the server that raised the Alert e.g. PSPrintSvr

Error Code int Not Null The error code associated with Alert, identifying in greater detail where the error was generated

Client varchar(32) Null The hostname of the Client machine that raised the Alert

User varchar(255) Null The logon ID of the User recorded against the Alert

Operation varchar(32) Null Detail of the operation that caused the Alert

Item varchar(255) Null The object associated with the operation e.g. PrintJob

Message varchar(255) Not Null Text of the error message recorded against the Alert

Important

Access to the SQL views should be on a read-only basis. Modifying data in the Pharos database by any means other than using standard Pharos applications is not recommended and could result in system instabilities.