Using the Reporting Database and Excel – Part 2

  • Article

This is a continuation from my previous post. In this post, I will be illustrating how someone can import data from the Project reporting database from a SQL query. To began, we will need to open up Excel.

The first step is to create a connection to the Project reporting database:

1. Click on the Data tab

2. Click “From Other Sources”

3. Click “From SQL Server”

 

This will kick off a wizard that will guide you through the steps of setting up a connection to a SQL server. To run through the wizard you will need the following details:

Server Name –This is the server name of the server that is hosting the reporting database

Credentials – These are the credentials that are used to log on to the reporting database

Database Name – The name of the reporting database, which for me is ProjectServer_Reporting

You don’t have to connect to a table, since we are going to use the SQL query from the last post to get the data we need from the reporting database. Once we have created the connection, we need to configure the connection to use the SQL query. To change the configuration:

 

1. Click Connections from the Data tab

 

 

2. Select the Workbook Connection we just created

3. Click Properties

4. Select the Definition tab

5. Change the Command type to SQL

6. Copy and Paste the SQL Query into the Command text box

 

 

7. Click OK

8. Click Close

Now we need to have the data show up in our workbook.

1. On the Data tab, click on Existing Connections

2. Select the connection that we created for the workbook

3. On the Import Data dialog, just click OK

 

 

This will select the default settings and the returned table from our query will be returned to the selected worksheet.

Now we have the data from the reporting database populated in the Excel worksheet where we can manipulate the data:

 

 

 

Hope this helps!

Chris Boyd