Transcript Reports – Tips and Tricks - EPK

Reports – Tips and Tricks
Bill Olford
[email protected]
EPK Users Meeting
Jan 23-25, 2007
Agenda
•
•
•
•
•
•
Alternatives
Management Graphics
Reports
SRS in SQL Server
EPK Data
Creating a simple report
EPK Users Meeting
Jan 23-25, 2007
Alternatives
Pros
Cons
Views
Live as of last publish
Slow if too much data is
displayed
Export to
xml/Excel
Many formats and analysis
tools available in Excel
Snapshot at point in time
Reusability
Management
Graphics
Available in IE session. Can
save format
Cost category/role level
Portfolio Analyzer Timesheet data available. Can
create own cube.
PI and NWI data not available
Needs cube. Snapshot so not live.
Report from
DB tables
Faster when large amounts of
data. Can integrate into PWA
with custom menu
Must account for data security in
the report specification
Report from DB
including extract
tables
Simplified structure of data
tables. Timesheet data
aggregated by fiscal period
Snapshot of data at time extract
run.
EPK Users Meeting
Jan 23-25, 2007
Management Graphics
• New graphics engine with v4.2
• Save template
• Improved
graphics
EPK Users Meeting
Jan 23-25, 2007
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
EPK Users Meeting
Jan 23-25, 2007
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
EPK Users Meeting
Jan 23-25, 2007
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
EPK Users Meeting
Jan 23-25, 2007
Timesheet Data
EPK Users Meeting
Jan 23-25, 2007
SQL Query
• Hours conversion
– CHG_NORMALHOURS/60000.0
• Use cached names EPK_PROJ_TASKS
– don’t 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
EPK Users Meeting
Jan 23-25, 2007
Cost Type Data
• May require Publish
• By Cost Category/Role in views
• Don’t double count by taking sub-totals
EPK Users Meeting
Jan 23-25, 2007
Cost Tables
EPK Users Meeting
Jan 23-25, 2007
CT Data by Resource
• CT data in Portfolio views is by Cost Cat
• Requires Publish by Resource
• Stored in EPKR_* tables
EPK Users Meeting
Jan 23-25, 2007
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 <> CMT_CALC_TOTAL_COST
EPK Users Meeting
Jan 23-25, 2007
Simple Report
•
•
•
•
•
•
Layout on paper
Select and sort fields
Gather data with SQL queries
Format with SRS
Distribute with PWA custom menu
Let’s do it for a PI listing
EPK Users Meeting
Jan 23-25, 2007
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 = @ID)
EPK Users Meeting
Jan 23-25, 2007
Preview
EPK Users Meeting
Jan 23-25, 2007
Questions?
• Hold hard questions for advanced reports
session by Al La Garde
EPK Users Meeting
Jan 23-25, 2007