Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Earned Value Report With CRYSTAL REPORT 11.2

13 replies [Last post]
Fri, 2009-09-25 20:50
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
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.




Top

Replies

Mon, 2009-10-26 04:54
#1
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
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
Top
Mon, 2009-10-26 04:24
#2
D Artagnan
User offline. Last seen 2 years 19 weeks ago. Offline
Joined: 19 Sep 2008
Posts: 207
Groups: Civil Engineering, GPC Qatar
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...
Top
Mon, 2009-10-26 04:00
#3
D Artagnan
User offline. Last seen 2 years 19 weeks ago. Offline
Joined: 19 Sep 2008
Posts: 207
Groups: Civil Engineering, GPC Qatar
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
Top
Mon, 2009-10-12 00:08
#4
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
Top
Sun, 2009-10-11 21:45
#5
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
SPI: (SCHEDULE ANALYSIS AND FORECASTING
EVM Performance)


Top
Sun, 2009-10-04 00:46
#6
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
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.



Top
Fri, 2009-10-02 01:52
#7
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
Here is the Real Ver.


Top
Fri, 2009-10-02 01:43
#8
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
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
Top
Sun, 2009-09-27 01:10
#9
Arnold Puy
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 25 Apr 2007
Posts: 1147
Groups: GPC Qatar, Pinoy Planning Planet Community
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
Top
Sat, 2009-09-26 17:26
#10
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
Hey Arnold,

when you typed this (1. dbo.TASKFIN) you meant--> dbo.TASKSUMFIN (correct)...just checking...
Top
Sat, 2009-09-26 02:53
#11
Arnold Puy
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 25 Apr 2007
Posts: 1147
Groups: GPC Qatar, Pinoy Planning Planet Community
Hi Jose,

You are most welcome.

Regards,

Arnold
Top
Sat, 2009-09-26 02:32
#12
Jose Ramirez
User offline. Last seen 12 years 5 weeks ago. Offline
Joined: 6 Sep 2008
Posts: 105
Groups: None
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...
Top
Sat, 2009-09-26 02:04
#13
Arnold Puy
User offline. Last seen 9 years 24 weeks ago. Offline
Joined: 25 Apr 2007
Posts: 1147
Groups: GPC Qatar, Pinoy Planning Planet Community
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-xerFil...

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
Top