Windchill reporting r6 2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 118

Windchill Reporting R6.2 PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on
  • Presentation posted in: General

Windchill Reporting R6.2. Rebecca French PTC Global Services. Draft A.2. Contents. This workshop will cover the following areas Overview Examples and references Creating Reports Output Types Joins Sub Select IBA Reporting Workflow & Lifecycle History Reporting Non Standard Output

Download Presentation

Windchill Reporting R6.2

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Windchill reporting r6 2

Windchill ReportingR6.2

Rebecca French

PTC Global Services

Draft A.2


Contents

Contents

  • This workshop will cover the following areas

    • Overview

    • Examples and references

    • Creating Reports

      • Output Types

      • Joins

      • Sub Select

    • IBA Reporting

    • Workflow & Lifecycle History Reporting

    • Non Standard Output

      • Charts

      • Excel Web Query

      • Xml Manipulation

    • Macros

    • Configuration

    • Advantages

    • Limitations

    • Objects & Relationships Summary


Why bother with query builder

Why Bother With Query Builder?

  • Query Builder provides an OOTB UI which enables consultants and customers to produce reports on the data held in Windchill in various formats quickly and easily

  • While it does require a good knowledge of Windchill as an application and some technical skill it is not rocket science and once you get used to it is no where near as daunting as first time users think.

  • Things people say about Query Builder:

    • You need to know all about the data model

      • NOT REALLY. You do need some awareness of the relationships between objects and how they are constructed but this normally a fairly limited set. See Objects & Relationships Summary.

    • It is too difficult to understand

      • NOT REALLY. It is not the easiest of tools to use at first sight, but given a few pointers most people can get to grips with it.

    • It is too limited

      • NOT REALLY. It will not necessarily fulfil every requirement the business has but you generally find it should satisfy 50-80% of the requirement, particularly at R6.x. With the addition of out puts such as Excel Web Query you can add another dimension to the report without customisation


Windchill reporting r6 2

What Query Builder Can Do OOTB

There are a large variety of OOTB outputs, Metrics can be calculated, Relationships can be navigated.

Before you resort to customised reports – give it a try you may be surprised by how far you get!


Windchill reporting r6 2

What Query Builder Can Do OOTB

You can also output to other applications, via csv, tsv and xml.

OOTB you can produce Excel Web Queries which add the functionality of Excel to the give greater flexibility on format, allowing automatic refresh from the database.


Overview

Execution

Potential Custom QML

Generation

Oracle

Authoring

Report Template Object

Report

Template

GUI

XML Query Specification (QML)

Low-Level Outputs

For Customizations

JDBC

Output

Persistence

Manager

Java Objects

Invocation

HTML

Form

Input Parameters

XSLT

Transforms

XML

Potential

Custom Invocation

Custom Output

Formats

CSS

HTML

CSV

TSV

Browser

Presentation

Output Format

Customization

Points

Overview

  • Query Builder builds Report Template objects which are executed at runtime


Overview1

Overview

  • If you really find the OOTB functionality too limited you can see from the architecture that there are customisation points.

    • Don’t overlook the use of third party tools, such as Excel Web Query

    • Input sheets can be customised

    • Macros allow functions to be run to generate input

    • XSLT will allow manipulation of the output

    • The Charting element can be extended to provide more flexible charts and graphs

  • Extension of Query Builder has the advantage of still retaining the flexibility of a user interface to alter the input and output from reports

  • As we are all aware, business requirements are constantly shifting.


Overview2

Overview

  • This presentation will cover the use of the tool OOTB but there are references to where you can look for guidance on extending the functionality.

  • The presentation uses Windchill 6.2, the tool was much improved between versions 5 and 6. Some references have been made to the key differences between the versions but these are not comprehensive, so be warned, if you are using 5.x you may be disappointed, all the more reason to upgrade.

  • The use of the tool with other products such as Project Link and Dynamic Design Link has be investigated in a limited manner. At 6.1 you will be using a 5.1 Query Builder, at 6.2 it will be in line with Windchill 6.2. You may find that some of Class specific to the links are not available by default for selection. If you are to use Query builder with links in anger you may wish to clarify the support status.


Examples and references

Examples and references

  • All of the reports used in the presentation are supplied as examples, plus one or two extra. But there are other places you can look for information.

  • Windchill Report Templates

    • System/Reports

      • These are loaded via the Windchill load program, demo data. See Windchill Installation and Configuration Guide

  • Windchill Customizers Guide – Report Generation

  • Tutorial, accessible from Help in Query Builder

  • PTC Windmill

    • /Windchill Sample Reports

    • THIS IS PTC INTERNAL ONLY


Creating a report template

Creating a Report Template

  • Creating Reports Contents:

    • Creating a basic Report on WTPart

    • Running the Report

    • Standard Output Types

    • Joins By Link

      • Part Usage

      • Parts and Described By Documents

    • Joins Via Criteria

      • Change Activities

    • Sub-Select

    • Joins By Reference


Creating a report template1

Creating a Report Template

  • This is done from Windchill Explorer

  • For example creating a report which will list the parts in the database, with optional search criteria fields


Creating a report template from

Only part of the class name should be entered. Some of the class on the list appear with spaces therefore entering an exact class name may not return the class you want on the list

Multiple classes may be selected at once, using the shift or control keys

Creating a Report Template - From

  • There are various tabs in which you can enter your information.

  • The first is From, this allows you to select the classes that will be used in the query, as input, output or to enable a join.

  • In this case we select WTPart


Creating a report template select

Where there are multiple classes, you need to select the class which holds the attribute

Multiple attributes may be selected at once, using the shift or control keys

Creating a Report Template - Select

  • The Select tab allows you to select the attributes you want to display in the results of the report


Creating a report template select1

The order of the selected attribute can be changed

Creating a Report Template - Select

  • In this case we want the Name, Number, Creator and Version

    • Name and Number appear at the top level.

    • The Creators Full Name appears as a sub option of Created By

    • Version is a little more tricky. It will depend on the way you wish it to be displayed.

      Version -> Lineage -> Series -> Value

      This will display the Revision.Iteration, e.g. A.5

    • The alias column contains the text which will be used as the heading for the output in the results. It defaults to the modelled name which may not always be helpful to a user.

    • It can be updated by selecting the contents of the cell, and an alternate name can then be entered


Creating a report template attributes

Persistent Attribute

Derived Attribute

Creating a Report Template - Attributes

  • Attributes can be one of two types:

  • Persistent

    • These relate directly to a database or column (which may be a view rather than a table)

  • Derived

    • These use Java Instance methods to establish values, they are run after the query is run in the database.

  • Key Differences

    • Derived attributes are most more costly on performance so should be used only when required

    • Derived attributes may not always be available for selection in functions, criteria or sorts (within query builder)

    • Derived attributes will detrimentally affect grouping


Saving the report

Saving The Report

  • Once there is enough information in the report that it could be run it can be saved.

  • As a minimum the report must have From classes and Selected attributes

  • The Report is saved in the users personal cabinet.

  • The Report may be moved to an alternate location once completed

  • Reports can have access control policies applied in the same way as other business objects


Creating a report template criteria

Creating a Report Template - Criteria

  • Criteria allows comparison between, attributes, parameters, constants and sub selects

    • Attribute – A persisted attribute on the class.

    • Parameter – This represents user input. For each Parameter Type an appropriate input field will be displayed at runtime

    • Constants – These allow enforcement of a fixed value, e.g Part State = Released

    • Sub Select – This allows a sub query to be entered as criteria, an example will be given

  • All of these options are used in the examples in this workshop


Creating a report template criteria1

Select Name from the list of attributes for WTPart

Creating a Report Template - Criteria

  • In this case we want to allow the user to enter Name and State as criteria at runtime.


Creating a report template criteria2

The operator is the comparison type, e.g. equals, greater than etc.

The Name for a parameter is the text which will appear as the title for the input field label

A default value may optionally be entered.

Creating a Report Template - Criteria

  • The information on the top half of the frame (Left Operand) will be compared, in the manner you select as the Operator, to the information in the lower half.

  • In this case the attribute Name is compared to a value which will be entered by the user


Creating a report template criteria3

The default for an attribute which is an enumerated type will allow selection from a list

Creating a Report Template - Criteria

  • The steps are repeated for State


Running the report

Criteria defined as Type Parameter can be entered.

If an input field is left blank it will be ignored.

The type of output can be selected

Running the Report

  • To run a fully formatted output with the required input parameters the you need to save the report.


Running the report1

Running the Report

  • It is possible to test a report before saving using preview, this can be useful if you are unsure of the effect of changes you have made.


Standard output types

HTML With Merging

MSWord 2000 HTML

Standard Output Types

  • OTTB Standard Output

  • HTML – Standard HTML tabular output

  • HTML with Sorting – Column headings can be used to sort output

  • HTML with Merging – consecutive values in a column are merged

  • CSV, TSV – Output to file, comma or tab separated

  • MS Word 2000 HTML, see below (Not available 5.x)

  • PDF, requires Adobe Acrobat Distiller (Not available 5.x)

  • XML (Not available 5.x)


Enforcing output

Enforcing Output

  • The output type can be enforced for the query by setting the properties


Running reports from html interface

Running Reports From HTML Interface

  • Users can run reports from the HTML Interface

    • Reports can be located via Local Search or Browse Cabinets

    • Links to Generate the report appear beneath the URL for the properties page or from the Properties Page.

    • The Report description is shown on the properties page, this helps users determine the applicability of the Report – so you should always enter this when the report is defined.


Refining criteria

Refining Criteria

  • Currently the Report will only support the input of the correct Part name. To allow the input of wild cards (default wild card is %) the criteria should be altered to LIKE as opposed to =

  • It is also case sensitive, to avoid this issue with any text input field it is advisable to set both Function settings to UPPER


Refining criteria1

Refining Criteria

  • The report outputs all iterations of all versions of the Parts. This is often an issue with iterated objects.

  • To alleviate this an additional criteria is added which requires the attribute, Latest Iteration is a constant value of 1. This will show the latest iteration of all the revisions


Refining criteria booleans

Refining Criteria - Booleans

  • If the Report must return all parts where the name, and state are those entered, the iteration is the latest and the type is separable with a source of make, or the type is component with a source of buy.

  • This could be written:

    (Name = x) AND (State = y) AND (iteration = 1)

    AND ( ( (type = separable) AND (source = make) ) OR

    ( (type = component) AND (source = buy) ) )

  • In the Criteria tree it would be:

If you need to move an entry you can drag it to the section you wish to move it to.

NOT 5.x, in 5.x you must delete existing entry and recreate the entry in the new location


Selecting entire objects

Selecting Entire Objects

  • This does not apply to R5.x

  • If the entire object is selected it will displayed in the form shown.

The Number (Name) Version string should be displayed as a link to the object’s properties.

At 6.0 the link will be displayed, at R6.2 it is simply text, no link is generated. This issue has been logged as SPR#934820 and should be fixed in service pack 3.


Joins part uses

Joins – Part Uses

  • In the case of most business objects you do not need to understand the data model as defined in Rose to use join.

  • You need to understand Windchill and have some idea of the class names and you can create most of the links you need.

  • For example the uses relationship for a Part. A Part Version owns all versions of another Part. All versions equates to the Part Master, therefore the link is from WTPart to WTPartMaster, the link is Part Usage.

  • In the Rose model the relationship is represented:


Joins part uses1

Joins – Part Uses

  • On the From & Select Tabs:


Joins part uses2

Joins – Part Uses

  • Link Vs Reference

  • Link Joins are made where a modelled link with a cardinality of greater than 1 exists, e.g a WTPart may reference more than one Document. The link name will be:

    • The name of the association class, if one is specified

    • Else, link class name is the concatenation of role names

    • If role names are not specified, they are derived from the end class names

  • Reference joins are made where the object to object relationship has a cardinality or one or less E.g.

    • A Part contains a reference to zero or one Teams

    • A WfAssignedActivity must have a link to only one parent process

  • Some knowledge of Windchill and some areas of the data model is required but an element of guess work will often suffice, see section Objects & Relationships summary at the end of the presentation


Joins part uses3

Joins – Part Uses

  • In the Join Tab

Pick Part Usage

Output in merged HTML


Joins described by document

Joins – Described By Document

  • If we want to add the link to Described by.

  • This is a Part Version to Document Version link. WTPart to WTDocument

  • Modelled as:


Joins described by document1

Joins – Described By Document

  • If we want to add the link to Described by. This is a Part Version to Document Version link. WTPart to WTDocument


Joins uses described by

Joins – Uses + Described By

  • We want to create a report that shows the parent part, the uses parts and the documents that are associated to those “child” objects.

  • For example:


Joins uses described by1

WTDocument

WTPart

WTPart

WTPart

WTPartMaster

WTPartMaster

WTDocument

Joins – Uses + Described By

  • To achieve this we can not simply combine the two previous reports.

  • The Described By report navigates the WTPart to WTDocument relationship

The uses navigates the WTPart to WTPartMaster

Therefore combining them would retrieve the Documents associated to the “parent” Wtpart, which does not meet the requirement


Joins uses described by2

WTPart

WTPartMaster

WTPartMaster

WTDocument

WTDocument

WTPart

WTPart

Joins – Uses + Described By

  • We want to navigate to obtain the following relationships

So to achieve the desired result:


Joins uses described by3

Joins – Uses + Described By

To achieve this we need to be able to distinguish between two WTParts, to do this we add two WTPart classes to the From Tab

NOTE – when you are doing this alias the two parts sensibly so you know which one you are selecting for the joins or attributes and any criteria


Joins uses described by4

WTPart - Parent

Part Usage Link

WTPart Describe Link

Master Iteration Link

WTPartMaster

WTPartMaster

WTDocument

WTDocument

WTPart - Child

WTPart - Child

Joins – Uses + Described By

  • For the Join you need:

    • Uses - Parent WTPart to WTPartMaster

    • Master Iteration - WTPart Master to Child WTPart

    • Described By - Child WTPart to WTDocument


Joins uses described by5

Joins – Uses + Described By

  • We add Select as required, making sure we pick the appropriate WTPart


Change activity join

Change Activity Join

  • A Change Activity has links to parts or documents that will be changed (Affected or Original Data Versions) and are the result of the change (Amended or New Data Versions)

  • The Parts and Documents inherit from Changeable2, the Affected objects are linked by the AffectedActivityData link, Amended objects are linked by the ChangeRecord2 link.


Change activity join1

Change Activity Join

  • We want a report that will show the Change Activities and their associated amended or affected Documents. In Windchill we have:


Change activity join2

Change Activity Join

  • If we create the following report:

This report will return zero results. No document version is going to be associated as both amended and affected. By definition a Document that is affected by a change will be altered, creating a new iteration or version, which is associated as the amended object


Change activity join3

Change Activity Join

  • If we add two Documents, and alias appropriately then we can separate affected and amended links to the two document classes.


Change activity join4

Change Activity Join

This shows results, but only where the Activity has both an affected and amended link, those with only affected are not shown.

Therefore Change Activity 00001, Material Specification Change – 9978 is not shown in the results.

The key requirement for the report is that we wish to see activities that have amended OR affected Documents.


Change activity join5

Change Activity Join

  • To enforce the OR we can link the objects via the criteria tab.

  • If we use the original report as a basis:

    • Removing the joins.

    • The link classes need to be added as selected classes to allow us to use them as criteria

    • Selection remains unchanged.


Change activity join6

Link Properties

Change Activity Join

  • To successfully link the via criteria we need to look at the Rose model, and at the link properties in detail


Change activity join7

Change Activity Join

  • Armed with the knowledge that Role A on the link is the Document, B is the Activity, the other element we need to understand is the attribute used on the link.

  • Objects are normally linked using their unique persistence id. This is displayed on the object itself as Persist Info.Object Identifier.Id.

So to display either the Change Activities where there is an affected or amended document the criteria would colloquially be:

( ( (Doc Id = Affected Role A ID) AND (Activity ID = Affected Role B ID) )

OR ( (Doc Id = Amended Role A ID)

AND (Activity ID = Amended Role B ID) ) )


Change activity join8

Change Activity Join

  • The expression:

    ( ( (Doc Id = Affected Role A ID) AND (Activity ID = Affected Role B ID) )

    OR ( (Doc Id = Amended Role A ID)

    AND (Activity ID = Amended Role B ID) ) )

  • Would be implemented as shown below, in addition the results are limited to display only the latest iteration of any revision.


Change activity join9

Change Activity Join

  • If the report is run the results will be:

This includes a large number of duplicates


Change activity join10

Change Activity Join

  • To reduce the duplicates the Distinct option on the select tab can be checked

  • The results are then more coherent


Sub select

Sub-Select

  • This functionality is not available in 5.x

  • A Sub-Select is a “query within a query,” a query statement nested within the main query designed to limit the selected output of the parent query by producing an intermediate result set of some sort.

  • There are several different ways to include Sub-Selects the basic ones are:

    • Equality comparison operation

    • IN

    • NOT IN

  • It is important in any Sub-Select to ensure that there is only one field returned form the Sub-Select and that the object(s) returned from the Sub-Select are of the same type as the comparison.


Sub select1

Sub-Select

  • For a Sub-Select equality operation only one result can be returned. Generally an equality Sub-Select can be more easily achieved as direct criteria.

  • When using IN or NOT IN then the result from the Sub-Select may consist of more than one row. A match occurs if any of the rows matches. See example.

  • Additionally you may use a Sub-Select using EXISTS or NOT EXISTS. This is not a comparison operation. It returns true or false.

  • SQL knowledge is a definite help when using Sub-Selects.


Sub select example

Sub-Select - Example

  • If we look at the simple report on WTPart

The state is entered by the user at runtime.


Sub select example1

Sub-Select - Example

  • If we wish enforce the return of objects at more than one state we could alter the criteria tab

To clarify the results if we add the Part’s State to the Select tab the results in this case would be:


Sub select example2

Sub-Select - Example

  • We may want to expand this further, to include all of the parts art any of the states in the Review Lifecycle.

This would require four criteria within the OR of the criteria tab, this could become excessive if your Lifecycle contains a large number of states. An alternate method would be to add a Sub-Select in the criteria.


Sub select example3

Selecting Edit Sub Select will allow the sub query to be created

Sub-Select - Example

If we remove the OR and then add a criteria comparing the WTPart State attribute to a value IN a Sub Select


Sub select example4

Sub-Select - Example

Creating a Sub-Select uses the same techniques as creating a report, it contains the same tabs and methods.

NOTE You may only select one value, which is compared to the value of the attribute in the object.

In this case the Sub-Select needs to return state values where the state exists on a given Lifecycle Template name.

A Lifecycle Template does not contain the states directly. The state is held in a Phase Template


Sub select example5

Sub-Select - Example

The the Sub-Select query would be constructed with the From tab and Join as shown here:


Sub select example6

Sub-Select - Example

In the Select tab we can only select on value, in this case we use the Phase State

Once the Sub-Select query has been Completed, Select OK on the Sub-Select window and then Save the Report Template.

You MUST Save the template before you re-enter the Sub Select or it will be re-initialised to the blank form !!!!!!


Sub select example7

Sub-Select - Example

In this case the results from this report would be:


Join by reference

Join By Reference

Note that this query will return any objects which are at a state in the Lifecycle Template, this may include objects on other Lifecycle Templates.

To additionally enforce the selection of objects from a specific Lifecycle Template the Report would need to be updated.

  • The Lifecycle Template is added as a Class in the From Tab

  • A criteria Lifecycle Template name equals a constant of the required name is added to the Criteria Tab


Join by reference1

Even if the join is created from the By Link section, if the Join you select is a Reference then the tool will place it in the By Reference section

Join By Reference

Additionally the WTPart needs to be joined to the Lifecycle Template.

A Lifecycle Template is not linked to a WTPart via a link class, it is linked via a WTReference.

However, the distinction is not important when you create the join, The Query Builder tool will determine the type of join for you


Iba reporting

IBA Reporting

  • When constructing an IBA report is important to remember that IBAs, while they may be seen as just more attributes on an part, are objects in themselves that are related to the part.

  • When constructing a report the fowling relations must be considered:

    • A part is an IBA holder, it contains references to the IBA types

    • When creating a report you must know which type of IBA you are linking to. E.g. String, Float etc.

    • An IBA has a definition of its type and and value which it holds

  • See the following example for guidance on how to create the relationships between a WTPart and and IBA.


Iba reporting1

IBA Reporting

  • We have parts in the database which are provided by a supplier, these have IBAs associated to them.

    • LeadTime - which is an integer value representing the number of days between order and delivery

    • Cost - A float value representing the cost in pounds

  • We want to create two reports:

    • A report which shows the parts which have a lead time of less than 20 days

    • A report which shows the parts which have a cost of less than £80


Iba reporting2

IBA Reporting

  • The classes which must be selected are:

    • The WTPart

    • The IBA type Definition, which contains the name of the IBA, for the lead time this will be IntegerDefinition

    • The IBA type Value, which contains the value, for the lead time this will be IntegerValue


Iba reporting3

IBA Reporting

The Joins required as as follows:

  • Select & Criteria

Output:


Iba reporting4

IBA Reporting

To query for objects with a cost less than 80 the Report would be as follows:


Iba reporting5

IBA Reporting

To query for objects with a cost less than 80 and that have a lead time of less than 20 the Report would be as follows:

This report will only return Parts which have both the IBAs Cost and LeadTime


Workflow and lifecycle reports

Workflow and Lifecycle Reports

  • Workflow and Lifecycle History reporting is normally carried out to monitor or gather metrics

  • This section covers:

    • Workflow Objects Relationships

    • Key Objects and Joins

    • Workflow Report Examples

    • Lifecycle History Overview

    • Lifecycle History Modelled Relationships

    • Lifecycle History Example

  • There are further examples in the section on Chart Output and Excel Web Query


Workflow modeled relationships

Workflow Modeled Relationships

  • When reporting on Workflows it is important to understand the relationships.

  • The key classes are WfAssignedActivity and WorkItem

The WfAssignedActivity is linked by reference to the WfProcess instance via an attribute inherited from WfNode, parentProcessRef

The WorkItem is linked to the Primary Business Object via an attribute, primaryBusinessObject


Workflow relationships

WfProcess

Activities, e.g. Set Users, Review, Approve

WfActivity

WfActivity

Assignment is made via the WfAssigment class

WorkItem

WorkItem

WorkItem

Objects which appear on the users worklist

Workflow Relationships

  • When an instance of WfProcess runs an Activity is instantiated as WfAssignedActivity

  • The WfAssignedActivity may be assigned to one or more users, this is done via a WfAssignment and results in the user being allocated a WorkItem


Workflow key objects and joins

Workflow Key Objects and Joins

  • Reporting on Activities

    • If you are try to monitor task information by user then you need to query the Work Item.

    • If you are trying to monitor how long a task took in total, rather than by user, then you need to query the WfAssignedActivity.

    • Note that where an activity is in a loop, only the last visit will be returned in any query.

  • Joins

    • A WorkItem joins to a WfAssignment via a WorkItemLink

    • A WfAssignment joins to a WfAssignedActivity via a ActivityAssignmentLink

    • A WfAssignedActivity joins to a WfProcess via a ParentProcessReference

    • The Primary Business Object can be joined to the WorkItem via a PrimaryBusinessObject reference


Workflow example workitem duration

Workflow Example – WorkItem Duration

  • Report:

    Get the duration of each completed WorkItem, displaying the task name, user and role.

  • Classes need are the WorkflowAssignedActivity to get the name of the task, the WorkItem for the user, role and duration.

  • These need to be linked via the Workflow Assignment class


Workflow example workitem duration1

Check the Advanced option to allow function to be performed between attributes

In this case we are performing a function on a date therefore we can either use Subtract to get the number of days or Time Difference(Days)

Workflow Example – WorkItem Duration

  • The name is selected from the WfAssignedActivity, the Role and user (owner) from the WorkItem.

  • The duration is not an attribute, it is the result of a subtraction of the creation date from the last updated date, where the WorkItem is complete.


Workflow example workitem duration2

Floor is applied to the Duration to round the result down

Only Completed WorkItems are returned

Workflow Example – WorkItem Duration

  • Remaining definitions


Workflow example workitem duration3

Workflow Example – WorkItem Duration

  • Results:


Workflow example duration by object

Workflow Example – Duration By Object

  • Report

    Duration of each activity for a specific Document, which has completed its process

  • In this case we would appear only to need the WTDocument, WfProcess and the WfAssignedActivity. However, to link to the PrimaryBusinessObject we need to include the WorkItem.

  • As before we need the WfAssignment class to link the WfAssignedActivity to the WorkItem


Workflow example duration by object1

Workflow Example – Duration By Object

  • Join


Workflow example duration by object2

Appears as Executed on the list of possible States

Workflow Example – Duration By Object

  • Select and Criteria

NOTE this functionality is not available in 5.x


Workflow example duration by object3

Workflow Example – Duration By Object

  • If the report is run, entering 77265-4 as the Document number, results would be of the form:


Ootb workflow reports examples

OOTB Workflow Reports Examples

  • The Workflow examples provided OOTB are:

    • Overdue Processes

    • Overdue Running Processes

    • Overdue Work Category

    • Team Processes

    • Processes Initiated by Current User

  • These can be loaded via the Load Demo and Load Report utilities


Lifecycle history overview

The history also contains information on reviews

The user listed against automatic actions such as enter phase or set state will always appear as actioned by Administrator

The History lists the actions, E.g. Set State, Promote. It also lists an Action of Enter_Phase. The object will always have an action Enter_Phase whether the state change occurred due to a Set State robot, Promote robot or any other action.

This is key if you are interested in reports on the dates of state change.

Lifecycle History Overview

  • Information on how an Object progress through a lifecycle is held in the Lifecycle History. The entire history for an object can be displayed by accessing the Lifecycle History link on the Properties page for the object.


Lifecycle history model

Lifecycle History Model

  • The History is modelled as shown.

  • Generally it is not necessary to retrieve information on signatures, if users need all of the information, including signatures they can access the Lifecycle History link, recreating this as a report is simply a duplication of OOTB functionality


Lifecycle history example

Lifecycle History Example

  • Report:

    Report on the time taken from creation of the Document to the time each State was entered where the Document is currently in a Released State

    Allow user input of Document Name non cases sensitive strings with wild cards

    List the name, number and version of the Document, the state, duration and the date the state was entered

  • The Select and Join tabs of the report would be as follows:


Lifecycle history example1

Lifecycle History Example

  • Select:

Note that the state must be selected from the Lifecycle History, NOT the Document


Lifecycle history example2

Lifecycle History Example

  • Criteria:

Sort on the following.

Note that the sort will be a strict alphanumeric sort therefore values above 100 will distort the sort, also sorts on dates will not sort by the date as you would expect


Lifecycle history example3

Lifecycle History Example

  • The output in merged HTML would be of the following form


Workflow vs lifecycle history

Workflow Vs. Lifecycle History

  • It should be noted that where each state is associated to a Phase Workflow state durations can be determined by the time each workflow takes to execute


Non standard output

Non Standard Output

  • There are alternate outputs provided OOTB that do not appear on the standard list.

    • Chart Output

      • Bar Chart (barChart.xsl)

      • Line Chart (lineChart.xsl)

      • Pie Chart (pieChart.xsl)

    • Excel Web Query

      • Excel 2000 format (excel2000WebQuery.xsl)

      • Excel 97 format (excel97WebQuery.xsl)

  • It is also possible to create your own XSLT style sheets to generate other output types, see Windchill Customizers Guide – Report Generation


Non standard output1

Non Standard Output

  • Chart output was not available OOTB at 5.x but there is an example customisation in Windmill, in /Windchill Sample Reports.

  • Web Query was also not available in 5.x but the xsl files you need to easily implement it are in Windmill, in /Windchill Sample Reports.


Output bar chart

Output – Bar Chart

  • If we want to create a bar chart showing the average duration in days that tasks take from creation to completion by all required users

  • Using the example in the first workflow query as a basis.


Output bar chart1

Output – Bar Chart

  • The Select options are adjusted.

  • Function Average is added to the subtraction of WorkItem Created Date from the Last Updated Date

  • If an aggregate function, e.g. Max, Min, Average, Count etc, is used the output must be grouped, this is achieved by checking the Group By option


Output charts

Output - Charts

  • Chart generation is only accessible by entering a custom XSL URL


Output custom chart types

Output – Custom Chart Types

  • In order to implement a custom chart type:

    • Implement the wt.query.template.Chartable interface

      • Inputs: Report data, chart width and height, parameter string

      • Outputs: Java Component which produces chart

    • Add any necessary classes to the method server classpath (e.g. to the Windchill codebase)

    • Create a custom XSLT stylesheet using existing chart XSLT as a template

      • Change Chartable implementation class name

      • Change parameter string as necessary

      • Change chart width and height as desired


Output excel web query

Output – Excel Web Query

  • Excel 97 and 2000 both support the retrieval of data from external sources, this includes retrieval of data from Windchill using a iqy file generated from a query.

  • The Report output does not by default appear on the list, it must be entered as a Custom XSL URL in the same manner as for Chart Output.

    • For Excel 97 - templates/reports/excel97WebQuery.xsl

    • For Excel 2000 - templates/reports/excel2000WebQuery.xsl

  • The following slides explain the steps required to create, use and update a report in Excel using a Web Query generated from a Report


Output excel web query1

Output – Excel Web Query

  • Using a lifecycle history report:


Output excel web query2

Output – Excel Web Query

  • The Report is generated and the XSL URL entered

This will prompt for Open or Save To Disk – DO NOT OPEN – it will not make any sense!

Save to Disk – Make sure you enter IQY for the extension of the file


Output excel web query3

Navigate to where the IQY file was saved

Confirm the input location

Log in to Windchill to run the report

Output – Excel Web Query

  • From Excel create a new Workbook

  • Select an appropriate cell, e.g. A1


Output excel web query4

Output – Excel Web Query

  • Once the report has been run it will appear in the following form:


Output excel web query5

Pivot tables can be added

Calculations can be added, =IF(AND(B11=B12,C11=C12),E12-E11,0), these can be automatically updated on refresh of data

Charts can be added in any format

Output – Excel Web Query

  • The spreadsheet can be modified. More than one query can be added to a spread sheet to allow calculations, pivot tables and charts to be created using data from multiple queries


Output excel web query6

Output – Excel Web Query

  • Refreshing Data

    • When data is refreshed it will, in some cases, undo the formatting etc., therefore it is recommended that formating etc. is performed using macros.

  • To refresh the data select a cell in the report range

    • Data -> Refresh Data


Excel web query advantages

Excel Web Query Advantages

  • It is quite common for different areas of the business to have different requirements, which would require different customisations

  • The business requirement can change frequently requiring output formats to be updated on a regular basis

  • Using an Excel Web query with macro formatting and calculations allows:

    • The business to format the reports in any manner they wish

    • Various formats of chart to be added to the spread sheet without custom formats being developed

    • Calculations and other functions such as pivot tables to be quickly and easily generated

  • There is an example formatted Web Query with this presentation. It contains a Macro as example of how, relatively easily the query can be refreshed and reformated


Macros

Macros

  • Macros are provided to enable parameter and constant values to be derived by the system at report execution time.

  • Two are provided with OOTB

    • CURRENT_USER_NAME

    • CURRENT_TIME

  • Macros can be created by extending the extending the MacroExpressionProcessor see Windchill Customizers Guide – Report Generation

  • There are also a few examples in Windmill /Windchill Sample Reports


Configuration

Configuration

  • Various aspects of Reporting can be configured or actioned from the server:

    • Standard Output Type list can be modified or added to

    • Import/Export of Templates is possible from the command line

    • The wild card, default %, can be altered

    • The number of returns from a query can be limited to avoid negative database impact if excessively large queries are run


Configuration adding output types

Configuration – Adding Output Types

  • Adding an XSLT style sheet to the standard list

    • The format must be added to a suitable properties file, it is recommended that this is a custom properties file, the OOTB properties file is dbservice.properties

    • The entry must be in the following form:

      wt.services/rsc/default/wt.query.report.DefaultXSL/Format_Name/java.lang.Object/0=template_location

      E.g.

      wt.services/rsc/default/wt.query.report.DefaultXSL/Excel2000WebQuery/java.lang.Object/0=templates.ext.mine.excel2000WebQuery

      The location is relative to $WT_HOME/codebase, the extension of the file should not be entered

      The format name, location or template name may NOT contain spaces

    • The entry will then appear on the list as the Format Name, e.g. Excel2000WebQuery

    • If a localised version or alternate format of display is required then the resource bundle, wt.query.template.templateResource must be updated by modifying and rebuilding templateResource.rbInfo


Configuration export import

Configuration – Export/Import

  • Export/Import of Templates

    • Report templates can be exported using a command line utility from the server, see Windchill Customizers Guide – Report Generation. This creates two files a csv load file and an XML representation of the report. The csv references the XML file(s).

    • Reports can be imported into another system using either, LoadReportTemplate or LoadFromFile both methods are described in the Customizers Guide.

      • LoadReportTemplate will stop and restart the Method Server, this is not an option it is automatic

      • LoadFromFile does not affect the Method Server but does require the report template xml files to be located in <Windchill_Home>/loadFiles/Reports unless a full path is specified in the associated csv


Configuration export example

Configuration – Export Example

  • Export of Query Builder Templates.

  • To export a single report

    • Open a command window, with the correct codebase and paths set

    • Change location to a directory where you wish to place the output

    • Execute the following command:

      java wt.query.template.ExportReportTemplate WRKShopIBACost

    • To export multiple reports you can use a wild card:

      java wt.query.template.ExportReportTemplate WRKShop%

  • This will create xml files for each report and a single csv file.


Configuration export example import

Configuration – Export Example/Import

  • Import of Query Builder Templates

  • The csv file may be placed in any location, the xml files sholud be placed in <Windchill_Home>/loadfiles/reports

  • It is possible to import the xml files from an alternate loaction if you wish to enter the full path in addition to the file name in the csv file

    • Open a command window, with the correct codebase and paths set

    • Execute the following command:

      java wt.load.LoadFromFile -d <PATH>/exportreports.csv


Configuration wild card

Configuration – Wild Card

  • Wild Card

    • This is information has been requested from technical support and will be added when available (Not applicable to R5.x)


Configuration database impact

Configuration – Database Impact

  • It is possible to create reports that produce excess results causing a negative impact on performance.

  • To avoid issues in a production system reports should always be developed on a test system which holds a representative amount and type of data.

  • Where possible all reports should be developed with suitable criteria which will limit processing.

  • To avoid a large queries being run it is possible to limit the number of results returned

    • The property which controls this is wt.pom.queryLimit. This should be set in the db.properties file. The default is unlimited.

    • This will affect Report Templates, Local Search and any database queries using the wt.query package classes and methods and Windchill itself

    • It should not be set too low or you will limit the queries that Windchill has to perform to run correctly, the value should be in the thousands, e.g. 5000 is not a large setting for this property


Advantages

Advantages

  • Use of the OOTB GUI allows easy modification of Reports as business requirements change

  • If alternate outputs are required these can be dealt with using manipulation of the xml output

  • With an extension to create Excel web query files the business can produce their own charts and calculations

  • Charting can be extended to allow the production of different formats and styles of graphical output


Limitations

Limitations

  • Data held as Binary Large Object format can not sensibly be used in queries or as output. E.g.

    • File Content

    • Access Control Lists for an object

    • Any objects not implementing Persistable or ObjectMappable


Notes on testing

Notes On Testing

  • While initially it is easier to test a reports on a system where there is little or no data this is only acceptable for initial development.

  • It is important that you ensure your test data will return enough information to establish any possible performance problem.

  • You also need to ensure that you have data that will both satisfy and not satisfy the queries you test.

  • Creating and checking the data may frequently take longer than creating the query itself.

  • When you are creating queries which will have a large number of criteria it is easier to build this up gradually. Otherwise you are left with a query which returns zero results and you will not know which criteria is the culprit.


Objects relationships summary

Objects & Relationships Summary

  • This list is a sub set of the most commonly used relationships.

  • Document - Internal

Part - Internal

Part - Document


Objects relationships summary1

Objects & Relationships Summary

  • Change Objects – Internal, (Package wt.change2)

Change Objects – External, (Package wt.change2)

Changable2 can be either Document or Part


Objects relationships summary2

Objects & Relationships Summary

  • Workflow

Lifecycle History


  • Login