This week we were working on an Excel Services report that pulls data from Project view tables, Resources, Tasks and Assignments.
Luckily, we have Power Pivot available to help join these tables, right?
We started pulling the tables one at a time using the Data/From Other Sources/SQL option.
We enter the server name and our tables come up in a drop down.
We choose our PWA database and select the table we want. However, the table doesn’t have the GUID columns. This is a problem, because we need those columns to create our relationships.
Back to the drawing board. Data/From Other Sources/SQL.
Now we notice there is a checkbox for Multiple Tables. We select this and pull in all the tables we need. Our tables come in WITH their GUID columns. First obstacle solved.
The next thing we run into is a need to filter the AssignmentsByDay table. This table can grow quite large as you can imagine. In this situation, where the database was large, pulling in the whole table was locking up the data retrieval.
So, we created a separate data source for that specific table. We pulled only the fields we needed, using a SQL query. The process continued to lock up. Then we added a filter to pull only information from days no older than 30 days ago. Also, a filter for rows where actual work was > 0. Bingo! This worked.
However, we were back with the problem where the GUIDs didn’t pull in.
The last thing we did to fix this problem was to cast the field as a character string using CAST AS NVARCHAR(100) surrounding the table/field name. This fixed our final problem, and we were able to proceed.
Thanks to the Mr. Excel blog for the concept/sample for the character string solution.
