Importing Projects Module data into Analytics Plus

We are yet to sync 'Projects' module's data into Analytics Plus. Meanwhile, you can use custom queries to import those data from ServcieDesk Plus.

Step 1:

Step 2:

Click 'Import Data' and Choose 'Local and Cloud Databases' under Import From Data Sources section.

Step 3:

Provide the database server connection details under 'Connect to Local and Cloud Databases'.

Step 4:

Now choose 'Custom Query' under 'Select Data to Import' and paste the below custom query to pull project details.

SELECT "projectdet"."PROJECTID" AS "Project id","projectdet"."PROJECTCODE" AS "Project Code","projectdet"."TITLE" AS "Title","projecttypedef"."PROJECTTYPENAME" AS "Project Type","projectowner"."FIRST_NAME" AS "Owner","projectprior"."PRIORITYNAME" AS "Priority","projectstatus"."STATUSNAME" AS "Status","projectcreatedby"."FIRST_NAME" AS "Created by", DATEADD(MILLISECOND, "projectdet"."CREATEDTIME" % 1000, DATEADD(SECOND, "projectdet"."CREATEDTIME" / 1000, '19700101')) AS "Created date", DATEADD(MILLISECOND, "projectdet"."SCHEDULEDSTARTTIME" % 1000, DATEADD(SECOND, "projectdet"."SCHEDULEDSTARTTIME" / 1000, '19700101')) AS "Schedule start", DATEADD(MILLISECOND, "projectdet"."SCHEDULEDENDTIME" % 1000, DATEADD(SECOND, "projectdet"."SCHEDULEDENDTIME" / 1000, '19700101')) AS "Schedule end", DATEADD(MILLISECOND, "projectdet"."ACTUALSTARTTIME" % 1000, DATEADD(SECOND, "projectdet"."ACTUALSTARTTIME" / 1000, '19700101')) AS "Actual start", DATEADD(MILLISECOND, "projectdet"."ACTUALENDTIME" % 1000, DATEADD(SECOND, "projectdet"."ACTUALENDTIME"/ 1000, '19700101')) AS "Actual end", DATEADD(MILLISECOND, "projectdet"."PROJECTEDEND" % 1000, DATEADD(SECOND, "projectdet"."PROJECTEDEND" / 1000, '19700101')) AS "Projected On","projectsitename"."NAME" AS "Site",esti.ESTIMATEDHOURS AS "Estimated Hours",esti.ACTUALTASKHOURS AS "Actual Hours",esti.ESTIMATEDCOST AS "Estimated Cost ($)",esti.TOT_TASKHOURS_COST AS "Actual Cost($)",dept."deptname" AS "Department"
FROM "ProjectDetails" "projectdet"
LEFT JOIN "ProjectTypeDefinition" "projecttypedef" ON "projectdet"."PROJECTTYPEID" = "projecttypedef"."PROJECTTYPEID"
LEFT JOIN "SDUser" "projectownerdet" ON "projectdet"."OWNERID" = "projectownerdet"."USERID"
LEFT JOIN "AaaUser" "projectowner" ON "projectownerdet"."USERID" = "projectowner"."USER_ID"
LEFT JOIN "PriorityDefinition" "projectprior" ON "projectdet"."PRIORITYID" = "projectprior"."PRIORITYID"
LEFT JOIN "ProjectStatus" "projectstatus" ON "projectdet"."STATUSID" = "projectstatus"."STATUSID"
LEFT JOIN "SDUser" "projectcreatedbydet" ON "projectdet"."CREATEDBY" = "projectcreatedbydet"."USERID"
LEFT JOIN "AaaUser" "projectcreatedby" ON "projectcreatedbydet"."USERID" = "projectcreatedby"."USER_ID"
LEFT JOIN "SiteDefinition" "projectsite" ON "projectdet"."SITEID" = "projectsite"."SITEID"
LEFT JOIN "SDOrganization" "projectsitename" ON "projectsite"."SITEID" = "projectsitename"."ORG_ID"
LEFT JOIN ProjectEstimations esti ON "projectdet"."PROJECTID" = "esti"."PROJECTID"
LEFT JOIN DepartmentDefinition dept ON "projectdet"."departmentid" = "dept"."deptid"

Step 5:

Enter Project details as the table name. Make sure that the datatypes match the column data.

Click next to schedule the import.

Step 6:

Repeat the steps for importing rest of 2 queries (Project tasks and Project worklogs). The queries are listed below,

Project Tasks query:

SELECT "taskdet"."TASKID" AS "Task ID","taskdet"."TITLE" AS "Title","taskcreatedby"."FIRST_NAME" AS "Created By","taskowner"."FIRST_NAME" AS "Owner","taskdet"."PER_OF_COMPLETION" AS "Percentage Of Completion","taskdet"."ADDTIONAL_COST" AS "Additional Cost", DATEADD(MILLISECOND, "taskdet"."CREATEDDATE" % 1000, DATEADD(SECOND, "taskdet"."CREATEDDATE" / 1000, '19700101')) AS "Created Date", DATEADD(MILLISECOND,"taskdet"."SCHEDULEDSTARTTIME" % 1000, DATEADD(SECOND, "taskdet"."SCHEDULEDSTARTTIME" / 1000, '19700101')) AS "Scheduled Start Time", DATEADD(MILLISECOND, "taskdet"."SCHEDULEDENDTIME" % 1000, DATEADD(SECOND, "taskdet"."SCHEDULEDENDTIME" / 1000, '19700101')) AS "Scheduled End Time", DATEADD(MILLISECOND, "taskdet"."ACTUALSTARTTIME" % 1000, DATEADD(SECOND, "taskdet"."ACTUALSTARTTIME"/ 1000, '19700101')) AS "Actual Start Time", DATEADD(MILLISECOND, "taskdet"."ACTUALENDTIME" % 1000, DATEADD(SECOND,"taskdet"."ACTUALENDTIME" / 1000, '19700101')) AS "Actual End Time","taskdet"."MODULE" AS "Module","taskprior"."PRIORITYNAME" AS "Priority","taskstatus"."STATUSNAME" AS "Task Status","tasktype"."TASKTYPENAME" AS "Task Type","taskgroup"."QUEUENAME" AS "Group","wotask"."WORKORDERID" AS "RequestID","wotask"."TITLE" AS "Request Title","taskproject"."PROJECTID" AS "Project Id","taskproject"."TITLE" AS "Project Title","taskmilestone"."MILESTONEID" AS "Milestone Id","taskmilestone"."TITLE" AS "Milestone Title","taskdet"."ISESCALATED" AS "Is Escalated","taskdet"."ISOVERDUE" AS "Overdue Status",dept."deptname" AS "Department"
FROM "TaskDetails" "taskdet"
LEFT JOIN "AaaUser" "taskcreatedby" ON "taskdet"."CREATEDBY" = "taskcreatedby"."USER_ID"
LEFT JOIN "SDUser" "taskownersdu" ON "taskdet"."OWNERID" = "taskownersdu"."USERID"
LEFT JOIN "AaaUser" "taskowner" ON "taskownersdu"."USERID" = "taskowner"."USER_ID"
LEFT JOIN "PriorityDefinition" "taskprior" ON "taskdet"."PRIORITYID" = "taskprior"."PRIORITYID"
LEFT JOIN "StatusDefinition" "taskstatus" ON "taskdet"."STATUSID" = "taskstatus"."STATUSID"
LEFT JOIN "TaskTypeDefinition" "tasktype" ON "taskdet"."TASKTYPEID" = "tasktype"."TASKTYPEID"
LEFT JOIN "QueueDefinition" "taskgroup" ON "taskdet"."GROUPID" = "taskgroup"."QUEUEID"
LEFT JOIN "WorkOrderToTaskDetails" "wototaskdet" ON "taskdet"."TASKID" = "wototaskdet"."TASKID"
LEFT JOIN "WorkOrder" "wotask" ON "wototaskdet"."WORKORDERID" = "wotask"."WORKORDERID"
LEFT JOIN "TaskToProjects" "projtotaskdet" ON "taskdet"."TASKID" = "projtotaskdet"."TASKID"
LEFT JOIN "ProjectDetails" "taskproject" ON "projtotaskdet"."PROJECTID" = "taskproject"."PROJECTID"
LEFT JOIN "TaskToProjects" "miletotaskdet" ON "taskdet"."TASKID" = "miletotaskdet"."TASKID"
LEFT JOIN "MileStoneDetails" "taskmilestone" ON "miletotaskdet"."MILESTONEID" = "taskmilestone"."MILESTONEID"
LEFT JOIN DepartmentDefinition dept ON "taskproject"."departmentid" = "dept"."deptid"
where "projtotaskdet"."PROJECTID" IS NOT NULL

Project Worklog query:

SELECT "ChargesTable"."CHARGEID" AS "ChargeID",
       "ChargesTable"."DESCRIPTION" AS "Description",
       "ChargesTable"."TIMESPENT" AS "Time Spent",
       "ChargesTable"."TECH_CHARGE" AS "Tech Charge",
       "ChargesTable"."OTHER_CHARGE" AS "Other Charge",
       "ChargesTable"."TOTAL_CHARGE" AS "Total Charge",
   DATEADD(MILLISECOND, "ChargesTable"."CREATEDTIME" % 1000, DATEADD(SECOND, "ChargesTable"."CREATEDTIME" / 1000, '19700101')) AS "Created date",
       DATEADD(MILLISECOND, "ChargesTable"."TS_STARTTIME" % 1000, DATEADD(SECOND, "ChargesTable"."TS_STARTTIME" / 1000, '19700101')) AS "Start Time",
       DATEADD(MILLISECOND, "ChargesTable"."TS_ENDTIME" % 1000, DATEADD(SECOND, "ChargesTable"."TS_ENDTIME" / 1000, '19700101')) AS "End Time",
       "TaskToProjects".PROJECTID as "Project ID",
       "TaskToCharge"."TASKID" AS "TaskID",
       "CreatedBy"."FIRST_NAME" AS "Created By",
       "Technician"."FIRST_NAME" AS "Technician",
       "WorkLog_Fields"."UDF_CHAR1" AS "Additional Fied",
   "DepartmentDefinition"."deptname" AS "Department"
FROM "ChargesTable"
LEFT JOIN "TaskToCharge" ON "ChargesTable"."CHARGEID"="TaskToCharge"."CHARGEID"
LEFT JOIN "TaskToProjects" ON "TaskToProjects".TASKID = TaskToCharge.TASKID
LEFT JOIN "AaaUser" "CreatedBy" ON "ChargesTable"."CREATEDBY"="CreatedBy"."USER_ID"
LEFT JOIN "AaaUser" "Technician" ON "ChargesTable"."TECHNICIANID"="Technician"."USER_ID"
LEFT JOIN "WorkLog_Fields" ON "ChargesTable"."CHARGEID"="WorkLog_Fields"."WORKLOGID"
LEFT JOIN "ProjectDetails" ON "TaskToProjects"."PROJECTID" = "ProjectDetails"."PROJECTID"
LEFT JOIN DepartmentDefinition ON "ProjectDetails"."departmentid" = "DepartmentDefinition"."deptid"
Where "TasktoProjects"."PROJECTID" is not null

Step 7:

Open 'Projects task' table and click 'Project ID' column. Now choose 'Change to Lookup Column' from the 'Column Properties' drop down menu.

Step 8:

Choose 'Project id' under 'Project Details' and click OK.

Step 9:

Open 'Projects Worklog' table and click 'Project ID' column. Now choose 'Change to Lookup Column' from the 'Column Properties' drop down menu.

Step 10:

Look up the 'Project id' column under 'Project Details' and click OK.

Step 11:

Open 'Projects Worklog' table and click 'TaskID' column. Now choose 'Change to Lookup Column' from the 'Column Properties' drop down menu.

Look up the 'Taskid' column under 'Projects Task' and click OK

Step 12:

Now create reports from Project details table.