Open Northgate-GIT opened 7 years ago
@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. Is there a special format for specifying the attribute fieldnames from the the related table when setting up in the reportsWidget.js config file?
@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, 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.
@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...
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));
}
},
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
});
}
feature.attributes.myField = features[0].attributes.myField;
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
}
}
}
]
}
};
});
@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!
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!
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