Embed SQL Queries in Project Center Templates

You must be a Project Center administrator to use perform this procedure.

There are two tables available within all Project Center templates that are used for evaluating SQL queries to produce results for other template properties. These tables are called <sql connections table> (referred to as the "connections" table) and <sql table> (referred to as the "query" table).

• The connections table enables you to specify multiple OLE DB or ODBC data sources and connection strings. Each connection has a friendly name and an OLE DB or ODBC connection string.  

When crafting these connection strings, do not leave plain text database user passwords lying around.

You must use the <sql connections table> and the <sql table> rather than embedded SQL queries from Excel, as the embedded queries are not executed within templates.

Each property specified in the SQL table must be unique.

• The second SQL table, the query table, enables you to map template statements to SQL queries that populate their contents. Each row within this table specifies the template statement to populate, the friendly name of the data source to query from the connections table, and the SQL statement to execute. There are then zero or more substitutable parameters that can be evaluated at execution time and substituted into the SQL statements. These parameters, <p0> through <p9>, correspond to OLE DB/ODBC parameters represented as question marks (‘?’) in the SQL statements. The value of <p0> is inserted into the first substitution parameter, the value of <p1> is placed in the second substitution parameter, and so on.

The Project Center Server comes with a set of canned SQL queries that serve as examples for creating projects and importing contact information from Active Directory. These templates are located in C:\Program Files (x86)\Newforma\2022 Edition\Project Center\Templates\SQL Template.xlsx (for 64-bit machines) or C:\Program Files\Newforma\2022 Edition\Project Center\Templates\SQL Template.xlsx (for 32-bit machines).

You can integrate the sample SQL queries into your work process by copying the relevant sections from the example and pasting them into your own SQL template. Be sure to modify the connection string to point to your particular data source. Also, it is worth reviewing these queries to ensure that they obtain the data you are interested in importing.

The example below contains sample connection and SQL tables. The <sql connections table> specifies a single data source to a Deltek Vision database located on a server named hebron. The database instance on that server is VisionDemo50. The <sql table> has a single project template mapping for a project’s name. This query is set to use the value for the current project number in the template or the cell adjacent to the <project number> tag.  To set these queries correctly, make the parameter values for the queries literal values or cell references to other values. In this case, <p0> is a reference to cell B1 at the top of the spreadsheet.