Cadet 1st Class
Cadet 1st Class
223 views

Populate Number of Active issue in Project

Jump to solution

Hi Team,

I want to show that how many issue are In Progress in a Project just before Project closer status.
For this i have created a field " No.Of active issue" and I created one rule which will show no of active issue and populate in " No.Of active issue" field.

Below is SQL Query:
SELECT
NVL(COUNT(kr.request_id ),0), NVL(COUNT(kr.request_id ),0)

FROM
kcrt_request_types krt ,
kcrt_requests kr,
knta_references krf,
pm_projects pp,
kcrt_fg_pfm_project kfpp

WHERE
kr.request_type_id = krt.request_type_id
AND kr.request_id = krf.parameter1
and krf.original_source_id =pp.project_id
and pp.project_id = kfpp.PRJ_PROJECT_ID
AND kr.status_code IN ('IN_PROGRESS','NEW')
AND krt.request_type_name ='Project Issue'
AND kfpp.request_id = '[REQ.REQUEST_ID]' ;       

( In place of '[REQ.REQUEST_ID]' given Some Project number to test that Query is giving correct output or not)



Query is giving output as expected (checked in Admin console) but when i adding above SQL query in Rules i am getting error (attached error screen shot and Rule screen shot).

Can you please help me to find out if i am missing something.

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Hi Roshan,

Can you check in the PPM Server logs if you can find more info about the error? There's nothing useful in the error message you're getting.

FYI I have no problem to get this to work on my environement with the following request, which will just count the total number of references per project:

SELECT
NVL(COUNT(kr.request_id ),0), NVL(COUNT(kr.request_id ),0)
FROM
kcrt_request_types krt ,
kcrt_requests kr,
knta_references krf,
pm_projects pp,
kcrt_fg_pfm_project kfpp
WHERE
kr.request_type_id = krt.request_type_id
AND kr.request_id = krf.parameter1
and krf.original_source_id =pp.project_id
and pp.project_id = kfpp.PRJ_PROJECT_ID
AND kfpp.request_id = '[REQ.REQUEST_ID]'

 

The field in the project details is configured as the same way as on your screenshot.

One important last note: I managed to get the same error as you did get by just adding ";" at the end of my SQL query in the Rule window. Do you have a ";" at the end of your rule SQL by any chance? If you do, please remove it and try again.

Cheers,

Etienne.

View solution in original post

0 Likes
1 Reply
Micro Focus Expert
Micro Focus Expert

Hi Roshan,

Can you check in the PPM Server logs if you can find more info about the error? There's nothing useful in the error message you're getting.

FYI I have no problem to get this to work on my environement with the following request, which will just count the total number of references per project:

SELECT
NVL(COUNT(kr.request_id ),0), NVL(COUNT(kr.request_id ),0)
FROM
kcrt_request_types krt ,
kcrt_requests kr,
knta_references krf,
pm_projects pp,
kcrt_fg_pfm_project kfpp
WHERE
kr.request_type_id = krt.request_type_id
AND kr.request_id = krf.parameter1
and krf.original_source_id =pp.project_id
and pp.project_id = kfpp.PRJ_PROJECT_ID
AND kfpp.request_id = '[REQ.REQUEST_ID]'

 

The field in the project details is configured as the same way as on your screenshot.

One important last note: I managed to get the same error as you did get by just adding ";" at the end of my SQL query in the Rule window. Do you have a ";" at the end of your rule SQL by any chance? If you do, please remove it and try again.

Cheers,

Etienne.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.