Tips & Tricks: Generate Your Company Standard Reports

Published: January 19, 2016
Categories: Best Practices • EnterprisePlatform • PublisherLT • ShipConstructor • Tips & Tricks

EnterprisePlatform Tips & Tricks Generate Reports usin Template

Reporting is one of the most critical things we do throughout the entire lifecycle of our project. As you know, I am a very big proponent of leveraging the full set of data we have in various representations (2D, 3D, 4D) to represent information in more intuitive ways. This allows us to communicate information with various stakeholders much quicker. Many of these immersive reports that I have blogged about use the 3D model and modern visualization to communicate the important information in a very clear and concise manner. However, I fully understand and respect the need to continue to generate traditional 2D reports.
Every company I visited has used unique ways to communicate and report information. One common theme is that they almost always revolve around using Excel. It starts with having an Excel template where data from the live project is populated in a manual or semi-manual workflow. In this blog post I will demonstrate how you or anyone in your organization can generate an up-to-date report using your company’s standard Excel templates. This will allow you to continue to leverage your Excel reports and their familiarity throughout your organization. It will also enable anyone (no longer an engineering function:)) generate these reports with less than 3 minutes of effort.

Overview

For those who do not have access to YouTube click here

In EnterprisePlatform 2016 R2 we added the capability to load virtually any information from the ShipConstructor MIM into a specific Excel template source tab. This allows you to use Excel’s features to create some very rich and traditional reports that you are currently using in your company.

If you refer to the image above, the Source tab is the information which will automatically be populated by PublisherLT. All the other tabs have reports from which information is driven by the data contained in the Source tab. This means that once the Source tab data is updated the reports will automatically reflect the changes and be updated.

There is no limit to the number of reports you can generate or Excel templates you can use. A best practice which current users are using is to have a specific template per job function. For example your weight engineer, detail planner, purchaser, project manager, various managers, engineering manager, VP, QC/QA, weld engineers, etc. will have their own Excel template. There is definitely overlap in some of these reports but the goal that has worked best is to keep the Excel templates focused on a specific job function.

The benefit of having many Excel templates is that the responsibility of who is allowed to change and modify it is very clear. The templates can exist anywhere on your network allowing a person or even a single department to manage the template(s). When anyone generates a report using PublisherLT, it will always use the latest version of the template which makes “pushing” the template to your team very easy and non-disruptive.

Workflow for setup

The first thing we need to do is set up your project and generate some Excel templates. In the attached package I have included some Excel templates and other files to get you generating reports very quickly. The templates and EnterprisePlatform operations I have provided may require you to modify them to support your company’s standards; however, it should get you familiar with generating reports using an Excel template.

In my package I have included 4 Excel template reports:

Part Report
Profile Cutting Report
Assembly Report
Weld Report

It is most likely that not all of them will work on your project since I am not sure how your project is set up. For example if you do not use WeldManagement or do profile nesting you will not get any results from those reports.

How-To Set Up your Project and Generate Reports Video

For those who do not have access to YouTube click here

How-To Set Up your Project

Unzip the packages included in this post (below)
Copy EnterprisePlatform folder to the root folder of your project
Copy operations.xml to c:\users\USERNAME\AppData\Roaming\SSI\EnterprisePlatform 2016 R2\
1. If you have a modified operations.xml file then you will need to follow the steps in the PublisherLT Operations section
Open Manager in your ShipConstructor project
Import ShipConstructor 2016 R2 BOM.xml

That is pretty much it for setting up your project.:)

How-To Generate Reports

Open PublisherLT
Connect to your project
Set the folder where you would like the reports to be generated
Select Load Manager
Select Load Criteria XML
Select file EnterprisePlatform\Automation\Reports\AllPartsInProject.xml in the attached package
Select Load Manager
Select Load Criteria XML
Select file EnterprisePlatform\Automation\Reports\AllPrimaryAssemblies.xml in the attached package
Select Load Manager
Select Load Criteria XML
Select file EnterprisePlatform\Automation\Reports\AllWeldsInProject.xml in the attached package
Select Import
Select file EnterprisePlatform\Automation\Reports\GenerateReports_Workpackage.xml in the attached package
Click Run

Additional notes

The reports I generate use PowerPivot. If you do not have PowerPivot then you will not be able to view the majority of the reports I have created.

You can create your own reports from scratch; however, if you want to use the ones I provided as a base I would suggest:

Follow the instructions above to set up your project.
Make any changes to the BOM in your project. You can add or remove whatever attributes you would like.
Generate the report following the steps above.
1. This is so that it contains your project information and the changes you made to the BOM. This will make it easier when creating your new template.
Open the generated report.
Make any modifications to the Excel Report.
1. Remove any tabs.
2. Create new tabs.
3. Just do not change or delete the Source tab.
Save your new template in the EnterprisePlatform\Report\ folder with the exact same name as the previous template.

PublisherLT Operations

If you have not modified your operations.xml file with your own operations you can simply copy the one provided in the attached package into c:\users\USERNAME\AppData\Roaming\SSI\EnterprisePlatform 2016 R2\.

If you have modified your operations.xml file you will need to edit the operations.xml file by inserting the below two sets of text:

1. Copy below code between <globals></globals>

<define name="PrimaryPHList">{Primary}</define> <!-- ${PrimaryPHList}  -->
<define name="PrimaryPH">PRIMARY</define> <!-- ${PrimaryPH}  -->
<define name="WFWeldBOMFull">Waveform Weld Full</define> <!-- ${WFWeldBOMFull}  -->
<define name="WFAssemblyBOMFull">Waveform Assembly Full</define> <!-- ${WFAssemblyBOMFull}  -->
<define name="WFPartBOMFull">Waveform Part Full</define> <!-- ${WFPartBOMFull}  -->
<define name="WFProfileCuttingBOM">Waveform Profile Cutting</define> <!-- ${WFProfileCuttingBOM}  -->

2. Copy below code between <generalgroupoperations></generalgroupoperations>

<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<!--Weld Properties to TSV, CSV or XML-->
<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<operation id="Waveform Weld Report" appliesto="welds">
<define name="operation.timeout">
<invoke function="GetOperationTimeout">
<property name="list.ids" />
<int>2</int>
<property name="default.startup.timeout" />
</invoke>
</define>


<define name="WeldReportFolder"><pathcombine><property name="rootdir"/><string>WeldReport</string></pathcombine></define>
<makedir>${WeldReportFolder}</makedir>


<!-- Weld Information -->
<define name="WeldTSVFilename">Welds.tsv</define>
<define name="WeldFullFilename"><pathcombine><property name="WeldReportFolder" /><property name="WeldTSVFilename" /></pathcombine></define>
<scon timeoutinsec="${operation.timeout}">EPEXPORTWELDPROPERTIES ${list.ids} "${WFWeldBOMFull}" "" "${WeldReportFolder}" ${WeldTSVFilename}</scon>


<!-- Assembly Information -->
<define name="AssemblyFilename">Assembly.tsv</define>
<define name="AssemblyFullFilename"><pathcombine><property name="WeldReportFolder" /><property name="AssemblyFilename" /></pathcombine></define>
<scon timeoutinsec="${operation.timeout}">EPEXPORTASSEMBLYPROPERTIESFROMPH "${PrimaryPH}" "${WFAssemblyBOMFull}" "" "${WeldReportFolder}" ${AssemblyFilename}</scon>


<!-- Combine -->
<define name="joinKeyColumn">AssemblyGUID</define>
<define name="baseKeyColumn">AssemblyGUID</define>
<define name="WeldTSVReportFilename">WeldReport.tsv</define>
<define name="WeldReportTSVFullFilename"><pathcombine><property name="WeldReportFolder" /><property name="WeldTSVReportFilename" /></pathcombine></define>


<scon timeoutinsec="${operation.timeout}">EPJOINDATA "${WeldFullFilename}" "${AssemblyFullFilename}" "${baseKeyColumn}" "${joinKeyColumn}" "${WeldReportFolder}" ${WeldTSVReportFilename}</scon>


<!-- Copy Weld Template -->
<define name="WeldTemplate"><pathcombine><property name="projectdirectory"/><string>EnterprisePlatform</string><string>Reports</string><string>WeldReports</string><string>WeldReportTemplate.xlsx</string></pathcombine></define>
<define name="WeldReport">WeldReport.xlsx</define>
<define name="WeldReportFullName"><pathcombine><property name="WeldReportFolder" /><property name="WeldReport" /></pathcombine></define>
<if>
<test><fileexists>${WeldTemplate}</fileexists></test>
<iftrue>
<copyfile sourcefile="${WeldTemplate}" destinationfolder="${WeldReportFolder}" newfilename="${WeldReport}" overwrite="true"/>
<log>copyfile sourcefile: "${WeldTemplate}" destinationfolder:"${WeldReportFolder}" newfilename:"${WeldReport}"</log>
</iftrue>
<iffalse>
<log>DID NOT COPY TEMPLATE copyfile sourcefile: "${WeldTemplate}" destinationfolder:"${WeldReportFolder}" newfilename:"${WeldReport}"</log>
</iffalse>
</if>


<excelimporttotable workbook="${WeldReportFullName}" sheetname="Source">${WeldReportTSVFullFilename}</excelimporttotable>
<associate inputitems="${list.ids}">${WeldReportFullName}</associate>


</operation>


<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<!--Part Properties to TSV, CSV or XML-->
<!-- Need to change Product Hierarchy Name to one that exists in project. Default: PRIMARY -->
<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<operation id="Waveform Part Report" appliesto="parts">
<define name="operation.timeout">
<invoke function="GetOperationTimeout">
<property name="list.ids" />
<int>2</int>
<property name="default.startup.timeout" />
</invoke>
</define>


<define name="PartReportFolder"><pathcombine><property name="rootdir"/><string>PartReport</string></pathcombine></define>
<makedir>${PartReportFolder}</makedir>


<!-- Generate Part Property information -->
<define name="productHierarchies">${PrimaryPHList}</define>
<define name="PartTSVReport">PartReport.tsv</define>
<define name="PartTSVReportFullName"><pathcombine><property name="PartReportFolder" /><property name="PartTSVReport" /></pathcombine></define>


<scon timeoutinsec="${operation.timeout}">EPEXPORTPARTPROPERTIES ${list.ids} "${WFPartBOMFull}" "" "${productHierarchies}" "${PartReportFolder}" "${PartTSVReport}"</scon>


<!-- Generate Part Creation and deletion information -->
<define name="PartCreationDeletionTSVReport">PartCreationDeletionReport.tsv</define>
<define name="PartCreationDeletionTSVReportFullname"><pathcombine><property name="PartReportFolder" /><property name="PartCreationDeletionTSVReport" /></pathcombine></define>


<sqlcommand outputfile="${PartCreationDeletionTSVReportFullname}">exec EP_spPartCreationDeletionInfo</sqlcommand>


<!-- Copy Part Template -->
<define name="PartTemplate"><pathcombine><property name="projectdirectory"/><string>EnterprisePlatform</string><string>Reports</string><string>PartReports</string><string>PartReportTemplate.xlsx</string></pathcombine></define>
<define name="PartReport">PartReport.xlsx</define>
<define name="PartReportFullName"><pathcombine><property name="PartReportFolder" /><property name="PartReport" /></pathcombine></define>


<if>
<test><fileexists>${PartTemplate}</fileexists></test>
<iftrue>
<copyfile sourcefile="${PartTemplate}" destinationfolder="${PartReportFolder}" newfilename="${PartReport}" overwrite="true"/>
<log>copyfile sourcefile: "${PartTemplate}" destinationfolder:"${PartReportFolder}" newfilename:"${PartReport}"</log>
</iftrue>
<iffalse>
<log>DID NOT COPY TEMPLATE copyfile sourcefile: "${PartTemplate}" destinationfolder:"${PartReportFolder}" newfilename:"${PartReport}"</log>
</iffalse>
</if>


<!-- Populate Excel Template -->
<excelimporttotable workbook="${PartReportFullName}" sheetname="Source">${PartTSVReportFullName}</excelimporttotable>
<excelimporttotable workbook="${PartReportFullName}" sheetname="DeleteCreate">${PartCreationDeletionTSVReportFullname}</excelimporttotable>
<associate inputitems="${list.ids}">${PartReportFullName}</associate>


</operation>


<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<!--Assemby Properties to TSV, CSV or XML-->
<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<operation id="Waveform Assembly Report" appliesto="assemblies">
<define name="operation.timeout">
<invoke function="GetOperationTimeout">
<property name="list.ids" />
<int>2</int>
<property name="default.startup.timeout" />
</invoke>
</define>


<define name="AssemblyReportFolder"><pathcombine><property name="rootdir"/><string>AssemblyReport</string></pathcombine></define>
<makedir>${AssemblyReportFolder}</makedir>


<!-- Generate Assembly Property information -->
<define name="AssemblyTSVReport">AssemblyReport.tsv</define>
<define name="AssemblyTSVReportFullname"><pathcombine><property name="AssemblyReportFolder" /><property name="AssemblyTSVReport" /></pathcombine></define>


<scon timeoutinsec="${operation.timeout}">EPEXPORTASSEMBLYPROPERTIES ${list.ids} "${WFAssemblyBOMFull}" "" "${AssemblyReportFolder}" "${AssemblyTSVReport}"</scon>


<!-- Copy Assembly Template -->
<define name="AssemblyTemplate"><pathcombine><property name="projectdirectory"/><string>EnterprisePlatform</string><string>Reports</string><string>AssemblyReports</string><string>AssemblyReportTemplate.xlsx</string></pathcombine></define>
<define name="AssemblyReport">AssemblyReport.xlsx</define>
<define name="AssemblyReportFullname"><pathcombine><property name="AssemblyReportFolder" /><property name="AssemblyReport" /></pathcombine></define>


<if>
<test><fileexists>${AssemblyTemplate}</fileexists></test>
<iftrue>
<copyfile sourcefile="${AssemblyTemplate}" destinationfolder="${AssemblyReportFolder}" newfilename="${AssemblyReport}" overwrite="true"/>
<log>copyfile sourcefile: "${AssemblyTemplate}" destinationfolder:"${AssemblyReportFolder}" newfilename:"${AssemblyReport}"</log>
</iftrue>
<iffalse>
<log>DID NOT COPY TEMPLATE copyfile sourcefile: "${AssemblyTemplate}" destinationfolder:"${AssemblyReportFolder}" newfilename:"${AssemblyReport}"</log>
</iffalse>
</if>


<!-- Populate Excel Template -->
<excelimporttotable workbook="${AssemblyReportFullname}" sheetname="Source">${AssemblyTSVReportFullname}</excelimporttotable>
<associate inputitems="${list.ids}">${AssemblyReportFullname}</associate>


</operation>


<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<!--Profile Cutting Report to TSV, CSV or XML-->
<!--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-->
<operation id="Waveform Profile Cutting Report" appliesto="parts">
<define name="operation.timeout">
<invoke function="GetOperationTimeout">
<property name="list.ids" />
<int>2</int>
<property name="default.startup.timeout" />
</invoke>
</define>
<define name="ProfileCuttingReportFolder"><pathcombine><property name="rootdir"/><string>ProfileCuttingReport</string></pathcombine></define>
<makedir>${ProfileCuttingReportFolder}</makedir>


<!-- Copy Profile Cutting Template -->
<define name="ProfileCuttingTemplate"><pathcombine><property name="projectdirectory"/><string>EnterprisePlatform</string><string>Reports</string><string>ProfileCuttingReports</string><string>ProfileCuttingReportTemplate.xlsx</string></pathcombine></define>
<define name="ProfileCuttingReport">ProfileCuttingReport.xlsx</define>
<define name="ProfileCuttingReportFullname"><pathcombine><property name="ProfileCuttingReportFolder" /><property name="ProfileCuttingReport" /></pathcombine></define>


<!-- Generate Profile Cutting Property information -->
<define name="ProfileCuttingTSVReport">ProfileCuttingReport.tsv</define>
<define name="ProfileCuttingTSVReportFullname"><pathcombine><property name="ProfileCuttingReportFolder" /><property name="ProfileCuttingTSVReport" /></pathcombine></define>
<scon timeoutinsec="${operation.timeout}">EPEXPORTPARTPROPERTIES ${list.ids} "${WFProfileCuttingBOM}" "" "${PrimaryPHList}" "${ProfileCuttingReportFolder}" "${ProfileCuttingTSVReport}"</scon>


<if>
<test><fileexists>${ProfileCuttingTemplate}</fileexists></test>
<iftrue>
<copyfile sourcefile="${ProfileCuttingTemplate}" destinationfolder="${ProfileCuttingReportFolder}" newfilename="${ProfileCuttingReport}" overwrite="true"/>
<log>copyfile sourcefile: "${ProfileCuttingTemplate}" destinationfolder:"${ProfileCuttingReportFolder}" newfilename:"${ProfileCuttingReport}"</log>
</iftrue>
<iffalse>
<log>DID NOT COPY TEMPLATE copyfile sourcefile: "${ProfileCuttingTemplate}" destinationfolder:"${ProfileCuttingReportFolder}" newfilename:"${ProfileCuttingReport}"</log>
</iffalse>
</if>


<!-- Populate Excel Template -->
<excelimporttotable workbook="${ProfileCuttingReportFullname}" sheetname="Source">${ProfileCuttingTSVReportFullname}</excelimporttotable>
<associate inputitems="${list.ids}">${ProfileCuttingReportFullname}</associate>
</operation>

Package

You can download the package here.