I recently wanted to get at the P3e data via excel in order to create a spreadsheet that wasn’t possible using the reports wizard in P3e (or not that I could find anyway. Since then I’ve been asked how it’s done, so I thought I’d share it with the rest of you, as it’s been invaluable to me. I have a standalone MSSQL server driving my database, so it may be different if you’re on a network or use ORACLE/another dbase. Anyway, the process is as follows: (’>’ means click)
1. Open a spreadsheet and select >Data>Get External Data>New Database Query.
2. Select ’’ >OK and type in item #1. the name you want to call it... eg. Test...
3. Select your server from item #2.... in my case this is the SQL Server.
4. In item #3. >Connect and type in or select the name of your server in the following format: SERVERNAME\PRIMAVERA and enter the user name and password in the LoginID and password boxes below. I had trouble with this bit because it’s not the login you use for getting into the P3e application, but finally found out from the Primavera website that the default for this is ’privuser’ for both, but of course you may have to sweetalk your administrator for this if ’privuser’ doesn’t work for networked versions.
5. >OK and Excel will connect to the database and open up item 4. giving you the option to select a default database table which isn’t necessary at this stage, although a good one to pick would be PROJECT as it contains the project unique ID’s and proj_short_name which is the Project ID in the P3e application. It’s up to you if you select the save login option.
6. >OK and you’ll go back to the ’choose data source’ box.
7. Select your data source, eg. Test, and >OK it.
You’ll then get the Query Wizard - Choose Columns box. This means you’ve been successful and you’re in!
The lookup tables for the database mapping is in the Documentation folder on installation disk 1, and there’s also a schema telling you what links to what. The most useful tables I found so far are PROJECT, TASK (activities), PROJCATVAL (project code values), but there are many many more... have a look around and enjoy!!
Emma
1. Open a spreadsheet and select >Data>Get External Data>New Database Query.
2. Select ’’ >OK and type in item #1. the name you want to call it... eg. Test...
3. Select your server from item #2.... in my case this is the SQL Server.
4. In item #3. >Connect and type in or select the name of your server in the following format: SERVERNAME\PRIMAVERA and enter the user name and password in the LoginID and password boxes below. I had trouble with this bit because it’s not the login you use for getting into the P3e application, but finally found out from the Primavera website that the default for this is ’privuser’ for both, but of course you may have to sweetalk your administrator for this if ’privuser’ doesn’t work for networked versions.
5. >OK and Excel will connect to the database and open up item 4. giving you the option to select a default database table which isn’t necessary at this stage, although a good one to pick would be PROJECT as it contains the project unique ID’s and proj_short_name which is the Project ID in the P3e application. It’s up to you if you select the save login option.
6. >OK and you’ll go back to the ’choose data source’ box.
7. Select your data source, eg. Test, and >OK it.
You’ll then get the Query Wizard - Choose Columns box. This means you’ve been successful and you’re in!
The lookup tables for the database mapping is in the Documentation folder on installation disk 1, and there’s also a schema telling you what links to what. The most useful tables I found so far are PROJECT, TASK (activities), PROJCATVAL (project code values), but there are many many more... have a look around and enjoy!!
Emma