This post explains how to add sub-totals using Oracle and SQL Server OVER() analytics function.
It is similar to Analytic 1 (adding a rank()) but I will go further and use a test table to demonstrate it...

First, we create a table with data using the following statements (works in Oracle and SQL Server):


Then you can create a report and add the table in your Data Source (Data Source -> Tables, then contextual menu Add Tables from Catalog...).

Build the simple model of the report:
Rows: Category, Owner
Data: Amount

This simple report shows the amount per product category and owner.

Now we will add a new column which show the total amount per category:

1. Drag and drop the Amount element in Data again,
   
2. Rename it to Total per category
   
3. Expand the Custom SQL property and type the magic analytic command: sum(sum(amount)) over(partition by category)
   

if you check the SQL generated by pressing F8, it looks like:


And the report show this new column: A sub-total by category.

You can easily add a sub-total by Owner as well with the custom SQL: sum(sum(amount)) over(partition by owner)

Once again, use your database engine skills when you can...

Some links to understand better how OVER() works:
SQLServer http://technet.microsoft.com/en-us/library/ms189461.aspx
Oracle http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF51200

Plus a lot a samples if you Google it...

Hi there,

I rewrote this code to make it compatible with the last version of SealReport. And I added some error trapping.

I now need to work on it in the case of a pivot table on the data column, as many result columns could appear, and this would be the case as well in case of a drill-down, any ideas are welcome to make it easier to handle.

Another point I need to work on is a way to handle multi-level SubTotals.

Thomas

---

@using Seal.Model
@using System.Data
@{
ReportModel model = Model;
ReportExecutionLog log = model.Report;

//Final script executed to modify the model result tables after their generations
//Note that other assemblies can be used by saving the .dll in the Repository 'Assemblies' sub-folder...
log.LogMessage("Modifying result values with the 'Final Script'...");
ResultTable summaryTable = model.SummaryTable;
foreach (ResultPage page in model.Pages)
{
ResultTable dataTable = page.DataTable;
ResultTable pageTable = page.PageTable;

log.LogMessage("Data table:{0} lines, body start:{1}, body end:{2}", dataTable.Lines.Count, dataTable.BodyStartRow, dataTable.BodyEndRow);

ResultCell[] subTotalLine = null;
int i=dataTable.BodyStartRow;
int cols = dataTable.Lines[0].Length;

double sumAmount = 0;

string currentValue = "";
string breakValue = "";
int columnToWatch = 6;

// Column to watch
currentValue = dataTable.Lines[i][columnToWatch].DisplayValue;
breakValue = dataTable.Lines[i][columnToWatch].DisplayValue;

// Main loop on the data table
while (i <= dataTable.BodyEndRow)
{

// Catch the error values in the column to sum up
//try {
// log.LogMessage("Old key:'{0}', New key:'{1}', Current value:'{2}'", breakValue, currentValue, dataTable.Lines[i][cols-1].DoubleValue.Value);
//}
//catch {
// log.LogMessage("Old key:'{0}', New key:'{1}', value on row '{2}' is null", breakValue, currentValue , i);
//}

// If we have a break on the value to watch, or that the value became blank (last line)
if ( currentValue != breakValue || /* currentValue == "" */ i == dataTable.BodyEndRow) {

// Define the new line as a result cell with the right number of columns
var newSubTotalLine = new ResultCell[cols];

//Set all cells of the new line as 'IsTotal' ??
for (int j=0;j<cols;j++) {
newSubTotalLine[j] = new ResultCell() { IsSubTotal = true };
}

// Format the new line
// Add the text 'Subtotal'
newSubTotalLine[0].Value = "Subtotal";
newSubTotalLine[columnToWatch].Value = breakValue;
// Add the total values for the last x columns
newSubTotalLine[cols-1].Value = sumAmount;

// Set the CSS style as right aligned
newSubTotalLine[cols-1].FinalCssStyle = "text-align:center;";

// Add some logging as Debugger
//log.LogMessage("subTotalLine:'{0}'", sumAmount);

if (sumAmount != null) {
//log.LogMessage("Old value:'{0}', New value:'{1}', SubTotals:{2}", breakValue, currentValue, sumAmount);
dataTable.Lines.Insert(i, newSubTotalLine);
dataTable.BodyEndRow++;
i++;
}
// Assign the new value for our loop
breakValue = currentValue;

//Reset the Sum value to the current line
try {
sumAmount = dataTable.Lines[i][cols-1].DoubleValue.Value;
}
catch {
// do not sum up a null value
sumAmount = 0;
}

}
else {

// If we are not on a new line, add the current line value to the summed variable
try {
sumAmount += dataTable.Lines[i][cols-1].DoubleValue.Value;
}
catch {
// do not sum up a null value
}

}
i++;

// Column to watch
try {
currentValue = dataTable.Lines[i][columnToWatch].DisplayValue;
}
catch {
// Do nothing
}

}

log.LogMessage("Data table:{0} lines, body start:{1}, body end:{2}", dataTable.Lines.Count, dataTable.BodyStartRow, dataTable.BodyEndRow);

}

}