Importing Projects Module data into Analytics Plus

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:
Login into Analytics Plus and open ServiceDesk Plus database.

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.

      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Change module data : Import into Analytics Plus

                      Change module data : Import into Analytics Plus Note: When Analytics Plus officially supports Change module integration Out of the box, then these imported tables and reports might get overwritten. Please ensure to name the table as 'Change_localDB'. ...

                    • Problem Module Data import

                      Importing Problem data into Analytics Plus:  You could log into the Analytics Plus 'ME ServiceDesk Plus Analytics' database and start creating these two tables using the below queries. Check this link for steps to import data from Local DB. Problem ...

                    • How to import Survey data from ServiceDesk Plus to Analytics Plus?

                      Helpdesk surveys help you identify how happy your customers really are with your services. A recent research revealed that 96% of unhappy customers don't complain but, 91% of those unhappy customers leave and never come back. That is sad, isn't it?   ...

                    • Steps to import Archived Request into Analytics Plus

                      By default, we don't import archived request details into Analytics Plus but we are working on a model to import these details out of the box and will be available in one of our future builds. Meanwhile, please follow the below text instructions Step ...

                    • How to import description and resolution fields from ServiceDesk Plus to Analytics Plus?

                      Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...

                      Related Products