EPK Users Meeting

1
Reports Tips and Tricks

• Bill Olford
• billo_at_epkgroup.com

2
Agenda

• Alternatives
• Management Graphics
• Reports
• SRS in SQL Server
• EPK Data
• Creating a simple report

3
Alternatives
4
Management Graphics

• New graphics engine with v4.2
• Save template
• Improved graphics

5
Reports

• Open database
• EPK-Suite Database Tables PDF
• MSP tables from PJDB.HTM and PJSVRDB.HTM in the
  \HELP\1033 folder
• Reporting on EPK Data white paper
• Example RDLs albeit v32 vintage

6
SQL Server Reporting Services

• Design report in VS.Net
• Great formats and even better price
• Server functionality like
• Push or pull
• Cached
• Subscription
• PWA/EPK security not enforced default is to see
  all data returned

7
Database

• Get Project data from MSP tables
• Use Extract for snapshot
• easier access to custom field values
• No entity diagram
• No referential integrity
• Usually same field name for foreign keys

8
Timesheet Data
9
SQL Query

• Hours conversion
• CHG_NORMALHOURS/60000.0
• Use cached names EPK_PROJ_TASKS
• dont join to MSP project, task, or assn tables
  in case rows have been deleted
• Closed PIs stay in DB until deleted in DB Admin
• Need union for schedule, PI and NWI actuals
• Timesheet stamped with department and CN
• Dept resource belonged to that period
• CN lookup value can change later but TS value
  stays

10
Cost Type Data

• May require Publish
• By Cost Category/Role in views
• Dont double count by taking sub-totals

11
Cost Tables
12
CT Data by Resource

• CT data in Portfolio views is by Cost Cat
• Requires Publish by Resource
• Stored in EPKR_ tables

13
Resource Planning

• Resource Plans Analyzer
• Resource plan row
• Commitment CMT_STATUS 256
• Dates
• Rate by project or even by resource commitment
• Revenue edited
• CMT_TOTAL_COST ltgt CMT_CALC_TOTAL_COST

14
Simple Report

• Layout on paper
• Select and sort fields
• Gather data with SQL queries
• Format with SRS
• Distribute with PWA custom menu
• Lets do it for a PI listing

15
SQL for PI Listing

• select EP.PROJECT_ID AS Item ID,
  EP.PROJECT_NAME AS Name, EP.PROJECT_START_DATE AS
  Planned Start, EP.PROJECT_FINISH_DATE AS
  Planned Finish, MWP.PROJ_NAME AS Linked
  Project, EP.PROJECT_STAGE_ID AS Stage ID,
  ES.STAGE_NAME AS Stage, MWR.RES_NAME AS Stage
  Owner, MWR.WRES_EMAIL AS eMail, EPI.COST_01 AS
  Budget Total, EPI.COST_09 AS NPV, EPI.NUMBER_01
  AS Discounted ROI, EPI.NUMBER_02 AS Corporate
  Image, EPI.NUMBER_03 AS Cost Reduction,
  EPI.NUMBER_04 AS Employee Morale, EPI.NUMBER_05
  AS Growth, EPI.NUMBER_06 AS Infrastructure,
  EPI.NUMBER_07 AS Regulatory, EPI.NUMBER_08 AS In
  Business, EPI.NUMBER_09 AS Weighted Rating,
  MWR1.RES_NAME AS Exec Sponsor, EPI.URL_01 AS
  Team Web Site
• from EPKP_PROJECTS EP
• join EPKP_STAGES ES ON
  EP.PROJECT_STAGE_ID ES.STAGE_ID
• left join MSP_WEB_PROJECTS MWP ON EP.WPROJ_ID
  MWP.WPROJ_ID
• left join MSP_WEB_RESOURCES MWR ON
  EP.PROJECT_OWNER MWR.WRES_ID
• join EPKP_PROJECT_INFOS EPI ON EP.PROJECT_ID
  EPI.PROJECT_ID
• left join MSP_WEB_RESOURCES MWR1 ON EPI.RES_01
  MWR1.WRES_ID
• where (EP.PROJECT_ID _at_ID)

16
Preview
17
Questions?

• Hold hard questions for advanced reports session
  by Al La Garde