Can Report widget use fields from related tables?

[Northgate-GIT]

I have a layer that has several related tables which are also exposed via the map service, and I would like to combine data from the feature attributes and the related tables. Can the Report widget use data from related tables?
thanks, Pascual

[tmcgee]

@Northgate-GIT The report widget can show attributes from related tables and combine them with attributes from the feature.

I have done this but perhaps not in the same way you intend. In my use-case, the report includes parcel attributes and map at the top of the page with a table of permits on the bottom of the page (and the table can extend to multiple pages if it exceeds the first page_. I have been meaning to provide an example of this in my repo but have not had the time. I probably won't have time until after the Developer Summit.

[Northgate-GIT]

Ok thanks for confirmation @tmcgee.
Is there a special format for specifying the attribute fieldnames from the the related table when setting up in the reportsWidget.js config file?

[LevesqueB]

@Northgate-GIT Hi! Did you figure out how to add data from related table? I am serching a way to do that too. Thanks for any advices.

[Northgate-GIT]

Hi @LevesqueB, no I have not gotten to this point yet. Though @tmcgee indicated he may be posting an example of this soon, so hopefully that will illustrate the approach.

[LevesqueB]

Hi @tmcgee,
In the report widget, I try to display my attributes from a related table like the use case you wrote about. I trried with the tables:[] without any success.
Should I use attributes :[] with something like this (fieldname: "fieldName": "relationships/0/COMMON",
"label": "Common Name") to be able to link with the related table. Thanks a lot.

[tmcgee]

@Northgate-GIT @LevesqueB

I don't work with related records much so not sure how they should be referenced for inclusion in a simple report. You might be able to refer to them using the syntax provided by @LevesqueB above but that is untested.

What I include below isn't a direct answer to the question. It is intended to help push this discussion forward. It is a relatively complex example of executing a report that includes multiple related records in a table. The example is for permits associated with a parcel APN. These are stored in a separate layer not a related table. It should be relatively easy to convert this to execute a Related Records query on the related table using OBJECTID or whatever is used as a unique identifier.

This is mostly a quick stream-of-consciousnesses dump from a real world example. Hopefully it adds some clarity, not confusion...

1. get the identified feature. Similar approach to what is demonstrated in this repo

permitReportClick: function () {
    var feature = this.map.infoWindow.getSelectedFeature();
    this.executePermitReport(feature);
    return false;
},

2. execute the report

// a bit too hard-coded for my liking but it works...
executePermitReport: function (feature) {
    var apn = feature.attributes.APN;
    var q = new Query();
    q.outFields = ['RECORD_ID', 'RECORD_MODULE', 'RECORD_TYPE', 'RECORD_NAME', 'RECORD_STATUS', 'RECORD_OPEN_DATE', 'NAME_FULL'];
    q.orderByFields = ['RECORD_OPEN_DATE DESC'];
    q.where = 'APN = \'' + apn + '\'';

    var layer = this.map.getLayer('queryLayer');
    if (layer) {
        var queryTask = new QueryTask(layer.url + '/3');
        queryTask.execute(q).then(lang.hitch(this, 'processPermitQueryResults', feature));
    }
},

3. check for results of the query and send them to the report in a features array. NOTE that the original parcel feature is included as well.

processPermitQueryResults: function (feature, results) {
    var features = results.features;
    if (features.length < 1) {
        topic.publish('growler/growl', {
            title: 'Permit Report',
            message: 'No permits found for APN ' + feature.attributes.APN,
            level: 'warning',
            timeout: 3000
        });
        return;
    }

    topic.publish('permitReportWidget/createReport', {
        feature: feature,
        features: features,
        reportLayout: permitReport.reportLayout
    });
}

4. Alternatively, in step 3 you could add attributes from one or more of these features as additional attributes to the original feature. Something simple like this could do the trick:

feature.attributes.myField = features[0].attributes.myField;

5. Now lets use the feature + features in the report. To display a multi-record table populated from the features array, include one or more tables in the configuration. Here is the tables section to highlight a few areas of interest. The complete report configuration is included below.

tables: [
    {
        // informs the table to use the features as the data source
        features: true,

        columns: [
            {
                title: 'Date',
                dataKey: 'RECORD_OPEN_DATE'
            },
            {
                title: 'Permit #',
                dataKey: 'RECORD_ID'
            },
            {
                title: 'Category',
                dataKey: 'RECORD_MODULE'
            },
            {
                title: 'Type',
                dataKey: 'RECORD_TYPE'
            },
            {
                title: 'Description',
                dataKey: 'RECORD_NAME'
            },
            {
                title: 'Status',
                dataKey: 'RECORD_STATUS'
            },
            {
                title: 'Owner Name',
                dataKey: 'NAME_FULL'
            }
        ],

        data: [], // will be populated from query results

        options: {
            theme: 'grid',
            startY: 356,
            margin: {
                left: 35,
                top: 100
            },
            styles: {
                cellPadding: 4,
                overflow: 'linebreak',
                valign: 'top'
            },
            // format the column widths
            columnStyles: {
                RECORD_ID: {columnWidth: 62},
                RECORD_MODULE: {columnWidth: 55},
                RECORD_STATUS: {columnWidth: 42},
                RECORD_OPEN_DATE: {columnWidth: 42},
                NAME_FULL: {overflow: 'ellipsize'}
            },

            // custom function to format dates from the raw data in the field
            drawCell: function (row, data) {
                if (data.column.dataKey.indexOf('DATE') >= 0) {
                    row.text = [formatters.date(parseInt(row.raw, 10))];
                }
            },

            headerStyles: {
                rowHeight: 18,
                fontSize: 10,
                fillColor: colors.subheading,
                textColor: colors.white
            },
            bodyStyles: {
                rowHeight: 15,
                fontSize: 9
            }
        }
    }
]

This is the complete report configuration:

define([
    'commonConfig/formatters'
], function (formatters) {

    //portrait letter
    var pageWidth = 612;
    var pageHeight = 792;
    var pageCenter = 306;

    //landscape letter
    //var pageWidth = 792;
    //var pageHeight = 612;
    //var pageCenter = 346;

    var colors = {
        text: [33, 33, 33],
        line: [33, 33, 33],
        border: [33, 33, 33],
        subheading: [192, 34, 40],
        heading: [65, 112, 124],
        transparent: [255, 255, 255, 0],
        white: [255, 255, 255]
    };

    var fonts = {
        text: {
            color: colors.text,
            size: 11,
            font: 'helvetica',
            style: 'normal'
        },
        heading: {
            color: colors.heading,
            size: 14,
            style: 'bold'
        },
        rowHeading: {
            color: colors.subheading,
            size: 11,
            style: 'bold'
        },
        footer: {
            color: colors.text,
            size: 9,
            style: 'italic'
        }
    };

    var borders = {
        rowHeading: {}
    };

    var tableStyle = {
        fontSize: 10,
        cellPadding: 5,
        fillColor: colors.white,
        valign: 'top', // top, middle, bottom
        overflow: 'linebreak', // visible, hidden, ellipsize or linebreak
        columnWidth: 133
    };

    return {
        map: true,

        topicID: 'permitReportWidget',

        /*
            printTaskURL must be on the same server as application
            or the server must be configured for CORS
            or you can use a proxy
        */
        printTaskURL: 'https://server/arcgis/rest/services/Utilities/PrintingTools/GPServer/Export%20Web%20Map%20Task',

        defaultStyles: {
            font: fonts.text,
            line: {
                width: 0.5,
                color: colors.line
            },
            rectangle: {
                lineWidth: 0.5,
                lineColor: colors.border,
                fillColor: colors.transparent
            }
        },

        reportLayout: {
            layout: {
                orientation: 'portrait',
                unit: 'pt',
                format: 'letter'
            },
            output: {
                type: 'save',
                options: 'permit-report.pdf' // providing a file name for saving. valid for 'save' and 'blob' types.
            },
            margins: {
                top: 30,
                left: 30,
                bottom: 30,
                right: 30
            },
            metadata: {
                title: 'Permit Report',
                author: 'County of Null Island',
                keywords: 'parcel, county of null',
                creator: 'MoosePoint Technology'
            },
            images: [
                {
                    id: 'countySeal',
                    url: '../common/images/county_seal_100x100.png',
                    format: 'PNG'
                }
            ],
            header: {
                text: [
                    {
                        text: 'Null Island Permit Report',
                        left: 100,
                        top: 55,
                        align: 'left',
                        font: {
                            color: colors.heading,
                            size: 18,
                            style: 'bold'
                        }
                    }
                ],
                images: [
                    {
                        id: 'countySeal',
                        top: 15,
                        left: 35,
                        width: 50,
                        height: 50
                    }
                ],
                lines: [
                    {
                        left: 35,
                        top: 70,
                        bottom: 70,
                        right: pageWidth - 30,
                        width: 1.5,
                        color: colors.line
                    }
                ],
                attributes: [
                    {
                        top: 15,
                        left: pageWidth - 38,
                        layout: 'stacked',
                        fields: [
                            {
                                fieldName: 'APN',
                                valueAlign: 'right'
                            },
                            {
                                fieldName: 'SitusFormatted1',
                                valueAlign: 'right'
                            },
                            {
                                fieldName: 'SitusFormatted2',
                                valueAlign: 'right'
                            }
                        ]
                    }
                ]
            },
            footer: {
                text: [
                    {
                        format: 'date',
                        left: 38,
                        top: pageHeight - 30,
                        font: fonts.footer
                    },
                    {
                        format: 'pageNumber',
                        left: pageCenter,
                        top: pageHeight - 30,
                        align: 'center',
                        font: fonts.footer
                    },
                    {
                        text: 'Copyright 2017, Null Island',
                        left: pageWidth - 38,
                        top: pageHeight - 30,
                        align: 'right',
                        font: fonts.footer
                    }
                ],
                lines: [
                    {
                        left: 35,
                        top: pageHeight - 40,
                        bottom: pageHeight - 40,
                        right: pageWidth - 35,
                        width: 0.5,
                        color: colors.line
                    }
                ]
            },
            map: {
                top: 80,
                left: 35,
                height: 266,
                width: 266,
                dpi: 144,  //multiple of 72
                format: 'PNG32',
                preserveScale: false,
                border: true
            },
            text: [],
            lines: [],
            pages: [], // you can group everything related to each individual page, if desired
            groupedItems: [], // for grouped items such as text on top of a graphic or shape
            shapes: [], // types: circle, ellipse, rectangle, square, triangle
            attributes: [
                {
                    layout: 'column',
                    options: {
                        margin: {top: 80, left: pageCenter + 5, bottom: 35},
                        tableWidth: 266,
                        theme: 'grid',
                        style: tableStyle
                    },
                    fields: [
                        {
                            fieldName: 'CurrentOwnerName',
                            label: 'Current Owner Name',
                            rowHeading: {
                                text: 'Owner & Mailing Address',
                                font: fonts.rowHeading,
                                border: borders.rowHeading
                            }
                        },
                        {
                            fieldName: 'MailingDBAorCareOf',
                            label: 'Mailing DBA or Care Of'
                        },
                        {
                            fieldName: 'MailingStreetAddress',
                            label: 'Mailing Street Address'
                        },
                        {
                            fieldName: 'MailingCityState',
                            label: 'Mailing City State'
                        },
                        {
                            fieldName: 'MailingZipCode5',
                            label: 'Mailing Zip 5'
                        },
                        {
                            fieldName: 'MailingZipCode4',
                            label: 'Mailing Zip 4'
                        },
                        {
                            fieldName: 'MailingAddress1',
                            label: 'Mailing Address 1'
                        },
                        {
                            fieldName: 'MailingAddress2',
                            label: 'Mailing Address 2'
                        },
                        {
                            fieldName: 'MailingAddress3',
                            label: 'Mailing Address 3'
                        },
                        {
                            fieldName: 'MailingAddress4',
                            label: 'Mailing Address 4'
                        }
                    ]
                }
            ],
            tables: [
                {
                    features: true,
                    columns: [
                        {
                            title: 'Date',
                            dataKey: 'RECORD_OPEN_DATE'
                        },
                        {
                            title: 'Permit #',
                            dataKey: 'RECORD_ID'
                        },
                        {
                            title: 'Category',
                            dataKey: 'RECORD_MODULE'
                        },
                        {
                            title: 'Type',
                            dataKey: 'RECORD_TYPE'
                        },
                        {
                            title: 'Description',
                            dataKey: 'RECORD_NAME'
                        },
                        {
                            title: 'Status',
                            dataKey: 'RECORD_STATUS'
                        },
                        {
                            title: 'Owner Name',
                            dataKey: 'NAME_FULL'
                        }
                    ],
                    data: [], // will be populated from query results
                    options: {
                        theme: 'grid',
                        startY: 356,
                        margin: {
                            left: 35,
                            top: 100
                        },
                        styles: {
                            cellPadding: 4,
                            overflow: 'linebreak',
                            valign: 'top'
                        },
                        columnStyles: {
                            RECORD_ID: {columnWidth: 62},
                            RECORD_MODULE: {columnWidth: 55},
                            RECORD_STATUS: {columnWidth: 42},
                            RECORD_OPEN_DATE: {columnWidth: 42},
                            NAME_FULL: {overflow: 'ellipsize'}
                        },
                        drawCell: function (row, data) {
                            if (data.column.dataKey.indexOf('DATE') >= 0) {
                                row.text = [formatters.date(parseInt(row.raw, 10))];
                            }
                        },
                        headerStyles: {
                            rowHeight: 18,
                            fontSize: 10,
                            fillColor: colors.subheading,
                            textColor: colors.white
                        },
                        bodyStyles: {
                            rowHeight: 15,
                            fontSize: 9
                        }
                    }
                }
            ]
        }
    };
});

[LevesqueB]

@tmcgee Thanks for sharing! It's a lot. I will try to make it in my report. For the discussion, I use the identify function with related table developed by @roemhildtg, maybe similar approach could be a way for related table in the report widget. Thanks again.

[LevesqueB]

Hi all!
The reporting tool works very well. I can display data from a selected feature and from a non spatial table (querytask). I try to add a second table into the report but I cannot figure out how to add-it into the reportwidget. In the report reactor, I add the second query (inspired by : Manage results from multiple queries, js api). but into the report I can only have one table at the time, i cannot to use both into var features.
In the report reactor, I added a new var but I cannot find how to add it into the report widget. Is it possible to add a new variable ? Or I have to do something else?
Thanks for any advise!