The attached report illustrates how to safely query the RDB to allow team members to see a read only view of task assignment data. We’ve picked a random task custom field to illustrate the concept. I’ve deliberately defined the report to use “drill down” by Project Name to mimic the My Tasks grid behavior and to (UI) scale for users with lots of task assignments.
The figure below illustrates the My Tasks web part placed above the Report Viewer web part which is displaying the sample report.
The report has two parameters that are used in the query:
To maximize performance we avoided use of the _Userview SQL Views and referenced the underlying tables. If you want to access Task Assignment custom fields (the example show Task Custom Fields) then the query in the report will need to be reworked slightly, this should be trivial.
The RDB is only minimally indexed. We strongly recommend the addition of secondary indexes on the following tables:
Msp_Epm_Resource (Composite on ResourceNTAccount, ResourceUID)
Msp_Epm_Assignment (Composite on ResourceUID, AssignmentStartDate)
And if you are delivering a small sub set of custom fields you could consider an indexed view over those fields to maximize performance.
PingBack from http://www.travel-hilarity.com/airline_travel/?p=703