Intro to SQL with Deltek Vision
To being you’ll need to have SQL Management studio installed and a login to access your Vision Database. You can download the studio 2014 from here:
Once you open the Studio you should be prompted to connect to your server. Enter your server name (this is in most cases different than your Vision server) and then you’ll need choose your Authentication, if you’re using Windows Authentication your database administrator will need to allow your windows login access to the database, if you’re using SQL Authentication then your database administrator needs to provide you a login account to use, the vision login account can be used for this.
After you connect make sure your vision database is selected from the drop down in the upper left.
To begin learning SQL the Design Query in Editor is a great way to begin, simply select if from the Query dropdown menu as seen below.
Then begin by selecting the tables to use, to begin just select the PR table, which is the Project table.
You should see something similar screen below now with the PR table. The concept of a table is very similar to an Excel Workbook, the table is just a series of columns and rows. The rows simply contain the data and the columns determine what type of data can be stored. For example most columns that describe something like a name or description would store a character value (like “Project 1234”), and columns that hold numbers for something like a contract or invoice amount would be configured to store numerical values (like 1230.50).
Note there a different types of character and number columns, there’s fixed or variable length columns and there’s integer or decimal type number columns.
For this example just check off a few of the columns in the PR table, in this example just the WBS1 (this is the top level project number) and the Name columns are chosen. After that’s checked, notice the bottom of the window creates the SQL for you. Learning SQL begins with the Select and the From statements. The Select statement is used to specify which columns you’d like from the Table (just like a excel worksheet, column 1 & 6 etc), and the FROM statement simply specifies which table (again analogous to a worksheet), so you’re ‘Selecting’ the Columns ‘From’ a Table. So in our example we’re using “select wbs1, name from PR” to get the name and number from all your project records.
To view the results click OK and then press the !Execute button, or pressing the F5 key on your keyboard should work as well.
This result will likely be quite large, this where the WHERE statement comes in, it is used to refine your results. So for example you can use “select name from pr where wbs1 = ‘13245.00’” to get the name of your project with a number of ‘13245.00’. In this example wbs1 is the name of the column in the PR table that’s commonly referred to as the project number. One thing to note that when using text fields you’ll need to enclose them with quotes that’s why the where wbs1=’13245.00′ is enclosed with quotes. Another example you can do now with the query you created before would be to get just active projects, which is set by the Status column, so you would use just add “where status=’a'” to get select name, wbs1 from pr where status = ‘A’, as seen below.
You’ll notice there’s lots of duplicate in this prior example, that is because the PR table contains the projects top level (wbs1) and also it’s lower level phases (wbs2) and tasks (wbs3). Let’s add them to the select so it now reads select name, wbs1, wbs2, wbs3 from pr where status=’a’ and you’ll now see those lower levels.
To get just the top level projects you can add an additional filter in the where clause, to only get project records where the second level is blank. To do that just add “and wbs2=”’ which reads where wbs2 is blank. So you should get a result similar to the one below.
You can also practice or experiment with Where clauses in the Query Designer, simply enter them as Filters, so in the example below we’ve added Org to the results and specified to only get results of projects in the BO:AR org and that have a status of Active, so the where clause is Where Status=’A’ AND Org=’BO:AR’, to get our active projects in the BO:AR (Boston Architectural) org.
Tip, one of the best ways to learn about what’s contained in a table is to use the * character to get all the columns in a table, this is often what you’ll need to figure out that to filter active projects that status needs to be equal to ‘A’ and not ‘Active’ or some other value. So to get all the columns you can use “select * from pr” or if you just want to look at one project you can use “Select * from pr where wbs1=’1234.00′” – just use one of your project numbers. Note that you’ll still get second and third level records from this project if you don’t use the wbs2=” (wbs2 and wbs3 if you use it).
Spend some time “Selecting” data from different tables using either just the SQL screen or the Query Designer, most tables use a convention similar to the projects table (PR), employees in them EM, clients in CL, etc. Opportunities are an exception they in an opportunities table. In the next article we’ll show you how to combine information from different tables. Note the EM table may contain sensitive information, so have a discussion with your database administrator about limiting your access or others to that table. Access to table can be restricted by individual login from either a sql or windows account.
One quick tip for exporting or using your results if you create a query result set you’d like to use or share. Simply right click in the upper left corner of the query grid, and choose Copy with Headers.
Then open Excel and right click in the same upper left corner and choose paste.
And you’ll get your results quickly in to Excel.
Next you can read about using multiple tables here: