Skip to content
ProjectControls.com
Oracle Primavera - PM6

Earned Value Report With CRYSTAL REPORT 11.2

In the Open Standard
Jose RamirezMember for 18 years 1 month
Created
13
6
0
Hey, Arnold Puy (Hope you can help with this, thanks in adv.)

Earned Value Cost Report

I am trying to create a report (Earned Value Cost Report) using Crystal Report 11.2 (yes, i know i can do it via Primavera, but the i wanna do it with crystal report)

I am linking Crystal Report  PMSDK 6.2.1  MS SQL

1. My problem is I don’t know exactly which tables to extract the data from in SQL to get the following information (AC, PV, EV, etc):

Am Thinking the following tables below:

SQL TABLE

dbo.TASKRSRC
dbo.TASKSUM
dbo.WBSRSRC

2. Which columns to select from the tables: To Get SOME of the following information:

AC Actual Cost (to date)
BAC Budget At Completion (planned budget)
CPI Cost Performance Index (CPI = EV / AC)
CV Cost Variance (CV = EV - AC)
EAC Estimate At Completion (Formulas are listed below)
ETC Estimate To Complete (ETC = EAC - AC)
EV Earned Value (EV = PV * %Complete)
PV Planned Value: BAC for this task or CAP
SPI Schedule Performance Index (SPI = EV / PV)
SV Schedule Variance (SV = EV - PV)
VAC Variance At Completion (VAC = BAC - EAC)


Cost Performance Index CPI = EV / AC
Cost Variance CV = EV - AC
Earned Value (for a task) EV = PV * %Complete
Estimate To Complete ETC = EAC - AC
Schedule Performance Index SPI = EV / PV
Schedule Variance SV = EV - PV
Variance At Completion VAC = BAC - EAC
% Schedule Variance SV = ( EV - PV ) / PV

BTW the PMSDK and CRYSTAL REPORT Database connections are perfect. Now is just getting familiar with all the TABLES and Columns in MS SQL for primavera.




Save Follow Follow forum Report
Reply New topic

13 replies

Oldest first
  1. 0
    Arnold Puy ·
    #1
    Hi Jose,

    I would suggest using the “PATTLNK_632006_536-xerFileParser&Builder” in order for you to find easily what you really want and this is only intended for individual project. You can download it here:

    http://knowledgebase.primavera.com/attachment/PATTLNK_782008_1412-xerFileParserBuilder.xls

    Regarding to your query, you can find the list of the above mainly in the following:

    1. dbo.TASKFIN
    2. dbo. TASKRSRC
    3. dbo.TASK

    Hope this helps.

    Regards,

    Arnold
    Report
  2. 0
    Jose Ramirez ·
    #2
    Hey Arnold, Thank you so much...Yeah, def. this is what i was looking for. I don’t know why i didn’t used this .xls file for this purpose. I have had this xerFileParserBuilder.xls for while and i never thought about using it to see what info. the columns on the tables contained. thank you again, and now back to making my report...
    Report
  3. 0
    Arnold Puy ·
    #3
    Hi Jose,

    You are most welcome.

    Regards,

    Arnold
    Report
  4. 0
    Jose Ramirez ·
    #4
    Hey Arnold,

    when you typed this (1. dbo.TASKFIN) you meant--> dbo.TASKSUMFIN (correct)...just checking...
    Report
  5. 0
    Arnold Puy ·
    #5
    Hi Jose,

    dbo.TASKFIN & dbo.TASKSUMFIN are different tables but the content are related to one another.

    For more details and explanation please check below for documentation:

    C:\Program Files\Common Files\Primavera Common\PMSDK\Doc\GeneralTables&Fields.htm


    Regards,

    Arnold
    Report
  6. 0
    Jose Ramirez ·
    #6
    Here is my first Earned Value Report With Crystal Report 11.2 and Primavera...Wow it’s easier to make this in Excel..lol..This is just a test. Let me know what else i should do to add a little more oomph!!! This is also a test project n i haven’t started anything on this project....

    EV
    Report
  7. 0
    Jose Ramirez ·
    #7
    Here is the Real Ver.


    Report
  8. 0
    Jose Ramirez ·
    #8
    This Summary Report Contains EV Periodic Progress % & Cumulative Progress %. This Report is based on Fin Period Work Perf: Earned value for the Project in the period.



    Report
  9. 0
    Jose Ramirez ·
    #9
    SPI: (SCHEDULE ANALYSIS AND FORECASTING
    EVM Performance)


    Report
  10. 0
    Jose Ramirez ·
    #10
    Report
  11. 0
    D Artagnan ·
    #11
    when i try to link primavera database to ms access and run query of dbo.TASK, nothing comes out. When i run dbo.PROJECT i see all the projects...

    I am hoping for some help. Thanks in advance
    Report
  12. 0
    D Artagnan ·
    #12
    i got it now...

    my problem now is to have a crosstab report, if that’s how it’s called.

    this is how i want it to look like.

    -------------------- (dates) ------ 2/12 ------ 2/19 ------- 2/26 -------- 3/5
    ACTUAL COST
    CUMM ACT COST
    BUDG COST
    CUMM BUDGE COST

    and make a graph out of this..

    thanks again in advance...
    Report
  13. 0
    Jose Ramirez ·
    #13
    The first tab allows you to select the type of chart. Choose Bar, and on the right side of the screen, choose the specific type of bar chart (e.g. side-by-side, stacked bar, etc.).

    On the second tab, you define the data for the graph (Figure 9). You need to provide two definitions. For the first definition (the On change of label), you need to define the division and subdivision. The division for a bar chart is the X-Axis definition (each month), and the subdivision includes any groupings within each month (the cost categories). So drag the two data columns that represent the month and cost categories into the On change of section. The second definition (show value) represents the actual data that Crystal Reports will chart, so drag the data column that contains the actual numeric data into the Show value(s) section.

    Hope this answers your Q


    chart expert
    Report

Log in or join to reply

ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.

Log inJoin free

Existing members: your original username and password still work. Forgot password?