Only showing unique records in sub-reports
Hello. I'm trying to create a mailing label report that is generated from a 1-to-many Data Link. The linked table contains the associated addresses (multiple per parcel). I've set it up in Tasks|Reports so that the user can graphically select a set of taxlots on which to run the report.
I've set up a report, and within it a sub report in order to access the data link table. The report runs fine - however there are duplicate labels generated. For example:
Parcel 1 -
305 Main St (situs address)
100 Warner St (owner address)
Parcel 2 -
310 Main St (situs address)
100 Warner St (owner address)
I want the final report to contain only 3 records, not 4. I have been successful in removing duplicates for a "regular" mailing label report that doesn't use sub-reports, but I can't find a way to remove duplicates across the whole report when a sub-report is used.
Anyone have any ideas?
Thanks!
Chris
-
Chris,
Can you share how you are removing duplicates in the "regular" report?
Mike
0 -
Hi Mike. I got the original code from the code gallery, and modified it for our use. Here's what I have in the Script section of the report:
using System;
using System.Collections.Generic;
using System.Data;public void ActiveReport_DataInitialize()
{//These are the user defined fields for the mailing labels
string fldName = "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table.Fullname";
string fldAddress = "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table.Owner_Full_Address";
string fldCity = "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table.Owner_City";
string fldState = "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table.Owner_State";
string fldZip = "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table.Owner_Zip";
int dataTableIndex = 0;
int numRows = (rpt.DataSource as DataSet).Tables[dataTableIndex].Rows.Count;
int numBlanks = 0;
//Create a new Dataset and Table to store the sorted records
DataSet sortDataset = new DataSet();
DataTable sortTable = sortDataset.Tables.Add();
sortTable.Columns.Add(fldName, typeof(string));
sortTable.Columns.Add(fldAddress, typeof(string));
sortTable.Columns.Add(fldCity, typeof(string));
sortTable.Columns.Add(fldState, typeof(string));
sortTable.Columns.Add(fldZip, typeof(string));
for (int ii = 0; ii < numRows;ii++)
{
//Store the field values in variables
string txtName=(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[ii][fldName].ToString();
string txtAddress=(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[ii][fldAddress].ToString().Trim();
string txtCity=(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[ii][fldCity].ToString().Trim();
string txtState=(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[ii][fldState].ToString().Trim();
string txtZip=(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[ii][fldZip].ToString().Trim();
//If the owner name and main address have valid values, add them to the sort dataset, otherwise add 1 to the null counter
if (txtName != "" && txtAddress != "")
{
sortTable.Rows.Add(txtName,txtAddress,txtCity,txtState,txtZip);
}
else
{
numBlanks=numBlanks + 1;
}
}
//Sort the sort table
sortTable.DefaultView.Sort = fldName + " ASC, " + fldAddress + " ASC";
//Create a new sort table, with unique records
DataTable sortTable2=sortTable.DefaultView.ToTable(true);
int cntSort=sortTable2.Rows.Count;
//In the original table, delete the number of null and duplicate rows
for (int k = 0;k<numRows-cntSort;k++)
{
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[0].Delete();
}
(rpt.DataSource as DataSet).Tables[dataTableIndex].AcceptChanges();//For each valid record, replace the original values with the sorted values
for (int i = 0; i < cntSort; i++)
{
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[i][fldName] = sortTable2.Rows[i][fldName];
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[i][fldAddress] = sortTable2.Rows[i][fldAddress];
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[i][fldCity] = sortTable2.Rows[i][fldCity];
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[i][fldState] = sortTable2.Rows[i][fldState];
(rpt.DataSource as DataSet).Tables[dataTableIndex].Rows[i][fldZip] = sortTable2.Rows[i][fldZip];
}
}0 -
Thanks Chris,
Where was the original code found - I didn't notice any under the Reports section that used Scripting...
I tried to convert to my data, but got an unhelpful error. Can you tell me what "GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table." stands for? Are you linking to another table here? If my fields are all from the layer, can I just have something like: string fldName = "OWN_NAME"; in the original user defined fields, where OWN_NAME is one of the fields in my layer?
0 -
Chris,
One more question. If I'm using calculated fields, should I refer to them, or to the original fields when I'm setting up the user defined fields?
0 -
Mike, I'm not sure where that example went from the Code Gallery, but it was one of the first ones posted when the gallery went live.
"GOLDStandard.dbo.ocMap_Taxlots_Topic_Query_as_Table." is a joined table at the .mxd level. We just upgraded to Server 10.1, which now requires fully qualified field names for joined tables (this has been a real pain!). If you don't have a join, you should be able to just use your short field name.
When you set up the user defined fields in the script, use the actual field names, not calculated fields.
0 -
Hope that helps!
0 -
Chris,
Still not working. In the report, do I refer to my original fields, or do I use either the fld.... or txt.... fields that were defined in the Script?
0 -
Mike, in the report, use the original fields (or calcualted fields).
I've just emailed you my .rpx file, so you can see how everything is set up.
Chris
0 -
While I haven't tried, I think there may be some logic problems trying to accomplish what you are doing. This is unconfirmed, but believe the sub report would be called for EACH single main record. Thus your code in the subreport would be unable to see into records related to OTHER main records, only its own.
If this assumption is true (can anyone confirm?), then the way I would handle this is to create a workflow to generate the data for the report. It would loop through every main record, then find all of the contacts associated and add each (if they aren't already there) to a recordset. Then a report would generate against that. Because you can "fake" the recordset for the report, you can generate the items as a Main report and avoid the complexity of the subreport. Further, the duplicate check logic would have already occurred so the report can be "dumb" and just output what it gets.
Just an idea though...
0 -
Yes Shawn, you are right about the problem of removing duplicates across sub-reports. I don't understand report recordsets well enough yet to do what you propose, but will try to figure it out when I get some time.
Chris
0 -
Chris, handle this problem through data perhaps. Relate your records and make it so the related items are unique, while duplicating the spatial elements (a well crafted SELECT statement should do this). Then generate a report through a workflow against this new table (don't use it as a layer) and you should be able to accomplish what you want without programming software, just well crafted SQL.
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
11 Kommentare