Filtering by Discipline when running Reports in Bentley Substation

Good Day

I am busy working on customizing a report in Bentley Substation. Currently i have managed to customize the report to output all the parts used in a project, quantity and cost them. Now i am looking to further customize the report to break it down per discipline (i.e. structures, foundations, equipment etc.) - see below.

The problem that i am finding is that it i cannot apply a filter under the field column to select "Discipline". Why is this the case?

All comments or suggestions will be welcomed. 

Mohammad

  • Hi Mohammad. Reports can only be filtered using the fields that are present in the query on which the report template is based. Such fields can be seen in the Field List > Data Fields area of Report Template Designer when the template is open. I would guess that Discipline is not included in the report template/query and so you cannot use that field for Filtering.

    To add a field that is not available in the Data Fields area when modifying the template, you would need to create a custom SQL query and base a new template on that custom query. This may or may not be feasible, depending on the field and base query. What existing report template or Predefined Report Type was used as a basis for your custom template?



  • Hi Matt

    Thanks for the prompt response. I find it very strange that all other fields are accessible in the filter besides "Discipline". What was the reasoning for this? By filtering by Discipline one could easy breakdown reports for for the various disciplines involved in Substation Engineering (Earth Mat, Terrace, Foundation, Steelwork etc.).

    The predefined report type being used is "Bill of Materials (Quantified)"

    Kind Regards

    Mohammad

    Bentley Substation v8i (SELECTseries 8)
    Version Number: 08.11.13.140

  • I agree that including a Discipline field in the BOM reports would be useful, so I've submitted a request for this to be included in a future release. The tracking number is TFS 862870; I will send a separate confirmation email to you.

    In the meantime, adding Discipline to the "Bill of Materials (Quantified)" report is simple since the Discipline value is read from the same table as most of the other data in the report. So you could add "PartNumber.Discipline" to the SELECT statement in the rep_QtyOfBOM stored procedure in the project database.

    Alternatively, you could create a new report template using Report Template Designer , choose the Customized By SQL Statement option on the New Report Template dialog, and paste in the query below. This will create a report template that has a column for each field in the query, so you will need to remove unwanted columns and format the template as desired if you use this method.

    SELECT  GetRepDevPartNo.Installation, GetRepDevPartNo.Installation_ANUM, GetRepDevPartNo.Location, GetRepDevPartNo.Location_ANUM, 
    GetRepDevPartNo.id,GetRepDevPartNo.DeviceTag, GetRepDevPartNo.DeviceTag_ANUM, GetRepDevPartNo.partnumber, GetRepDevPartNo.balloon, 
    GetRepDevPartNo.balloon_ANUM, GetRepDevPartNo.type, GetRepDevPartNo.quantity AS QUANTITY, .0 AS spare, PartNumber.Quantity_Per_Unit, 
    PartNumber.Category, PartNumber.Manufacturer, PartNumber.Catalog_Number, PartNumber.Supplier, PartNumber.Part_type, PartNumber.Use_Unit, 
    PartNumber.Order_Unit, PartNumber.Detailed_Description1, PartNumber.Detailed_Description2, PartNumber.Detailed_Description3, 
    PartNumber.Detailed_Description4, PartNumber.Detailed_Description5, PartNumber.X, PartNumber.Y, PartNumber.Z, PartNumber.Weight, 
    PartNumber.Power_Consumption, PartNumber.Selling_Price, PartNumber.Buying_Price AS BUYING_PRICE, PartNumber.Discount AS DISCOUNT, PartNumber.Assembly_Time, 
    PartNumber.Wiring_Time, PartNumber.hour_cost, PartNumber.Additional_Part, PartNumber.Date_Created, PartNumber.Date_Modified, 
    PartNumber.Device_Family, PartNumber.Attribute_Text, PartNumber.Single_Wire_Connection, PartNumber.Schematic_Symbol, 
    PartNumber.Layout_Symbol, PartNumber.Wire_Diagram_Symbol, PartNumber.Hydraulic_Symbol, PartNumber.Pneumatic_Symbol, 
    PartNumber.Single_Line_Symbol, PartNumber.Graphical_Plan_Symbol, PartNumber.Percent_Spare, PartNumber.Discipline,PartNumber.user1,PartNumber.user2,PartNumber.user3,PartNumber.user4,PartNumber.user5,PartNumber.user6,PartNumber.user7,PartNumber.user8,PartNumber.user9,PartNumber.user10,PartNumber.user11,PartNumber.user12,
    PartNumber.user13,PartNumber.user14,PartNumber.user15,PartNumber.user16,PartNumber.user17,PartNumber.user18,PartNumber.user19,PartNumber.user20,PartNumber.user21,PartNumber.user22,PartNumber.user23,PartNumber.user24,PartNumber.user25,PartNumber.user26,PartNumber.user27,PartNumber.user28,PartNumber.user29,PartNumber.user30,PartNumber.user31,PartNumber.user32,PartNumber.user33,PartNumber.user34,PartNumber.user35,PartNumber.user36,PartNumber.user37,PartNumber.user38,PartNumber.user39,PartNumber.user40,PartNumber.user41,
    PartNumber.user42,PartNumber.user43,PartNumber.user44,PartNumber.user45
    FROM PartNumber RIGHT OUTER JOIN GetRepDevPartNo ON PartNumber.project_id = GetRepDevPartNo.project_id AND PartNumber.part_number = GetRepDevPartNo.partnumber WHERE GetRepDevPartNo.project_id=@projectid