Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
AutoPLANT | OpenPlant | PlantWise
  • Product Communities
  • More
AutoPLANT | OpenPlant | PlantWise
AutoPLANT | OpenPlant | PlantWise Wiki How to Generate Consolidated BOM for Components using Pivot Table
    • Sign in
    • -AutoPLANT | Bentley Raceway and Cable Management | OpenPlant | PlantWise Wiki
      • -OpenPlant
        • +CONNECT Services - OpenPlant
        • +Example Configuration - CONNECT Edition
        • +Installations - OpenPlant CONNECT Edition Applications
        • +OpenPlant Download Links & Videos
        • OpenPlant Product Compatibility
        • +OpenPlant Modeler CONNECT
        • +OpenPlant PID CONNECT
        • +OpenPlant Isometrics Manager CONNECT
        • +OpenPlant Orthographics Manager CONNECT
        • +OpenPlant Support Engineering CONNECT
        • +OpenPlant Project Administrator CONNECT
        • +OpenPlant CONNECT Edition-PlantSight Integration
        • +OpenPlant Modeler v8i
        • +OpenPlant Isometrics Manager v8i
        • +OpenPlant Project Administrator v8i
        • +OpenPlant PID v8i
        • +OpenPlant Orthographics Manager v8i
        • +OpenPlant Support Engineering v8i
        • +OpenPlant ModelServer v8i
        • -OpenPlant Reporting v8i
          • Error "No default report template is defined for this report" Appears While Report Generation
          • How to Add/Remove HVAC Component Classes in Grid Report
          • How To Cascade Pipeline Properties to Bolt Report in OpenPlant Modeler
          • How to create Cable Tray Material GRID Report
          • How to Exclude Existing Components from OpenPlant Modeler Reports
          • How to Export Grid Reports in OpenPlant Modeler
          • How to Extract Reports for Multiple Component Class in OpenPlant Modeler
          • How to Generate Consolidated BOM for Components using Pivot Table
          • How to Include Equipment Origin in DGN reports
          • How to Include XYZ Coordinates in Reports for Piping Components
          • How to Insert Report into Drawing in OpenPlant Modeler
          • +How to Show Branch Diameter In Piping Reports
          • How to Show Nozzle Elevation in Report
          • To Add Pipe Allowances as Field Fit Length in Grid Report
          • To Create BOM for Single Pipeline Using Query Option in OpenPlant Modeler
          • To Generate Weld Quantity for Slip On Joints in Weld Reports
          • To Obtain Bend Angle For Pipe Bends/Mitered Bends in reports, Query by Piping Components
          • To Trim Piping BOM Reports Template Fields in OpenPlant Modeler
        • How to Connect with Bentley Technical Support Team using Microsoft Teams
        • How to Connect with Bentley Technical Support Team using Microsoft Teams via Browser
      • +Bentley AutoPLANT
      • +Bentley Navigator
      • +Bentley PlantSpace
      • +Plant Project Tools
      • +Bentley Raceway and Cable Management
      • i-model Composition Service for S3D
      • Working from home with OpenPlant
      • Working from home with Bentley Raceway and Cable Management
      • Working from home with AutoPLANT Modeler
    • How to Associate the Specification Based on Service in OpenPlant Modeler Connect

     
     Questions about this article, topic, or product? Click here. 

    How to Generate Consolidated BOM for Components using Pivot Table

    Applies To 
    Product(s): OpenPlant Reporting
    Version(s): 08.11.09.564 (SS5) | 08.11.11.87 (SS6)
    Environment:  Windows 7/8.1/10 (64 bit)
    Area:  Reports
    Subarea:  Piping Component
    Original Author: Rahul Kumar, Bentley Product Advantage Group

    Overview

    In OpenPlant Modeler, to Customize BOM reports as per requirement by user.

    Background

    When generating BOM report for Piping components it generates an output of each component of same group individually and not getting consolidated with respect to group it falls under. Sometimes it is required to generate a consolidated Summary report based on piping component class. Below steps will aid user to get the desired output using MS feature, Pivot Table option.

    Steps to Accomplish

    1. Launch OpenPlant Modeler, Select Project unit. Say OPModeler_Metric here.

    2. Go to OpenPlant Modeler>Tools>Item Reports.



    3. From Piping>Piping Component, select BOM type BOM by Line Number. 

    4. Right click on it, Select ‘Edit’. 



    5. Click on Query under Edit Report dialog.



    6. Select Multiple types of Items, to enable it to select multiple classes to choose.



    7. Select Seal, Piping Component, Bolt like shown in below image.



    8. Once selection done, it will look like below. ENSURE the button as highlighted below should be enabled, un-selection may result to error.



    9. Open as Grid.



    10. Export Report, using icon present in top left of report window.



    11. Save it .xlsx format to further work for Pivoting it.



    12. Open, saved xlsx file and select EC Class name and Length at a time by dragging.



    13. Click on Pivot Table after selection. Insert>Pivot Table.



    14. Create Pivot Table option pops up. You may select the range or keep it unchanged as based on selection Sheet/Range already got selection. Select New worksheet like shown in below image.



    15. This will create a new worksheet, ready for pivoting.


    16. Click on EC Class Name and Length. This will pivot all components and give a count.



    17. Notice Pipe reports to Pipe Quantity here. To Set Pipe as SUM of length. Right click on Pivot Table created > Value Field Settings. See image below.



    18. Set the value from Count of length to Sum of length. Click ok.



    19. Copy all previous fields to one place and also paste the sum of length for pipe to it. See below as final consolidated BOM.



    20. Above Mentioned steps is one example which facilitates user to create a report type. By selecting other columns user can get different types of report. 

    See Also

    http://communities.bentley.com/products/plant/design___engineering/w/plant_design_and_engineering__wiki/4352.openplant-reporting

    Comments or Corrections?

    Bentley's Product Advantage Group requests that you please confine any comments you have on this Wiki entry to this "Comments or Corrections?" section. THANK YOU!

    • OPR
    • SS5
    • 08.11.09.XXX
    • Consolidated BOM
    • SS6
    • 08.11.11.96
    • Pivot Table
    • OpenPlant Reporting
    • Share
    • History
    • More
    • Cancel
    • Rahul Kumar Created by Bentley Colleague Rahul Kumar
    • When: Tue, Aug 23 2016 9:28 AM
    • Rahul Kumar Last revision by Bentley Colleague Rahul Kumar
    • When: Tue, Aug 23 2016 9:30 AM
    • Revisions: 2
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2021 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies