Confirm

Back to discussions
Expand all | Collapse all

Report for information for feature attributes

  • 1.  Report for information for feature attributes

    Posted 14 days ago
    Hello,
    We have a number of features in the system with multiple attributes recorded against them but for the life of me I can't pull together a report in SQL that will give me all of the information as in the screen shot below.

    Sample of feature attributes
    Ideally we just want one report with
    Site Code,    Asset Number,  then all of the attributes "T:" across the top of the report and information that is in the boxes.

    Is this possible, and if so, how?


    ------------------------------
    Rob Morris
    Telford & Wrekin Council
    Telford
    ------------------------------


  • 2.  RE: Report for information for feature attributes

    Posted 14 days ago
    Hi Rob

    If you select the feature table on the Data Source Feature table and expand the Attributes tab it shows all the Attributes that are linked to Features.

    Note that the 'Attributes' shown on the Attribute tab on the Feature screen include Attribute Types, Date Types and Measurement Types.

    Alternatively you could use this SQL that has seperate columns for Attribute Type, Date Type and Measurement Type data.

    SELECT feature.site_code,
    feature.plot_number,
    attribute_type.attrib_type_code,
    attribute_type.attrib_type_name,
    attribute_value.attrib_value_code,
    attribute_value.attrib_value_name,
    feat_attrib_type.feat_attrib_notes,
    null_string,
    null_string,
    null_dateTime,
    null_string,
    null_string,
    null_string,
    null_decimal_0,
    null_string
    FROM feat_attrib_type,
    feature,
    attribute_type,
    attribute_value,
    system_default
    WHERE feature.site_code = feat_attrib_type.site_code
    AND feature.plot_number = feat_attrib_type.plot_number
    AND attribute_type.attrib_type_code = feat_attrib_type.attrib_type_code
    AND attribute_value.attrib_type_code = feat_attrib_type.attrib_type_code
    AND attribute_value.attrib_value_code = feat_attrib_type.attrib_value_code
    UNION
    SELECT feature.site_code,
    feature.plot_number,
    null_string,
    null_string,
    null_string,
    null_string,
    null_string,
    date_type.date_type_code,
    date_type.date_type_name,
    feature_date.feature_date,
    feature_date.feature_date_notes,
    null_string,
    null_string,
    null_decimal_0,
    null_string
    FROM feature,
    date_type,
    feature_date,
    system_default
    WHERE
    feature.site_code = feature_date.site_code AND
    feature.plot_number = feature_date.plot_number AND
    date_type.date_type_code = feature_date.date_type_code
    UNION
    SELECT feature.site_code,
    feature.plot_number,
    null_string,
    null_string,
    null_string,
    null_string,
    null_string,
    null_string,
    null_string,
    null_dateTime,
    null_string,
    measurement_type.measurement_code,
    measurement_type.measurement_name,
    feat_measurement.feature_quantity,
    unit_of_measure.unit_name
    FROM
    feature,
    feat_measurement,
    measurement_type,
    unit_of_measure,
    system_default
    WHERE
    feature.site_code = feat_measurement.site_code AND
    feature.plot_number = feat_measurement.plot_number AND
    measurement_type.measurement_code = feat_measurement.measurement_code AND
    unit_of_measure.unit_code = measurement_type.unit_code;




    ------------------------------
    Chris Wareham
    Principal Software Support Analyst
    Confirm
    ------------------------------

    Original Message


  • 3.  RE: Report for information for feature attributes

    Posted 11 days ago
      |   view attached
    Hi Rob

    Are you looking for something like attached?



    ------------------------------
    Mick Bird
    London Borough Of Haringey
    London
    ------------------------------

    Attachment(s)

    pdf
    Trees.pdf   55 KB 1 version
    Original Message


  • 4.  RE: Report for information for feature attributes

    Posted 11 days ago
    Hello
    Yes this is exactly what I'm after!!
    Ideally one line for each asset with all the different attribute types going along the top row so that we can filter on these when the report is placed in Power BI / excel

    ------------------------------
    Rob Morris
    Telford & Wrekin Council
    Telford
    ------------------------------

    Original Message