SSI 30th Anniversary
        • Design
        • The most significant opportunities to impact the cost of building and operating a ship are found in the design and engineering phase.

          Initial Design  |  Basic Design  |  Detailed Design

        • Build
        • Even a smaller shipbuilding project is immense in scope and scale. Manage the challenges that are unique to ship construction.

          Prepare  |  Fabricate  |  Assemble

        • Maintain
        • With the majority of a ship’s life taking place after it’s been built, it’s crucial to ensure that the organization has a clear picture of the vessel at all times.

          Assess  |  Manage  |  Repair / Refit

        • Nexus
        • SSI Nexus is the place where users, creators, & implementers of SSI software get together. Here they discuss best practices & industry trends, tackle common challenges, gain access to the latest software, and provide input into the future of the products that bring them together.

        • MyLearning
        • SSI MyLearning is where SSI users can access detailed training exercises, materials, courses, and certifications. The self-directed training curriculum ensures that training happens on your schedule and when you need it most.

        • SSI Blogs
        • The SSI blog is your place to get insights into the intersection of shipbuilding and technology, how our industry is moving forward, and keep up with SSI news. It’s the only place to read the latest from Denis Morais and Darren Larkins, SSI’s co-CEOs.

          Lighthouse Waveform  |  Crow’s Nest

        • Company
        • Learn more about SSI and our leadership.

        • Locations & Contact
        • You need a partner with a global presence.

        • News
        • The latest on SSI and shipbuilding.

        • Events
        • Join us at our next event, conference, or trade show.

        • Partners
        • Learn more about our Platform and Development Partners.

        • Clients
        • See the industry leaders who trust SSI.

        • Careers
        • Help us make the business of shipbuilding possible.

January 19, 2016
Best PracticesEnterprisePlatformPublisherLTShipConstructorTips & 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.

Generate Report with Template

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:

  1. Part Report
  2. Profile Cutting Report
  3. Assembly Report
  4. 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

  1. Unzip the packages included in this post (below)
  2. Copy EnterprisePlatform folder to the root folder of your project
  3. 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
  4. Open Manager in your ShipConstructor project
  5. Import ShipConstructor 2016 R2 BOM.xml

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


How-To Generate Reports

  1. Open PublisherLT
  2. Connect to your project
  3. Set the folder where you would like the reports to be generated
  4. Select Load Manager
  5. Select Load Criteria XML
  6. Select file EnterprisePlatform\Automation\Reports\AllPartsInProject.xml in the attached package
  7. Select Load Manager
  8. Select Load Criteria XML
  9. Select file EnterprisePlatform\Automation\Reports\AllPrimaryAssemblies.xml in the attached package
  10. Select Load Manager
  11. Select Load Criteria XML
  12. Select file EnterprisePlatform\Automation\Reports\AllWeldsInProject.xml in the attached package
  13. Select Import
  14. Select file EnterprisePlatform\Automation\Reports\GenerateReports_Workpackage.xml in the attached package
  15. 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:

  1. Follow the instructions above to set up your project.
  2. Make any changes to the BOM in your project. You can add or remove whatever attributes you would like.
  3. 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.
  4. Open the generated report.
  5. 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.
  6. 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.

Post Comments

  1. nick danese says:

    a w e s o m e !
    thank you, Denis.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.