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.