distinct mailing labels
I saw a few other posts in the forums on this topic that never got responses, but I'll try anyway.
I have a request for running a mailing labels report to have duplicate labels be excluded. For example, when a user runs a report to retrieve mailing labels for the parcel taxpayer, he or she doesn't want multiple labels generated if the taxpayer name and addess is the same for multiple properties.
I would hope that something like this could be achieved in the Scripting section of the Report Designer, otherwise is there something I can add to my Report activity's where clause or something in the Workflow Designer to filter selected parcels for only distinct addresses?
Thanks in advance,
John Nerge
-
Hi John,
here is a report that does the sort and removes any duplicates.
regards,
Edmond
<?xml version="1.0" encoding="UTF-8"?>
<ActiveReportsLayout Version="3.1" PrintWidth="12240" DocumentName="ARNet Document" ScriptLang="C#" MasterReport="0">
<StyleSheet>
<Style Name="Normal" Value="font-family: Arial; font-style: normal; text-decoration: none; font-weight: normal; font-size: 10pt; color: Black; ddo-char-set: 204; " />
<Style Name="Heading1" Value="font-size: 16pt; font-weight: bold; " />
<Style Name="Heading2" Value="font-family: Times New Roman; font-size: 14pt; font-weight: bold; font-style: italic; " />
<Style Name="Heading3" Value="font-size: 13pt; font-weight: bold; " />
</StyleSheet>
<Sections>
<Section Type="PageHeader" Name="PageHeader1" Height="0" BackColor="16777215" />
<Section Type="Detail" Name="MailingLabelDetail" Height="1440" ColumnCount="3" ColumnDirection="1" ColumnSpacing="180" BackColor="16777215" CanGrow="0">
<Control Type="AR.Field" Name="OwnerName" DataField="Name" Left="0" Top="0" Width="3888" Height="259.2" Text="OWNERFULLN" Style="font-weight: bold; vertical-align: middle; " />
<Control Type="AR.Field" Name="Address" DataField="MailingAddressCalc" Left="0" Top="270" Width="3888" Height="259.2" Text="Address" Multiline="0" CanGrow="0" Style="white-space: nowrap; " />
<Control Type="AR.Field" Name="City" DataField="CITY" Left="0" Top="540" Width="3888" Height="259.2" Text="City" HyperLink="Field1" />
<Control Type="AR.Field" Name="State" DataField="STATE" Left="0" Top="777.6" Width="3888" Height="259.2" Text="State" />
<Control Type="AR.Field" Name="ZipCode" DataField="ZIPCODE" Left="0" Top="1036.8" Width="3888" Height="259.2" Text="ZipCode" />
</Section>
<Section Type="PageFooter" Name="PageFooter1" Height="0" BackColor="16777215" />
</Sections>
<ReportComponentTray />
<CalculatedFields>
<Field Name="MailingAddressCalc" Formula="(HOUSENO.ToString().Trim() != "" ? HOUSENO + " " : "") + (STDIR.ToString().Trim() != "" ? STDIR + " " : "") + (STNAME.ToString().Trim() != "" ? STNAME + " " : "") + (STTYPE.ToString().Trim() != "" ? STTYPE : "")" FieldType="String" />
<Field Name="Name" Formula="OWNERFIRST + " " + OWNERLASTN" FieldType="String" />
<Field Name="Field1" Formula=""http://www.google.com/" + OWNERFULLN" FieldType="None" />
</CalculatedFields>
<Script><![CDATA[using System.Data;
using System.Collections.Generic;public void ActiveReport_DataInitialize()
{
try {
string sortField = "OWNERLASTN";
int dtIndex = 0; //Index of the DataTable in the DataSet.
int skipCount = 0;
int numRows = (rpt.DataSource as DataSet).Tables[dtIndex].Rows.Count;
int numColumns = (rpt.DataSource as DataSet).Tables[dtIndex].Columns.Count;
List<string> ownerNames = new List<string>();
bool[] removedRows = new bool[numRows];
//Sort the default view of a copy of the DataSet
DataSet sortedData = (rpt.DataSource as DataSet).Copy();
sortedData.Tables[dtIndex].DefaultView.Sort = sortField + " ASC";
//Check the data for duplicates and track which ones these are so they can be removed.
for(int i=0; i<numRows; i++)
{
string owner = sortedData.Tables[dtIndex].DefaultView.ToTable().Rows[i][sortField].ToString();
if (!ownerNames.Contains(owner))
{
ownerNames.Add(owner);
}
else
{
removedRows[i] = true;
skipCount++;
}
}
//Remove rows from the DataTable so that it is the correct size when duplicates are removed.
for (int i = 0;i<skipCount;i++)
{
(rpt.DataSource as DataSet).Tables[dtIndex].Rows[0].Delete();
}
(rpt.DataSource as DataSet).Tables[dtIndex].AcceptChanges();
//Loop through the dataset, replacing the old data with sorted data
//The OBJECTID column is left untouched as we don't really care what it contains at this point.
//Rows marked as duplicates will be skipped, and the results will fit into our resized DataTable.
skipCount=0;
for (int i = 0; i < numRows; i++)
{
if (removedRows[i])
{
skipCount++;
continue;
}
for(int j=0; j < numColumns; j++)
{
string columnName = (rpt.DataSource as DataSet).Tables[dtIndex].Columns[j].ColumnName;
if (columnName != "OBJECTID")
{
(rpt.DataSource as DataSet).Tables[dtIndex].Rows[i-skipCount][j] = sortedData.Tables[dtIndex].DefaultView.ToTable().Rows[i][j];
}
}
}
}
catch (Exception ex) {}
}]]></Script>
<PageSettings LeftMargin="261" RightMargin="261" TopMargin="720" BottomMargin="720" GutterMargin="14" Orientation="1" />
<Parameters />
</ActiveReportsLayout>0 -
Thanks Edmond, this is very helpful in seeing how a script is used with a report (plus it does something I can use).
Carmen
0 -
Thanks, Edmond, this works like a charm!
-John
0 -
I'm a little late to the party seeing this was thread is from 2013, but any chance someone can suggest how this report script can be applied to a report that uses a dataLink?
From my reading I should just have to change the dtIndex variable, but that has yet to yield any results. What am I missing?
Thanks,
Warren
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
4 Kommentare