Reporting of Data from Custom Lists

[Steve]

Am I right in saying that if I create a new workspace template for all
projects which contains a new list that the data is not copied over to the
reporting database like the risks, issues and deliverables data is?

If this is the case and I need to report on the new list's data must i do so
out of the published database?

Thanks in advance

 

[Andrew Lavinsky]

You are correct in your assumption. MOPS looks for four items in the workspace:
Issues, Risks, Documents, and Deliverables.

You're probably best off to write a custom SSRS report against the custom
list - like you would from any SharePoint content. That'll be easier than
trying to get that data into the Published database.

-A

 

[Brian Lukanic]

Can I ask a followup question on this topic? I am using MPS2007, Wss3.0 and
SQL 2005.....

A. I expect to report off of project-level data elements from all sorts of
data stored in the .mpp file and published to Server. Assume that there will
be a custom project field called "uniqueprojectname"

B. I also expect to have a templated Workspace associated with every
published project, which will contain a consistent set of custom lists.
Meaning that this workspace will be repatable in terms of design, data
structure, hierarchy, etc. Each Project workspace would presumably have a URL
off the server along the lines of myserver.foo.com/pwa/uniqueprojectname

C. Is it not too terribly difficult to report off data from both the
published schedule and the associated Sharepoint List content for any given
uniqueprojectname? I'm hoping that it will not require any fancy report
coding other than perhaps some joins or something. I'm not a DBA but I'm also
not unfamiliar with SQL syntax. So I just need to know that it can be done
without too much difficulty.

D. Any recommended best practices in terms of what/where/how to make use of
uniqueprojectname? I am planning to create a project-level enterprise custom
field that would house the uniqueprojectname in the .mpp, and I would make
sure to use that same uniqueprojectname as the URL for the workspace. Then,
my report would essentially be something like, "select * from Published where
uniqueprojectname = "MyProject1" and from 'sharepointtable' where URL =
"MyProject1" (this is obviously not correct syntax but you get the idea.

Any other caveats to watch out for before I start rolling my projects into
production?

Thanks
Brian

 

[jack dahlgren]

Brian,

While you CAN directly query the Sharepoint database, Microsoft does not
support this method. They want you to use the sharepoint API instead. The
reason being that sharepoint manages the database in a way which may break
your query. For example if the database gets too large, sharepoint may
create a new database and now your information is spread across another
database that you might not know exists.

There may also be migration issues down the line if something changes.

Generally it is best practice to code against the API instead of direct
queries of the database (Project Server Reporting Database excepted) because
you never know what Microsoft will change behind the scenes.

-Jack Dahlgren

 

[Brian Lukanic]

OK thanks, Jack. That seems to make sense. So are you saying that the ablity
to report off the data from these disparate sources can indeed be done, but
that is just needs to be done in a way that's different than I had thought?
Can you point me to a good resource or URL that explains how to make use of
the Sharepoint API for this purpose? As long as I can get a starting point I
can probably take it from there.

 

[Andrew Lavinsky]

Jack's definitely correct. Before you go the SSRS route however, you might
be able to mock up what you're looking for using Webparts on a blank page
in a page library. In fact, that might end up meeting your needs by combining
both MOPS and MOSS webparts - or at least provide a Proof of Concept for
your stakeholders.

-A

 

[Brian Lukanic]

Thanks Andrew. In fact that's about where I am currently, and it's sufficient
for most stakeholders. What I am looking at now is in more advanced
program/portfolio-level reporting where multiples of projects are put into a
single report. (Such as, show me all themes that are consistent across
projects, such as the "lessons learned" custom lists joined up with baseline
variance data.)

One other question: with regard to what you and Jack are recommending, is
this what I will need to install? (reporting services)
http://msdn.microsoft.com/en-us/library/bb326356.aspx And can I have the
reporting services "report server" reside on the same box as the MSP & WSS
box? My deployment scenario is essentially one box for all application
services and one box (SQL Server) that hosts the database. I don't want to
have a 3rd machine for reporting, nor do I want to put a reporting service on
the DB server.