Reporting Services

[DVDA]

has anyone used SQL reporting services in conjunction
with Project Server 2003 DB? Care to share your
experiences?

thanks

 

[jerod]

I have been working with this off and on for a few weeks now.
So far I really like reporting services. So far, I hate trying to extract
status report responses from the DB.

The responses to the status reports are stored in MSP_WEB_STATUS_RESPONSES
in an NTEXT field called WRESP_TEXT as XML.
While I can appreciate storing the consept of storing each status report as
an XML string, geting the data out has been quite a challenge.
Currently I built an app that parses each row of that table and builds a new
table.
This is not a solution I am happy with but so far it is all I have.
I have found almost no information on this so I am in "trial and ERROR"
mode.
If anybody has any ideas on how to transform the XML in these rows to
useable data I would love to hear them.

If you are just starting out, here are the steps I took to at least see the
data.
open Qurery Analyzer.
change to the ProjectServer database
select Tools - Options - Results
change the Maximim characters per column to 8000
uncheck print column headers
press OK
change to results in Text (CTRL+T)
and run this query

select '<ROOT>' as Resp
union all
select wresp_text as Resp from MSP_WEB_STATUS_RESPONSES
union all
Select '</ROOT>' as Resp

The root tags are in there to make make the XML well formatted so you can
change to results to file (CTRL+SHFT+F), run the query saving it as an .XML
file.

You can then open that file with Internet Explorer and see the formatted
XML.
you may have to open the file with notepad and remove the "rows affected"
line at the bottom of the file.

hth
Jerod

 

[Alain (PCubed)]

I agree with the extracting data part. I have been playing with SQL RS as
well with PServer 2k3 and like the type of reproting you can do, but the
database is poorly documented, even the included database files only talk
about tables and structure.

I would liek to know how Project Pro client writes to the tables without
having to do traces and how it maintains data integrity.

I see the value in the advance reporting capabilities, but unless you really
know your SQL, it's a pain to try and extract and report on information
unless you spend lots of time reviewing the tablse and structure.

Alain (PCubed)
www.pcubed.com