Reporting with SQL Reporting Services

[Ms. Smith]

Hello. We are looking to create reports using SQL Reporting Services.
We want to report similar tasks in different project files
cumulatively.

We have SQL Reporting Services, and according to our IT person, one of
the first steps is to create queries in SQL that will query the tables
to get the information we need to go into the reporting module.
However, this has been a very hard process. Mainly because:

1 - We have about 33 different project files
2 - The data in the "actual work" fields in the tables are recorded as
some kind of seconds that we are unsure of (i.e. 1 hour shows in the
database as 160000 or some other number like that).

Are we starting this process the wrong way? What is the correct way to
get this ball rolling?

Thanks!

 

[Chak]

Hello. We are looking to create reports using SQL Reporting Services.
We want to report similar tasks in different project files
cumulatively.

We have SQL Reporting Services, and according to our IT person, one of
the first steps is to create queries in SQL that will query the tables
to get the information we need to go into the reporting module.
However, this has been a very hard process. Mainly because:

1 - We have about 33 different project files
2 - The data in the "actual work" fields in the tables are recorded as
some kind of seconds that we are unsure of (i.e. 1 hour shows in the
database as 160000 or some other number like that).

Are we starting this process the wrong way? What is the correct way to
get this ball rolling?

Thanks!

Hi Smith,

Which version of Project Server you are using? Is it 2007 or 2003?

I can provide SQL queries to report the project data.

Thanks
Chak

 

[Chak]

Hello. We are looking to create reports using SQL Reporting Services.
We want to report similar tasks in different project files
cumulatively.

We have SQL Reporting Services, and according to our IT person, one of
the first steps is to create queries in SQL that will query the tables
to get the information we need to go into the reporting module.
However, this has been a very hard process. Mainly because:

1 - We have about 33 different project files
2 - The data in the "actual work" fields in the tables are recorded as
some kind of seconds that we are unsure of (i.e. 1 hour shows in the
database as 160000 or some other number like that).

Are we starting this process the wrong way? What is the correct way to
get this ball rolling?

Thanks!

Hi Smith,

Which version of Project Server you are using? Is it 2007 or 2003?

**************************************************************

If you are using Project Server 2007, please use the following SQL:

SELECT MSP_EpmProject.ProjectName AS ProjectName,
MSP_EpmTask.TaskStartDate AS Start,
MSP_EpmTask.TaskFinishDate AS Finish,
MSP_EpmTask.TaskPercentCompleted AS '%Complete',
MSP_EpmTask.TaskWork AS WorkinHrs,
MSP_EpmTask.TaskActualWork AS ActualWorkinHrs,
MSP_EpmTask.TaskDuration/8 AS DurationDays,
MSP_EpmTask.TaskPercentWorkCompleted AS '%WorkComplete',
MSP_EpmResource.ResourceName AS Owner
FROM MSP_EpmTask INNER JOIN
MSP_EpmProject ON MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID
INNER JOIN
MSP_EpmResource ON MSP_EpmProject.ProjectOwnerResourceUID =
MSP_EpmResource.ResourceUID
WHERE (MSP_EpmTask.TaskOutlineLevel = 0) AND
(MSP_EpmTask.TaskOutlineNumber = 0)

*****************************************************************************


If you are using Project Server 2003, please use the following URL to
view SQL scrips:

http://www.epmcentral.com/pjadmin/dbscripts-msps3.php

*********************************************************

Thanks
Chak
http://www.epmcentral.com

 

[Ms. Smith]

Hello Chak. Thanks so much for responding!

Thanks for these scripts. I'm using 2003.

Exactly what are these scripts doing? I want to make sure I understand
their purpose.

Thanks again!

Alexia

 

[Chak]

Hello Chak. Thanks so much for responding!

Thanks for these scripts. I'm using 2003.

Exactly what are these scripts doing? I want to make sure I understand
their purpose.

Thanks again!

Alexia

http://www.epmcentral.com-Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hello Alexia,

From your original posting, I understood you are planning to use SQL
Reporting Services for reporting. Please show these scripts to your
IT person who will be doing the reports. Depends on reports you are
planning to have, these SQL scripts will provide most of the
information related resource assignments, project summary, timesheet
details etc.

I mentioned the purpose of each script with SQL query.

Please let me know, if you have any questions.

Thanks
Chak
http://www.epmcentral.com

 

[Ms. Smith]

Hell Chak. I will give this to my IT person today. I just wanted to
know if this would help for sure with at least my second issue, where
the number of seconds in the table fields are hard to convert into
hours.

2 - The data in the "actual work" fields in the tables are recorded
as
some kind of seconds that we are unsure of (i.e. 1 hour shows in the
database as 160000 or some other number like that).

Thanks!

Alexia

 

[Chak]

Hell Chak. I will give this to my IT person today. I just wanted to
know if this would help for sure with at least my second issue, where
the number of seconds in the table fields are hard to convert into
hours.

2 - The data in the "actual work" fields in the tables are recorded
as
some kind of seconds that we are unsure of (i.e. 1 hour shows in the
database as 160000 or some other number like that).

Thanks!

Alexia









- Show quoted text -

Hello Alexia

MS Project Server 2003 database stores work values internally in the
following format.

work * minutes * 1000:

**********************************************************************
Example:

If you have 8 hours of work, it will be stored in the database as
follows:

8 * 60 * 1000 = 480000

i.e. You are seeing 8 hours as 480000

You have to divide this value by 60000 to get the work value

i.e. 480000/60000 = 8 hours


Answer to your question, your actual work is 160000/60000 = 2.6 hours

***********************************************************************************

FYI: All my scripts already have this conversion process. If you run
those scripts on your database, you will get accurate hours for
reports.

I hope this helps.


Thanks
Chak