Integrity LM: Server Gateway and Reports

[#1]

Hint: to open the small pictures perform a "right-click" and then "open in separate tab"

Intention

This document is intended as a discussion base for topics and solutions around Reporting in general and specifically the Integrity Gateway and the Integrity Reports.

Status

Although the PTC world speaks in between with ThingWorx and PTC Navigate (including reporting Apps), I still believe that Integrity Build-In Reporting has its place in the product.

Related Use Case

As a user, I want the system to provide a single interface to generate predefined reports without having to switch to different tools.

I need different formats depending on my goal for the information processing, either in MS Word, MS Excel, HTML or PDF format.

Standard Integrity Functionality

Integrity Gateway: Create standardized Output - mainly in Word Format - for any stakeholder or auditors.

Integrity Reports: Ad Hoc Reporting for any kind of business need in HTML or XML format.

1. Integrity Gateway (Client)

The Integrity Gateway is a Java client application that can be run with user interaction or in silent mode.

The user can select one of the provided templates (export configurations) and the target file name. Then, the gateway reads and transforms the Integrity data to create MS Word output.

2. Integrity Reports (Server)

Integrity Reports are a server-based way to create HTML pages or XML files from Integrity. The layout is based on HTML templates, which are enriched with certain Integrity tags. Such tags allow, for example, to create repetitive sections based on the data provided (for data tables). Integrity reports are very powerful, although we have seen problems when complicated rich text or specific image types are involved.

Comparison Matrix:

Feature	Integrity Gateway	Custom Gateway*	Reporting Engine*	Integrity Reports	Report Gateway*
Supports Output in Word Format	Yes	Yes	-	-	Yes
Supports Output in Excel Format	-	Yes	Yes	-	Yes
Supports Output in HTML Format	-	-	-	Yes	Yes
Supports Output in XML Format	-	-	-	Yes	Yes
Supports direct PDF Export	-	Yes	-	-	Partially
Supports Scheduling	-	-	-	-	Planned
Supports E-Mail Distribution	-	-	-	-	Planned
Supports configurable Output File Names	-	Yes		-	Planned
Supports Archiving	-	-	-	-	Planned

We see that Integrity provides different techniques for different purposes (standard and non-standard*). All are valid approaches for the respective business case.

Reporting Customizations

The most common customization is the Custom Gateway, but the Reporting Engine is also very often used. The Report Gateway is brand new and combines the functionalities of Reports and the Gateway on Server.

The Custom Gateway is a local Java Application which sits on top of the Gateway and offers mainly the following:

• Flexible Output Path and configurable File Names
• PDF Generator
• Baseline Picker

In addition, the Custom Gateway can add Java functionality to any export. Herewith you can get almost all data from any location in Integrity and enrich your data export. Examples are: trace listing, administration configuration export, audit trail listing, test document export with test session and detailed test results. All this is done with additional API calls directly before the document generation process.

The Custom Gateway also works with Excel. A similar template concept with tags as in Word was implemented. This allows you to create professional looking preconfigured Excel files directly from Integrity including Excel charts.

 

The Reporting Engine is an ad-hoc reporting tool to create Trace listings in Excel. It is a stand-alone Java-based solution that is not related to the Integrity Gateway. The layout is template-based and the trace data to be listed can be configured in specific XML files.

The Integrity Report Gateway is one of the latest customizations that bring the Custom Gateway functionality to the server. It was created on customer request to avoid local Java files completely. This solution already replaces the local Integrity Gateway. To further develop this solution, additional sponsors are required (current state: conceptual study, but very stable, re-use possible on request).

At the moment, we do not have a common solution to meet automated report scheduling, e-mailing or archiving. Although Integrity offers all of these elements, we only have to put everything together.

Report Scheduling

Use Case: As a user, I want to create a report (one or multiple times) automatically from the system at a particular time or day, for example, for my own internal or external meetings with my clients as a preparation. The report should be generated automatically, that I do not have to log into the system and have to run it by hand entering the selection criteria again and again.

Report e-Mailing

Use Case: As a user, I would like to have a system generated report automatically sent to me or a definable user group, for example, for my own internal or external review meetings with my customers. The report should be automatically sent, so I do not have to log into the system, to select and send it manually.

Report Archiving

Use Case: As a user I would like that specific reports are automatically archived by the system when I have requested this. For example, I want the reports generated by the system sent to my clients to be archived, so I can keep track of them. A different example relates to audit-related reports I have prepared, which I must hold for several years.

Next Steps

I am interested in your approaches to solve today's Integrity internal reporting requirements. I am also interested in how you have implemented scheduling, emailing, and report archiving.

Thank you

(Known professional or semi-professional solutions in use by our clients:  PTC ThingWorx Apps, BIRT, Oracle BI Publisher)

[kjain-2]

Hi Volker,

Appreciate your knowledge and Report Scheduling,e-Mailing etc. all are fruitful feature and required as well for almost every customer or end user.

I would like to know a little about Integrity Custom Gateway. Is Integrity Custom Gateway is on your repository?

Thanks Volker for your kind support always.

Regards,

Kapil Jain

[VolkerEckardt]

Hello Kapil,

I would be pleased to share the Custom Gateway form with you for free. It's a Gateway request starting form, allowing you to gain from the features listed above. This does not cover the additional Java classes. They were created on customer request and would need some consulting contract to get trained and often also adapted for your individual needs.

But, if you are interested in the Form itself, I can share it with you shortly.

Volker

[kjain-2]

Hi Volker,

In new things am always interested.

Thanks a lot Volker.

Kapil

[twiatr]

Hello,

Maybe this post is a little bit outdated, but I discovered in it that a tool called "Custom Gateway" exists.

It seems to fulfill our need, namely an Excel reporting tool.

We are ILM 10.9 users, can anyone tell me :

1. If this tool is compatible with ILM 10.9 ?
2. If it is free and downloadable somewhere on the PTC site (I was unable to find it...)
3. It it's officially supported by PTC, or at least validated by PTC (FDO) ?

Thank you by advance for your anwer,

Best regards

[jhm]

Hi Volker,

I have a request to generate a Traceability  Report, and export it to Excel

I have the report engine that was created in 2014. Just wondering if there is a newer version.

Thanks

John

[NilsN]

Hi Volker,

Thanks for this interesting post. Currently, we are using the standard Integrity Gateway and the Enhanced Gateway for Word document generation, and a separate tool written in Java which creates a custom traceability report.

I am wondering: what do you think will be the future of reporting in Integrity? Do you think eventually a single engine will be created, so users have a single point of entry for their document creation? To me that would be a great improvement, and it seems like a big opportunity for PTC to accompany a powerful tool with a single, powerful reporting engine.

edit: in the future will probably be everyone working in a robust web client of Integrity, keeping local Java scripts will be quite tricky then..!

Regards, Nils

[VolkerEckardt]

Hi Nils,

thanks a lot for your post and your question.

Reporting is and was always something I worked with. For me, reporting is always a company wide decision, and shall be consistent across different tools and departments. If I would become responsible to setup the reporting for a company, I would choose an enterprise solution, and would ask all vendors to provide an option to deliver the data into it.

For me, enterprise solution means a central reporting database, a common layout, different export options, report scheduling, report distribution and strict access rights. The tool I worked with in the past was BI Publisher from Oracle. Also Power BI from Microsoft is very strong.

If your company has such a corporate solution, I'd be happy to help you with how Integrity data can be made available for these apps to report.

Volker

[ddeupree]

HI Volker,

We have a pilot implementation of Integrity 11.1 I would like to hook up to Power BI for reporting purposes.

I am not a programmer by any stretch of the imagination, but I am a quick study.

Please let me know how to get started.

Thanks,

David Deupree

Integrity PLM Manager

Johnson and Johnson Medical Device.

[VolkerEckardt]

Hi David,

we at PTC are not experts in Power BI, but let me help you finding a good approach to exchange data with Integrity.

The key question is what kind of integration Power BI likes most, is this REST (WebServices/SOAP), is this a JSON file, or somethign else?

The second question is what type of data your users want, whether it's real time or daily accuracy. For example, REST is in real time, a JSON file is batch = not in real time.

Keep in mind that a REST request can cause pressure on server, a batch file generation can be executed outside of the peek user hours. 

The third question is about security, or access rights. There might be a company IT rule in place that you are not allowed just to send data from somewhere to somewhere, and if the data have to be encrypted. Also in the target app, in Power BI, not everyone is allowed to see anything.

A forth question is about a good set of business use cases to play with.

I would start with finding an answer for all 4 questions, and in parallel I would ask a developer to build a pilot for the 2 main options  (REST(WebServices/SOAP) + JSON file).  

Once you know what you need, and how complicated it is to build, you can much better estimate, and finally find sponsors.

Does this help? Any follow up question is welcome!

[twiatr]

Hello,

I'm interested in Custom Gateway, notably in the Excel Export.

I've downloaded the latest CustomGateway on gitHub, and installed it.

But i've no idea on how to configure the "export-config" in gateway-tool-configuration.xml so that it can export an Excel file instead of a Word one (the exporter Id). Can anybody help me please ?

This is what I tried, with some other exporter id values such as "MSEXCEL", "EXCEL", "XLSX"

  <export-config info-name="Test Custom Gateway Excel">
   <name>Excel Custom Gateway</name>
   <description>Excel Custom Gateway</description>
   <exporter id="excel">
    <property name="template">C:\Integrity\gateway\export\Microsoft Excel\Excel Custom Gateway.xlsx</property>
   </exporter>
   <extension>xlsx</extension>
   <gateway-configuration-name>EXTERNAL_EXPORT-DEFAULT-DEFAULT</gateway-configuration-name>
  </export-config>

Thanks by advance,

Thomas WIATR

[VolkerEckardt]

Hi Thomas,

such a Gateway XML entry would look like this:

      <export-config>
         <name>Export Documents (Excel)</name>
         <description>Excel Export Test Suite with Macro</description>
         <exporter class="com.ptc.services.gateway.exporter.excel.ExcelExport">
            <property name="template">http://localhost:7001/gateway/export/Microsoft Excel/Template_Excel_Import_Test_Suite.xlsx</property>
            <property name="type">something</property>
         </exporter>
         <extension>xlsx</extension>
         <display-fields>
            <display-field>DOCUMENT_TITLE</display-field>
         </display-fields>
         <gateway-configuration-name>Excel Export Test Document</gateway-configuration-name>
      </export-config>

This "something" has to be set to the Excel Export Type that you want to have as an output. Valid types are coded in the com.ptc.services.gateway.exporter.excel.ExcelExport, which is a Java Class in IntegrityCustomExport.jar.  IntegrityCustomExport.jar has to be registered in the gateway.lax.

So, the IntegrityCustomGateway as such is not enough to get Excel.

Does this clarify?

Volker

[twiatr]

Hi,

Thanks a lot for your quick answer.

It 's clear to me (at least it seems to be ... I hope I didn't misunderstand !).

I'll test that this week.

Thanks again

Thomas

[chirag_kpit]

Hi Volker,

I am interested in knowing how we can transfer Integrity data to Power BI.

Regards,

Chirag Agarwal

[ddeupree]

That is functionality we would like to explore as well.  We were also interested in looking into the use of Amazon Quicksight.  https://aws.amazon.com/quicksight/ since we utilize AWS for some of our environments.  Curious to see if there is a way to do that as well.  Not an IT person, but as a business owner of the platform, want to get churn data, time versus change information.

[아한]

Hi Volker,

I would like to use this Integrity Custom Gateway.

Can you share your knowledge creation?

Thanks

Regards,

Han