Ok - it’s been way too long since my last post, and I now feel forced to cover a topic that several people has asked me:

 

“native reports are great and all, but can I add a total to my report”.

 

The good news is the answer is yes, the bad news is it requires some work.

 

Let’s assume I want to create a report that can show customers grouped by state and zip code… After using the template to generate the class for me I start out by filling in CreateData:

 

return engine.SmallBusinessInstance.CustomerAccounts.DataView;

 

then I fill in the CreateDesign method:

 

// Add a title to the report

iReportDesign.ReportHeader.ReportTitle.Text = "Customers Grouped By Region";

 

//  Add the data columns to the report

//  Note:  the columns must correspond to columns in the DataView returned

//  by the CreateData method

 

// Add the Name data column to the report

groupByNameColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "Name",                     //Column name in the dataview

    "Customer Name",            //Caption for the column

    ReportColumnType.RowLabel,  //Column type

    ReportColumnDataType.Text); //Data type

 

// Add the Address data column to the report

iReportDesign.ReportColumns.CreateReportColumn(

    "Address",                   //Column name in the dataview

    "Address",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Text); //Data type

 

// Add the City data column to the report

groupByCityColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "City",                   //Column name in the dataview

    "City",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Text); //Data type

 

// Add the State data column to the report.  Save a reference to

//  the column so that it can be added to the GroupByColumns collection

//  later.

groupByStateColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "State",                   //Column name in the dataview

    "State",                   //Caption for the column

    ReportColumnType.Data,     //Column type

    ReportColumnDataType.Text);//Data type

 

// Add the ZipCode data column to the report.  Save a reference to

//  the column so that it can be added to the GroupByColumns collection

//  later.

groupByZipColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "ZipCode",                 //Column name in the dataview

    "ZipCode",                 //Caption for the column

    ReportColumnType.Data,     //Column type

    ReportColumnDataType.Text);//Data type

 

// Add the Balance data column to the report

groupByBalanceColumn = iReportDesign.ReportColumns.CreateReportColumn(

    "Balance",                  //Column name in the dataview

    "Balance",                  //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Currency); //Data type

 

// Add the Active data column to the report

iReportDesign.ReportColumns.CreateReportColumn(

    "Active",                   //Column name in the dataview

    "Active",                   //Caption for the column

    ReportColumnType.Data,      //Column type

    ReportColumnDataType.Boolean); //Data type

 

This is all that is needed for a regular list report but we want the report grouped so we implement the IReportHasGroupsV2 interface and fill in the CreateGroupByColumns method

 

/// <summary>

/// This method creates the GroupByColumns whose values are used to create

/// row groups in the report.  Rows that have identical values in the first

/// column of the groupByColumns collection are grouped first.  Within each

/// group, rows with identical values in the second column become subgroups,

/// and so on.

/// </summary>

public void CreateGroupByColumns(IReportGroupByColumnsV2 groupByColumns)

{

    // Create groups by state

    groupByColumns.CreateGroupByColumn(groupByStateColumn);

    // Create subgroups by zip code

    groupByColumns.CreateGroupByColumn(groupByZipColumn);

}

 

To sort the groups in a particular order I need to fill the CreateGroupSortByColumns method, in this case I will sort the groups by state.

 

public void CreateGroupSortByColumns(IReportGroupSortByColumnsV2 groupSortByColumns)

{

    groupSortByColumns.CreateGroupSortByColumn(groupByStateColumn, true);

}

 

The report now has groups but to actually see the groups we need to put values into the group itself, that is done in the GroupAdded method. In this method I am using a couple of properties that may need a little explanation:

-       reportGroup.GroupByColumnValues This is a collection on the group that contain the group by values this group is based on.

-       reportGroup.Header.GroupValues This is a collection with all the values for a group header, by using the indexer with the column name you can specify a value for the group header for a specific column, below I’m setting values in the “name” column

 

/// <summary>

/// This method is called whenever a row group or subgroup is created. 

/// It allows header and footer elements to be added to each group.

/// </summary>       

public void GroupAdded(IReportGroupV2 reportGroup)

{

    String stateValue = reportGroup.GroupByColumnValues[groupByStateColumn.Name] as String;

    String zipValue = reportGroup.GroupByColumnValues[groupByZipColumn.Name] as String;

 

    if (stateValue != null)

    {

        if (string.IsNullOrEmpty(stateValue))

        {

            stateValue = "None";

        }

 

        // Create the report group header

 

        // The value "Name" determines the horizontal location of each

        // header value within the report. It does not indicate that the

        // data in the "Name" column is available.

        if (zipValue != null)

        {

            if (string.IsNullOrEmpty(zipValue))

            {

                zipValue = "None";

            }

 

            reportGroup.Header.GroupValues["Name"] = String.Format("Zip code: {0}", zipValue);

        }

        else

        {

            reportGroup.Header.GroupValues["Name"] = String.Format("State: {0}"), stateValue);

        }

    }   

}

 

Yeah yeah yeah, we have seen it all before what about the total? I’m sure this is your question at this point…

 

OK Let’s get to the total then

 

The GroupAdded method is also the place you can add a total to your report! Just like you can set values for the reportGroup.Header you can also set values for the reportGroup.Footer so adding something like this to the method will actually give you a total…

 

reportGroup.Footer.GroupValues["Name"] = String.Format("Customers: {0}", CountGroupMembers(reportGroup));

reportGroup.Footer.GroupValues["Balance"] = GetGroupBalance(reportGroup);

 

Yes I know I’m cheating as I’m using a couple of methods I haven’t defined yet, The name of the methods should be pretty much self explanatory, however how to get to the values may not be. I’m sure you can find a much faster way of doing this by using the view created in CreateData but this demonstrates a few of the methods available and it works.

 

private int CountGroupMembers(IReportGroupV2 reportGroup)

{

    int count = reportGroup.GroupDataViewRows().Count;

 

    foreach (IReportGroupV2 group in reportGroup.SubGroups) {

        count += CountGroupMembers(group);

    }

 

    return count;

}

 

private Decimal GetGroupBalance(IReportGroupV2 reportGroup)

{

    Decimal balance = 0;

 

    foreach (DataRowView row in reportGroup.GroupDataViewRows())

    {

        balance += (Decimal) row.Row["Balance"];

    }

 

    foreach (IReportGroupV2 group in reportGroup.SubGroups)

    {

        balance += GetGroupBalance(group);

    }

 

    return balance;

}

 

Let’s look at the result of our effort (Click the image to see full view):

 

 

Nice eh?