Query to get history of Change Notice, like Created by, Number,Activity name, Assigned to, Reassigned to,

[vuchekar]

Hello All,

I am trying to build a report using Query Builder to Fectch history of change Notice like, Name, Activity, Created on, Completed on, Assigned to User,if it is Reassigned, Reassigned User, Vote etc.

Presently i am able to able to fetch details like name, Activity, Created on, Completed on, Assigned to user, if it is Reassigned (Yes or No).

but i am not able to get Reassigned user name, how i will get this.

Here is format of my desired output.

Change Notice Number	Created on	Activity Name	Role	Assigned to	Reassigned	Reassigned to User
123	10/10/14	xxxxxxx	Assignee	abc	yes	xyz
124	12/10/14	yyyyyyy	Assignee	bob	no

Please, guide us to solve this requirement.

Thanks,

Vivek

[Marco_Tosin]

Vivek,

take a look at this article I wrote http://communities.ptc.com/docs/DOC-6348

There is a lot of useful things about reporting with query builder and a section with some report on Change Management.

Hope you can find what you are looking for.

[vuchekar]

Hello Marco Tosin,

I have go through link provided by you.

I am able to fetch some data, not all, Please let us know how i can get this as below,

My Change Notice Page,

I want to fetch data from Task for change process table and process Status table as shown on page.

I am able to fetch the data from "tasks for change process", but not able to find data from "Process Status " table.

Here is report output for above change notice, we will notice that fields from "Process Status " tables are missing in reports.

Please let us know, how i can include this additional data to report.

Here is my qml file,

----------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE qml SYSTEM "/wt/query/qml/qml.dtd">

<qml>

<statement>

<query>

<select>

<column

alias="Change Notice (wt.change2.WTChangeOrder2)"

heading="Number" propertyName="number" type="java.lang.String">master&gt;number</column>

<column

alias="Change Notice (wt.change2.WTChangeOrder2)"

heading="Name" propertyName="name" type="java.lang.String">master&gt;name</column>

<object

alias="Change Notice (wt.change2.WTChangeOrder2)"

heading="Version" propertyName="versionInfo.identifier.value">

<property name="versionInfo">

<property name="identifier">

<property name="value"/>

</property>

</property>

</object>

<column alias="Work Item" heading="Role"

propertyName="role" type="wt.project.Role">role</column>

<column alias="Wf Assigned Activity"

heading="Wf Assigned Activity Name"

propertyName="name" type="java.lang.String">name</column>

<object alias="Work Item"

heading="Ownership.Owner.Full Name" propertyName="ownership.owner.fullName">

<property name="ownership">

<property name="owner">

<property name="fullName"/>

</property>

</property>

</object>

</select>

<from>

<table alias="Work Item">wt.workflow.work.WorkItem</table>

<table alias="Change Notice (wt.change2.WTChangeOrder2)">wt.change2.WTChangeOrder2</table>

<table alias="Change Task (wt.change2.WTChangeActivity2)">wt.change2.WTChangeActivity2</table>

<table alias="Wf Assigned Activity">wt.workflow.work.WfAssignedActivity</table>

<table alias="Wf Assignment">wt.workflow.work.WfAssignment</table>

</from>

<linkJoin>

<join name="wt.change2.IncludedIn2">

<aliasTarget alias="Change Notice (wt.change2.WTChangeOrder2)"/>

<aliasTarget alias="Change Task (wt.change2.WTChangeActivity2)"/>

</join>

<join name="wt.workflow.work.WorkItemLink">

<aliasTarget alias="Wf Assignment"/>

<aliasTarget alias="Work Item"/>

</join>

<join name="wt.workflow.work.ActivityAssignmentLink">

<aliasTarget alias="Wf Assigned Activity"/>

<aliasTarget alias="Wf Assignment"/>

</join>

</linkJoin>

<referenceJoin>

<join name="primaryBusinessObject">

<aliasTarget alias="Work Item"/>

<aliasTarget alias="Change Task (wt.change2.WTChangeActivity2)"/>

</join>

</referenceJoin>

</query>

</statement>

</qml>

---------------------------------------------

Thanks,

Vivek

[ChrisSpartz]

Hi Vivek,

Did Marco's article give you the information you needed to write the query? Let us know if you need further assistance.