This article will show you how to connect to Projectworks from Microsoft Power BI Desktop so you can build your own custom reporting solutions
Step 1: Get your Projectworks database credentials
In the Admin portal of Projectworks browse to the Integration Section and select the "SQL Logins" tile.
Here you will find two sets of credentials, an admin set, and a basic set. The admin credentials have access to cost and margin data, whereas the basic credentials do not.
Click the ellipses at the end of the row for the credentials you would like to use and select "View Login Details". This will open a pop-up which includes everything you need in order to connect from Power BI.
Step 2: Connect to Projectworks from Microsoft Power BI Desktop
When you open Power BI you will see a tile labeled "Import data from SQL Server". You will also see an item in the menu bar labeled "SQL Server" in the "Data" section. Select either of these options.
That will open a pop-up asking for your Server and Database, enter both these from the credential information found in Projectworks. It is important to specify your database otherwise authentication will fail in the next step. You can also choose Import or DirectQuery here. Import is faster but will not update without manually refreshing, DirectQuery is slower but means your data will always be up to date.
Click OK and you will then be prompted for your credentials. Select "Database" on the left hand side tab, then enter the User name and Password from Projectworks. In the "Select which level to apply these settings to" dropdown, if you have multiple options then select the option which includes both the server and database name, then click Connect.
Step 3: Import data into Microsoft Power BI From Projectworks
Once you are connected you will be presented with the Navigator which enables you to preview your data in real time from Projectworks. The available database Views that can be queried are listed down the left hand side. Scroll down to the "BI.DimUser" View and select the checkbox associated with that View. This should open a Preview of all your users that are currently in Projectworks.
Click the "Load" button to load the User data into Power BI. Once loaded you can click the Data View icon on the left where you should now see your Projectworks users loaded into Power BI.
If you switch back to the Report View you can now start creating your own reports with your data from Projectworks.
