Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

PPM Database

SOLVED
Go to solution
Highlighted
KorayKirdinli_1
Acclaimed Contributor

PPM Database

Hi i am working in a bank's IT.
We bought PPM and now my managers wat me a report from PPM database.
Every project ends the business depatmant fills a survey(questionary) about the project.
They score the project accourding to their pleasure. My manager wants to see the survey results . I could not find the table in the PPM database. I want to SELECT the resultsfrom database
Do you know the table and field names
8 REPLIES
Sascha Mohr
Esteemed Contributor

Re: PPM Database

If the survey is a request type you created, you will find most contents in the tables:
kcrt_requests
kcrt_request_details
kcrt_req_header_details
Filter in these tables for the correct request_type_id which can be found in kcrt_request_types
KorayKirdinli_1
Acclaimed Contributor

Re: PPM Database

Thanks for your reply.
I will start to lookup fields in these tables.
But some fields are i think generic and their names like PARAMETER1,2,3 etc.


KorayKirdinli_1
Acclaimed Contributor

Re: PPM Database

Can you give the fields estimated.
I could not found my select is like this is it correct:


select D.STATUS_NAME,C.FULL_NAME,B.*, A.* from ppmktprod_usr.kcrt_requests A
INNER JOIN ppmktprod_usr.kcrt_request_types B ON B.REQUEST_TYPE_ID= A.REQUEST_TYPE_ID
INNER JOIN ppmktprod_usr.knta_users C ON A.CREATED_BY = C.USER_ID
INNER JOIN ppmktprod_usr.KCRT_STATUSES D ON D.STATUS_ID = A.STATUS_ID
WHERE A.STATUS_CODE = 'CLOSED_SUCCESS' AND A.APPLICATION='elIT'
ORDER BY A.CREATION_DATE DESC
Sascha Mohr
Esteemed Contributor

Re: PPM Database

In the workbench when you open a field in a request type or request header type you will find several tab. One of them shows where the value for that field is stored. If there is no sturage tab like for the status field, these fields have there dedicated columns in the tables.
You might find things easier using a view like kcrt_requests_v because they already contain e.g. the speaking name of a status.
KorayKirdinli_1
Acclaimed Contributor

Re: PPM Database

Thanks again.
I found the topics that is related to survey.
But a request has more than one records in kcrt_requests_v view. Why there is more than one record for one request
Sascha Mohr
Esteemed Contributor

Re: PPM Database

This happens for request types with more than 50 custom fields. The lines in the view are then identified by the request ID and the "BATCH_NUMBER".
KorayKirdinli_1
Acclaimed Contributor

Re: PPM Database

Oh its good. I want to select like this.
Request,parameterQuestion,parameterAnswer

How can i join the requests.
My manager wants to see the result of each request's survey results.
For me its hard to find the result.
In screen shot i show you some of fields.
Solution

Re: PPM Database

Hi,

First you must identify the fields which you need.
Open the Workbench -> Demand Mgmt -> Request Types

Open your survey Request Type goto the fields tab. Open the field configuration of each field you need, goto the storage tab.
There you find the parameterXX and the batch number.

with this information you can build a statement like this:

select r1.request_id, r1.visible_parameter1, r1.visible_parameter2, r2.visible_parameter3 from kcrt_requests_v r1, kcrt_requests_v r2 where r1.request_type_name ='' and r1.batch_number=1 and r2.batch_number=2 and r1.request_id=r2.request_id

However it is mandentory to identify the fields your manager wants and there matching database fields.

Regards
Volker
//Add this to "OnDomLoad" event