cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

We are happy to announce the new Windchill Customization board! Learn more.

Using Query Builder and Report Templates to Gather Data

bcedar
14-Alexandrite

Using Query Builder and Report Templates to Gather Data

One of my first projects in Windchill was to implement Nonconformance.  Being new to Windchill I was relunctant to make many changes to the out-of-the-box implementation.

 

While walking my clients through the process of Nonconformance I was consistently being asked the generic question of "Why can't I see that information in my task?  Why do I need to dig into the Nonconformance to see the information I need?" and I couldn't give them a good answer.  I did what I could but the best I could do was use the PersistableAdapter api to get information stored in the Nonconformance itself.  For example...

com.ptc.qualitymanagement.nc.Nonconformance mine = (com.ptc.qualitymanagement.nc.Nonconformance)primaryBusinessObject;
com.ptc.core.lwc.server.PersistableAdapter obj = new com.ptc.core.lwc.server.PersistableAdapter(mine,null,null,null);
obj.load("dateFileOpened", "number", "name","description","OBSERVATION","ncKey","requester");

java.text.DateFormat df = java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM);
java.sql.Timestamp tempDate = (java.sql.Timestamp)obj.get("dateFileOpened");
java.util.Date dateVar = new java.util.Date(tempDate.getTime());

dateNcCreated   = (java.util.Date)obj.get("dateFileOpened");
ncNumber = (String)obj.get("number");
ncName = obj.get("name").toString();
ncDescription = (String)obj.get("description");
ncObservation = (String)obj.get("OBSERVATION");
ncKey = (String)obj.get("ncKey");
requester = obj.get("requester").toString();
container = (String)((com.ptc.qualitymanagement.nc.Nonconformance)primaryBusinessObject).getContainer().getName();

Early in the implementation, this was the best I could do.

 

One of the latest requests I have had in the Nonconformance implementation was to get a list of the Immediate Actions taken for each part of a Nonconformance presented in the Nonconformance Disposition task.

 

I first started looking at the out-of-the-box Nonconformance reports to get an understanding of the object family structure.  That led me to trying to find the APIs for Nonconformance, didn't find any as there are no such supported APIs documented in JavaDoc.  If I have a report, then I'm getting the information I need.  How does the report present the information?  Can I use the report itself?  My initial path was to get the URL of the report itself and parse the response for the data I needed... what a mess (and points to just how new to this stuff I am).  Then I found ReportTemplateHelper.generateTableModel().  This was my ah-ha moment.  I can build a report template that has all the information I need for a task, a notification, a custom jsp, and generate a TableModel then parse it like a 2 dimension array and display its contents.

 

So, here's how I do it...  First I need to generate the TableModel.  To do this I use the following method found in ReportTemplateHelper.

 

image.png

Here is the code...

package ext.daktronics;

import java.net.*;
import java.io.*;
import wt.query.*;
import wt.query.template.*;
import wt.fc.QueryResult;
import java.util.HashMap;
import wt.util.WTException;
import wt.pds.StatementSpec;
import wt.session.*;
import javax.swing.table.*;
import wt.query.template.ReportTemplateHelper;
import java.util.Hashtable;

public class Reporting {
	public static TableModel getReportTableModel(String reportName, HashMap<String,String> hashmap)
	{
		TableModel reportData = null;
		try {
			QuerySpec qspec = new QuerySpec(ReportTemplate.class);
			qspec.appendWhere(new SearchCondition(ReportTemplate.class, wt.query.template.ReportTemplate.NAME, wt.query.SearchCondition.EQUAL, reportName), null);
			QueryResult reportQuery = wt.fc.PersistenceHelper.manager.find((StatementSpec)qspec);
			if (reportQuery.hasMoreElements())
			{
				ReportTemplate report = (ReportTemplate)reportQuery.nextElement();
				if (hashmap == null)
				{
					hashmap = new HashMap<String, String>(); 
				} 
				hashmap.put("action", "ExecuteReport");  
				hashmap.put("format","formatCustom");
				hashmap.put("xsl1","templates/reports/simpleHtmlFormat.xsl");
				hashmap.put("oid",report.toString());
				Hashtable<String, String> hashtable = new Hashtable<String, String>();
				hashtable.putAll(hashmap);
				reportData = ReportTemplateHelper.generateTableModel(report, hashtable, null, false);
			}
		} catch (QueryException e) {
			e.printStackTrace();
		} catch (WTException e) {
			e.printStackTrace();
		}
		return reportData;
	}
	public static TableModel getReportTableModel(String reportName)
	{
		return getReportTableModel(reportName,null);
	}
}

 

Now that I have the TableModel, I can parse it.  In an expression bot in the workflow where I parse the information and create an in-line report layout in a workflow process level string variable...

HashMap hashmap = new HashMap();
hashmap.put("NC Number",ncNumber);
javax.swing.table.TableModel reportData = ext.daktronics.Reporting.getReportTableModel("Immediate Actions for NC", hashmap);
int numCol = reportData.getColumnCount();
int numRow = reportData.getRowCount();
for (int i = 0; i < numRow; i++)
{
	for (int j = 0; j < numCol; j++)
	{
		if (reportData.getValueAt(i,j) != null)
		{
			immediateActions += reportData.getColumnName(j) + ":&nbsp;" + reportData.getValueAt(i,j).toString() + "&nbsp;&nbsp;&nbsp;&nbsp;";
		}
	}
	immediateActions += "\n";
}

And the result looks something like...

image.png

 

I have used this method for independent jsp implementations (such has having a printable Nonconformance without having cognos), email notifications that include a list of affected objects and related people and places, and workflow tasks.

 

I am still new to the Windchill community, only working on this server for a little over a year.  Every week I learn something that turns something I figured out prior into garbage.  I don't think this method will fall pray to that.  Using this I can meet information presentation requirements by...

  1. Build a report that meets the information requirements.
  2. Generate the TableModel of the report.
  3. Parse and Present the TableModel.

If there are better ways, please link them to me and let me know.  I am always looking to improve how I do things.

I hope you find this as useful as I have, thank you.

2 REPLIES 2
Levente
7-Bedrock
(To:bcedar)

Many thanks for sharing this, 

One question: do you know how can I pass input parameters to the report from the Java code?

 

Thanks

bcedar
14-Alexandrite
(To:Levente)

The hashmap in the code example is what achieves the parameter passing.

Top Tags