Chapter 1
Configuring and Using P4Report
To install P4Report, download the installer from the Perforce web site and run it. The installer enables you to specify where you want P4Report installed and to choose from available options. When you install P4Report, the installation program configures a default ODBC data source using the Perforce settings in effect on your client computer. To access other Perforce servers, you can modify the default data source or configure additional ODBC data sources, one for each server. The following section tells you how.
Configuring an ODBC Data Source
To configure an ODBC data source for a specific Perforce server, perform the following steps:
- Choose Start>Perforce>P4Report>Data Sources.
The ODBC Data Source Administrator dialog is displayed.
- Click Add... The Create New Data Source dialog is displayed.
- Scroll to Perforce ODBC Driver, click it, then click Finish. The ODBC P4Report Setup dialog is displayed.
- Enter the required information as follows:
- Data Source Name: assign a name that corresponds to the Perforce server from which you intend to retrieve data. (The Description field is optional.)
- Port: the host computer and TCP/IP port number on which the Perforce server listens for client requests. Corresponds to the Perforce P4PORT setting.
- User: the name of the Perforce user that P4Report uses to connect to the Perforce server. Corresponds to the Perforce P4USER setting.
- Client: the name of the Perforce client specification that P4Report uses to connect to the Perforce server. Corresponds to the Perforce P4CLIENT setting.
- Host: the name of the Perforce client computer. Corresponds to the Perforce P4HOST setting.
- To configure the data source to use the Perforce settings in effect on the client computer at run time (instead of specifying fixed settings), check the Default boxes for the desired settings. For details about Perforce settings, refer to the Perforce User's Guide.
- To configure the data source so that ODBC client programs can update the underlying data, check the Enable Updates box.
- Click OK. Your data source is added to the list of data sources displayed on the ODBC Data Source Administrator dialog.
Reporting Using Microsoft Excel
To create reports based on Perforce data, you can use any reporting tool that accepts data from an ODBC data source. This guide tells you how to create reports using Microsoft Excel.
Before you define queries in Excel, be sure to define all required data sources as described in the preceding section, "Configuring an ODBC Data Source" on page 9.
To enter the SQL queries in the guide into Microsoft Excel spreadsheets, you must have Microsoft Query installed. There are two ways you can query the Perforce database:
- Query Wizard: the Query Wizard enables you to define simple queries graphically.
- SQL: For complex queries, such as queries that use SQL functions that are not available in the Query Wizard, you can enter SQL. You can use the SQL queries in this guide as templates.
Creating Queries Using the Query Wizard
To create queries using the Query Wizard, perform the following steps.
- Launch Excel. A new, empty spreadsheet is displayed.
- Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.
- On the Databases tab, choose the Perforce data source from the list of data sources and click OK. The Query Wizard - Choose Columns dialog is displayed.
- Expand the desired tables, choose the desired columns column, and click > to move the columns into the Columns in your query list.
- Click Next. The Filter Data dialog is displayed.
- Enter any conditions you want to apply to the data, to extract only the data on which you want to report.
- Click Next. The Sort Data dialog is displayed.
- Specify any sort orders you want to apply to the data. For example, to display time or date fields starting with the most recent, sort these fields in descending order.
- Click Next. The Finish dialog is displayed. Click Finish to return the data to your spreadsheet.
Entering SQL Queries in Microsoft Excel
To enter an SQL query into a spreadsheet, perform the following steps.
- Launch Excel. A new, empty spreadsheet is displayed.
- Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.
- On the Databases tab, choose the Perforce data source from the list of data sources and click OK. The Query Wizard - Choose Columns dialog is displayed.
- Expand any table, choose any one column, and click > to move the column into the Columns in your query list. It does not matter which column you choose, because you will replace the entire query with your own.
- Click Next. The Filter Data dialog is displayed.
- Click Next. The Sort Data dialog is displayed.
- Click Next. The Finish Dialog is displayed.
- Choose the View or edit data in Microsoft Query option and click Finish. Microsoft Query is launched, displaying your initial query.
- Choose View>SQL... The SQL dialog is displayed.
- Enter your SQL query in the SQL window and click OK. If your SQL query is correctly formed and the Perforce database contains any data that fulfills the query conditions, Microsoft Query displays the data in its main window.
- To place the data in your spreadsheet, choose File>Return Data to Microsoft Excel.
Using the Perforce SQL Command-Line Client
P4SQL, the Perforce SQL Command-Line Client, enables you to query Perforce servers directly using SQL, without going through Microsoft Excel, Crystal Reports, or other reporting software. P4SQL runs in a DOS window and accepts SQL-92-compliant queries. By default P4SQL queries the server specified by the Perforce settings in effect on your client computer (P4PORT, P4USER, P4CLIENT, and P4HOST) and does not use an ODBC data source. To specify an ODBC data source, use the -n flag when you invoke P4SQL.
Launching the command line client
To launch P4SQL, choose Perforce>P4Report>SQL Command-Line Client. A DOS window is launched and the command line client is started. Alternately, open a DOS window, change to the directory where you installed P4Report, and run p4sql from the command line. When invoked from the command line, the SQL command line client accepts the following flags:
Flag
|
Description
|
---|
-c client
|
Set client name (default P4CLIENT)
|
-d "delimiters"
|
Specify the column delimiter characters for output. You can specify a single delimiter character or a string (multiple characters). If the delimiter character has meaning to the command shell, enclose it in double quotes.
For example, to put a comma between column values, invoke P4SQL as follows:
p4sql -d ","
|
-e
|
Enable updates and inserts.
|
-H host
|
Set host name (default P4HOST)
|
-h or -?
|
Print usage message
|
-i file
|
Execute the SQL queries in the specified text file.
|
-n ODBC_data_source
|
Specify an ODBC data source to query
|
-P password
|
Set user's password (default P4PASSWD)
|
-p port
|
Set server port (default P4PORT).
|
-q
|
Set quiet mode (suppresses header/footer output)
|
-s query
|
Run a single SQL statement. For example:
p4sql -s "select * from jobs"
|
-u user
|
Set user's Perforce username (default P4USER)
|
-V
|
Print version information
|
Running queries
At the SQL> prompt, enter your queries (for example, select * from users;). By default you cannot alter the Perforce metadata using P4Report. To enable updating, start P4SQL from the command line, specifying the -e flag (or add the -e flag to the command associated with the SQL Command Line Client menu shortcut).
Note that typing RETURN or ENTER does not submit your query. You must terminate every query with a semicolon (;).
You can create text files that contain queries and run them from the command line by invoking the command line client, specifying the -i flag. You can send query output to an output file. By default, the query output returned by P4SQL is tab-delimited, enabling you to import it into Microsoft Excel. For example, to store query output in a file named output.txt, invoke P4SQL as follows:
C:\Program Files\Perforce\p4sql.exe -i query.txt > output.txt
The LIKE clause
In P4Report queries, LIKE clauses are case-sensitive so they can be sent verbatim to a UNIX Perforce server.
The SQL wildcards _ and %, which match single and multiple characters respectively, are translated by P4Report into Perforce wildcards as follows:
SQL wildcard
|
Perforce wildcard
|
---|
%
|
...
|
_
|
*
|
ODBC syntax vs SQL-92 syntax: joins and escape sequences
Join syntax
The SQL-92 syntax for expressing joins is as follows (words in square brackets [] are optional):
SELECT * FROM table-reference [NATURAL] <join type> JOIN table-reference
where <join type> is one of the following:
- INNER
- LEFT [OUTER]
- RIGHT [OUTER]
- FULL [OUTER]
- UNION
- CROSS
The ODBC standard defines its own outer join syntax, as follows:
SELECT * from {oj table-reference {LEFT | RIGHT | FULL}
OUTER JOIN {table-reference}
P4Report support the ODBC join syntax, but the syntax is primarily designed for ODBC applications that generate queries and require a standard way of specifying outer joins queries. For purposes of simplicity, SQL-92 join syntax is recommended.
Escape sequences
Curly brackets ({}) are used by ODBC for escape sequences that contain standard syntaxes for the following SQL features:
- Date, time, timestamp, and datetime interval literals
- Scalar functions such as numeric, string, and data type conversion functions
- LIKE predicate escape character
- Procedure calls
- Outer joins
Note that P4Report discards ODBC escape sequences when parsing an SQL expression, thereby converting the expression into a standard SQL one. If you use the preceding features, use standard SQL syntax and do not use ODBC escape sequences.
Query Performance
Avoid uncorrelated subqueries
For best performance, avoid queries that contain uncorrelated subqueries, because the subquery is executed for every row returned by the SELECT clause. Instead of subselects, use joins.
For example, instead of this subselect:.
select user from users where user in (select owner from clients);
|
use a simple join:.
select user from users, clients where user = owner;
|
Memory and CPU usage
Queries that contain an ORDER BY or GROUP BY clause require P4Report to load the report set into memory for sorting. For such queries, memory use increases in proportion to the number of rows in the result set, the number of columns in each row and the size of each column.
When a query is running, the SQL engine processes data until the query is complete. To process the data as fast as possible, the engine uses all the CPU time that it is given. If you monitor query performance, you are likely to see high CPU consumption during query processing.
Please send comments and questions about this manual to
manual@perforce.com.
Copyright 2002-2003 Perforce Software. All rights reserved.
Last updated: 12/12/03