Re: Report risks and issues

From: Earl Lewis (elewis-nospam_at_nospamutah.edu)
Date: 05/11/04


Date: Tue, 11 May 2004 14:40:59 -0600

Marcos,

OK, good. So we know how you want to publish the report and the tool you
want to use to do it. Now you need to setup some ODBC connections to the
project server database and the sharepoint database. These are 2 different
databases so you need 2 different ODBC connections, 1 to each. This should
all be done from the Crystal Reports development workstation.

You'll probably need to have your SQL admin setup user accounts on each of
the databases that you need to setup ODBC connections for. You could use the
MSProjectUser credentials but that probably wouldn't be recommended. After
the ODBC connections are setup make sure that your Crystal development
environment can connect to both databases.

In my environment I only have access to our project server SQL server db so
I can't give you too much help on the Sharepoint side, but I can get you
started from the project server perspective.

In project server there's a table is called MSP_WEB_PROJECTS that has some
fields that references the sharepoint site and the risk and issue list IDs
(actually GUIDs). These are the links to the appropriate fields in the
sharepoint tables to point you to the issues and risks for each project.
You may also want to look at MSP_WEB_OBJECTS to see if that adds any clarity
to the situation. Microsoft uses it in the stored procedure
MSP_WEB_SP_QRY_GetAllIssuesForIssuesEditForm

You'll have to take it from there for the sharepoint side and figure out
which tables/fields to use to get the data out that you want. Like I said, I
don't have access to my sharepoint SQL server db so can't see the table
structure.

I'm curious what you want to do with the binary data? These are generally
the documents that have been put in shared document folders or attached to
issues/risks. Working with these via Crystal is probably not recommended.
Although, I don't know the ins/outs of Crytal. It very well could have a way
to deal with these. I just can't comment on how to do it.

Hope that helps get you started.

Big hint on the SQL server stuff: look (DON'T EDIT) through the views and
stored procedures on the project server to see how Microsoft hooks things
together and emulate it to get the results you want.

Earl

"Marcos Pinto" <anonymous@discussions.microsoft.com> wrote in message
news:115DF046-2E2C-4ADD-B444-A28630C5FF91@microsoft.com...
> HI Earl,
>
> I want to use cristal reports to get the values from the database and
create a report with all risks and issues opened by project.
> My problem is that I don't know which tables has the information that I
need and I don't know how to get binary data from de tables/fields.
>
> Could you please, tell me where I can find this information and how to
read a binary value from the database?
>
> Thanks for your help,
> Marcos Pinto
>