Grouping within a report

Chris Dunlop

January 11, 2012 08:04

I'm trying to create a report that shows the acreage by zone of parcels.  Something like this:

Zone: Commercial

     Parcel 1000     5.07

     Parcel 1001     2.12

Commercial Total:  7.19 acres

 

Zone: Residential

     Parcel 0201     2.03

     Parcel 0202     1.50

     Parcel 0205     0.34

Residential Total:  3.87 acres

 

To do this, I've added a group, and in the header put a label referencing the Zone field. In the detail section I have labels referencing Parcel Number and Acres. In the group footer I've added a group subtotal for Acres.

When I run the report, it groups and subtotals at every change in Zone.  Problem is, the recordset isn't sorted on Zone, so it may group 3 commercial and subtotal them, then 5 residential, but then 6 additional commercial etc.  Is there any way around this without resorting to Active Reports scripting (copy recordset to a temporary dataset, sort it, then replace the original recordset etc).  I guess I could extend this question to general sorting in reports.

Any thoughts are appreciated.

Chris

 

0

• 

  Permanently deleted user

  January 17, 2012 03:46

  Hi Chris,

  I don't know of a way to do this outside of ActiveReports scripting.  However, a single line should take care of it for you:

    rpt.DataSource = ((DataSet)rpt.DataSource).Tables[0].Select(null, "ZONE");

  On the script tab in Report Designer, choose DataInitialize from the "Event" dropdown menu, then paste the line above in between the curly braces.  Change "ZONE" to match the name of the Zone field in your data.  Note you can also specify ascending or descending, or you can include multiple columns (if you are doing multi-level grouping) by using something like this:

    rpt.DataSource = ((DataSet)rpt.DataSource).Tables[0].Select(null, "ZONE, OWNERNAME ASC");

  The null value passed in as the first parameter to the Select method is the query, which you likely don't need to supply.  Also for the curious, the main report always takes the first table in the DataSet, which is why the index 0 is coded in to the script line.

  Let me know if this works for you.

  John

  0
• 

  Chris Dunlop

  January 18, 2012 07:56

  Thanks John, that's just what I was looking for!  I'll be able to make some very useful summary reports based on this.

   

  Chris

  0

Please sign in to leave a comment.