Most of the reports I create have a time element… Which client used the most project hours last year? ….. How many hours are my QA people scheduled to work next month? Without that time framework, the information is less valuable.
Time phased reports do, however, have an unfortunate side. The tables we must use to get this information are huge, often requiring 20 minutes or more to refresh, or a PowerPivot layer to accept more rows of data than a standard spreadsheet.
When we create Project Online reports, we use Odata. Luckily, Microsoft’s Power Query is able to import data from Odata and create filters to limit the data we access.
The first thing to do is add Power Query to Excel. Power Query is a free add-in.
Under the File menu, Options/Add-Ins, select COM add-ins.
Select Power Query and click Add.
Now, let’s go get some data.
Open Excel and click on the Power Query tab.
Click on From Other Sources/From Odata Feed.
Enter the URL for your PWA’s Odata. This is your PWA’s URL plus /_api/projectdata.
At this point, you will probably be asked to re-sign in to access the data. Select Organizational login.
You will see a list of Odata tables. Select the time-phased table you want to filter. For me, this is often the AssignmentTimePhasedDataSet table. This table has planned and actual work, per day, for all assignments. Yes, it is quite large.
The first thing we will do is fairly simple. If you table has a lot of columns, use the Choose Columns option to select only the columns you need. TIP: never remove ID columns, as these are frequently used to create relationships between tables.
The next step is the most important one. You are able to use Date/Time Filters to select data according to how close it is to today’s date.
To do this, click on the down arrow at the top of the TimeByDay column. Select Date/Time Filters.
Often, I use the “is in the previous” or “is in the next” filters. This will make a huge difference in the size of the table you pull.
TIP: Sometimes you can’t restrict the data using this technique. For instance, if you need to make monthly data available for all projects throughout their life, this filter is not useful.
Notice that the View/Advanced Filter actually shows you the filter that you’ve created using these GUI tools.
