WHERE MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay.AssignmentUID AND MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID AND MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay.ProjectUID AND MSP_EpmAssignment.TaskUID = MSP_EpmTask_UserView.TaskUID AND (MSP_EpmAssignmentByDay.AssignmentActualWork>$0) AND (MSP_EpmProject_UserView."Internal Project"='No')
I started working on this, but cannot get it to work.
OLEDBCONNECTTO[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=epm-2010-reporting;Data Source=hbg-sql2008;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HBG-THORH;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];
You obviously have a problem in your data model, perhaps too many keys. Do the following:
Switch to "Source Table View" in the table viewer. Now you can see what you really have loaded.
Rename the tables (by adding a label in front of the Load in the script). This will not change the data model, but it will help you think. If a table has exactly one record per project, the table should be called "Projects". The same for "Assignments" and "Tasks". If you don't know what each record represents, you need to figure it out.
Look at the data model and think. Ask questions like: Are the keys in the right tables? Are there any redundant keys? Are the tables linked correctly? Can there be more than one Assignment per Task? Can a Task belong to several Projects?
Without knowing the data, I cannot answer these questions.