Windchill Search engine: import results directly into Excel

[Spiff]

Hello!

I am searching for a way to directly import search results into Excel.

I don't want to use the manual Export -> Excel option but for example a direct URL to a search and get a text based XML or CSV file back as result which can be imported into Excel. And this search result should contain the EPMDocument number of each search result.

The standard search window cannot be imported to Excel since it contains Javascript elements and it is not showing up the EPMDocument number (or is this just a setting?)

My goal is to import this text into Excel with a macro and generating direct URLs to Creo View so the user can interact from Excel, gets a list of different versions and can directly open them in Creo View.

This means that the search routine should be some kind of translator from the search keyword to the internal EPMDocument number which seems to be the unique database number which is needed for Creo View, right?

I also thought of connecting directly to the Oracle database and perform the search there but I know that PTC advises users against connecting directly to the database with 3rd party software.

If you have any tips how to manage this, I would be glad.

Thanks!

Regards,

David

[AL_ANDERSON]

We do this all the time with Query Builder where you can generate an ".iqy" file and then open that in Excel where it comes in as an embedded web query. At that point, you save the excel file (.xlsx or .xls), and you can then open that file any time to refresh the data on that tab directly from Windchill. You can use properties of that table to auto-refresh every 60 seconds (or whatever) or auto-refresh on file open. There are other preferences, as well.

Meanwhile, any other tabs you create can look up data from the webquery tab easily. You can even lock and hid that query builder tab from users if you want to hide it.

However, each time the data refreshes, the excel user will have to log into Windchill with their user ID and password. The data is "in the system," so you must be a Windchill user to get updates.

Al Anderson

[Spiff]

Hi Al,

thanks very much for this idea!

I have just made first experiences with the Query Builder and will step deeper into the tutorials.

Can you provide a short example of how to generate the ".iqy"-file?

Is this kind of procedure flexible so a user can specify the search criteria within Excel?

For example:

You put a drawing number into an Excel cell, perform this web query and the result will be shown for this specific drawing number?

In my understanding that would mean that this ".iqy" points to a link where the drawing number is inserted by Excel, right?

Or do you have to generate a search report for each specific drawing number manually within the Query Builder?

Thanks!

David

[Spiff]

Hi,

I found a JavaScript class in the documentation which seems to be able to generate a direct URL which leads to a report with input variables included.

This is exactly what I am looking for:

ReportTemplateHelper

It is mentioned in the Customization Guide:

Customizing the Report Generation URL

Class Description

Is there an example available? I don't know how to start the ReportTemplateHelper.

I already prepared a report in the Query Builder which has one selection option (drawing number).

I want the ReportTemplateHelper to give out the Execution URL to the report including the drawing number variable which is normally input by the user requesting the report.

This is the link to the report input form on our server:

http://(server)/Windchill/wtcore/jsp/report/adhocReportCriteria.jsp?oid=OR%3Awt.query.template.ReportTemplate%3A92184795&u8=1

How should the URL to the ReportTemplateHelper look like for this report?

Thanks in advance!

David