1 / 17

Reports – Tips and Tricks

Reports – Tips and Tricks. Bill Olford billo@epkgroup.com. Agenda. Alternatives Management Graphics Reports SRS in SQL Server EPK Data Creating a simple report. Alternatives. Management Graphics. New graphics engine with v4.2 Save template Improved graphics. Reports.

xandy
Download Presentation

Reports – Tips and Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Reports – Tips and Tricks Bill Olford billo@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 • 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

  10. Cost Type Data • May require Publish • By Cost Category/Role in views • Don’t 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 <> 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 • Let’s 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 = @ID)

  16. Preview

  17. Questions? • Hold hard questions for advanced reports session by Al La Garde

More Related