Can Report widget use fields from related tables? #99
Comments
|
@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. |
|
Ok thanks for confirmation @tmcgee. |
|
@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. |
|
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. |
|
Hi @tmcgee, |
|
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...
permitReportClick: function () {
var feature = this.map.infoWindow.getSelectedFeature();
this.executePermitReport(feature);
return false;
},
// 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));
}
},
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
});
}
feature.attributes.myField = features[0].attributes.myField;
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
}
}
}
]
}
};
}); |
|
@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. |
|
Hi all! |
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
The text was updated successfully, but these errors were encountered: