Perforce 2008.2 P4Report User's Guide
<< Previous Chapter
About This Manual
Table of Contents
Index
Perforce on the Web
Next Chapter >>
Administrative Reporting

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:

  1. Choose Start>Perforce>P4Report>Data Sources.

    The ODBC Data Source Administrator dialog is displayed.

  2. Click Add... The Create New Data Source dialog is displayed.

  3. Scroll to Perforce ODBC Driver, click it, then click Finish. The ODBC P4Report Setup dialog is displayed.

  4. Enter the required information as follows:

  5. 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:

Creating Queries Using the Query Wizard

To create queries using the Query Wizard, perform the following steps.

  1. Launch Excel. A new, empty spreadsheet is displayed.

  2. Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.

  3. 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.

  4. Expand the desired tables, choose the desired columns column, and click > to move the columns into the Columns in your query list.

  5. Click Next. The Filter Data dialog is displayed.

  6. Enter any conditions you want to apply to the data, to extract only the data on which you want to report.

  7. Click Next. The Sort Data dialog is displayed.

  8. 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.

  9. 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.

  1. Launch Excel. A new, empty spreadsheet is displayed.

  2. Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.

  3. 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.

  4. 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.

  5. Click Next. The Filter Data dialog is displayed.

  6. Click Next. The Sort Data dialog is displayed.

  7. Click Next. The Finish Dialog is displayed.

  8. Choose the View or edit data in Microsoft Query option and click Finish. Microsoft Query is launched, displaying your initial query.

  9. Choose View>SQL... The SQL dialog is displayed.

  10. 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.

  11. 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)

-C charset

Specify character set, only if querying a Unicode-mode server. Default is P4CHARSET. For details about Unicode-mode servers, refer to the Perforce System Administrators Guide.

-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:

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

%

...

_

*

Limiting the number of rows returned

To ensure that a query returns no more than a specified maximum number of rows, include the LIMIT clause in the query. For example, the following query returns no more than 40 rows:

p4sql -s "select depotFile from fstat     where filespec='//depot/main/server/....cc' limit 40 You can also specify the start row using the LIMIT clause. For example, if you want your query to return 40 rows starting with the fifth row, discarding the first four result rows, specify an offset of five as follows:

p4sql -s "select depotFile from fstat     where filespec='//depot/main/server/....cc' limit 5, 40 The syntax for the LIMIT clause is as follows:

LIMIT [<offset>,]<limit>

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:

The ODBC standard defines its own outer join syntax, as follows:

SELECT * from {oj table-reference {LEFT | RIGHT | FULL}
OUTER JOIN {table-reference}
P4Report supports the ODBC join syntax, but the syntax is primarily designed for ODBC applications that generate queries and require a standard way of specifying outer join 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:

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.


Perforce 2008.2 P4Report User's Guide
<< Previous Chapter
About This Manual
Table of Contents
Index
Perforce on the Web
Next Chapter >>
Administrative Reporting
Please send comments and questions about this manual to manual@perforce.com.
Copyright 2002-2008 Perforce Software. All rights reserved.
Last updated: 12/05/08