LiveReport Query: agreed but not satisfied

Description
Vgt4a1
Posts: 2
Joined: Wed Apr 04, 2018 1:52 pm

LiveReport Query: agreed but not satisfied

Postby Vgt4a1 » Thu Apr 05, 2018 6:44 am

Hello,

i'd like to create a LiveReport-Page where i can show all Requirements in a table, which have the status "agreed" but are not liked with other workitems, which satisfy them.

Status "agreed" is pretty easy, using the Lucene query "status:agreed".
I think for the satifyed-filter i have to use a SQL-Query like

Code: Select all

STRUCT_WORKITEM_LINKEDWORKITEMS.C_ROLE = 'is satisfied by'

but i don't know how to do that.

Jürgen
Posts: 26
Joined: Tue Sep 12, 2017 1:02 pm

Re: LiveReport Query: agreed but not satisfied

Postby Jürgen » Mon Apr 09, 2018 10:46 am

Hi

we are using the macro below to find work items without specific links.
It should be possible to adapt this to filter also for the work item status, although I don't have the time to do it right now.


------snip

1 Traceability Analysis
1.1 Workitems without traces (of a specific link role)
Select "Check Links to lower level" if you want to find missing children, else for missing parents.

Example: Select "Product Specification", "refines" and check the box to find all PS, which do not have any child linked with the named role.


{parameter:wi_type|type=enum:workitem-type}
{parameter:link_role|type=enum:workitem-link-role|value=derives_from}
{parameter:back_link|type=boolean}

{parameter-form}
<table>
<tr><th>WI-Type</th><th>Linkrole</th><th>Check links to lower level</th></tr>
<tr><td>{parameter-editor:wi_type}</td>
<td>{parameter-editor:link_role}</td>
<td>{parameter-editor:back_link}</td>
</tr></table>
{parameter-form-submit}
{parameter-form-save}
{parameter-form}

#set($wi_type= $pageParameters.getAsString("wi_type"))
#set($link_role= $pageParameters.getAsString("link_role"))
#set($back_link= $pageParameters.getAsString("back_link"))

#if($back_link.equals("true"))
#set($LINK_COLUMN="L.FK_URI_WORKITEM")
#else
#set($LINK_COLUMN="L.FK_URI_P_WORKITEM")
#end
{workitems: sqlQuery=
SELECT DISTINCT W.C_PK
FROM WORKITEM W INNER JOIN PROJECT P
ON W.FK_PROJECT = P.C_PK
WHERE P.C_ID='$page.project'
AND W.C_TYPE='$wi_type'
AND W.C_PK NOT IN
(
SELECT DISTINCT WI.C_PK
FROM WORKITEM WI
INNER JOIN PROJECT PR
ON WI.FK_PROJECT = PR.C_PK
LEFT JOIN STRUCT_WORKITEM_LINKEDWORKITEMS L
ON WI.C_URI = $LINK_COLUMN
WHERE
PR.C_ID = '$page.project' AND
WI.C_TYPE='$wi_type' AND
L.C_ROLE='$link_role'
)
ORDER BY W.C_PK
| fields=id, title, module, author
}

Vgt4a1
Posts: 2
Joined: Wed Apr 04, 2018 1:52 pm

Re: LiveReport Query: agreed but not satisfied

Postby Vgt4a1 » Mon Apr 09, 2018 10:51 am

Hi Jürgen,
thanks for your reply!
My post wasn't activated by the adminestrators before so I could'nt post, that I allready found a soulution which works for me:

Code: Select all

--define your search criteria, 'group by' statements, etc.
and WORKITEM.C_STATUS = 'agreed'
and not exists (select WORKITEM2.C_PK from WORKITEM WORKITEM2, STRUCT_WORKITEM_LINKEDWORKITEMS LINK where WORKITEM2.C_TYPE = 'XXXrequirement' and LINK.C_ROLE = 'satisfies' and LINK.FK_WORKITEM = WORKITEM.C_PK and LINK.FK_P_WORKITEM = WORKITEM2.C_PK )
and not exists (select WORKITEM2.C_PK from WORKITEM WORKITEM2, STRUCT_WORKITEM_LINKEDWORKITEMS LINK where WORKITEM2.C_TYPE = 'YYYrequirement' and LINK.C_ROLE = 'satisfies' and LINK.FK_WORKITEM = WORKITEM.C_PK and LINK.FK_P_WORKITEM = WORKITEM2.C_PK )


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 2 guests