logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
epf  
#1 Posted : Tuesday, May 13, 2014 2:26:51 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 605
Switzerland

Thanks: 9 times
Was thanked: 88 time(s) in 85 post(s)
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):
Code:
create table TestAnalytics(
Category varchar(10),
Product varchar(10),
Owner varchar(10),
Amount int
);

insert into TestAnalytics values('Category A', 'Product 1', 'popol', 12);
insert into TestAnalytics values('Category A', 'Product 2', 'popol', 5);
insert into TestAnalytics values('Category A', 'Product 3', 'albert', 9);
insert into TestAnalytics values('Category B', 'Product 4', 'albert', 23);
insert into TestAnalytics values('Category B', 'Product 5', 'albert', 21);
insert into TestAnalytics values('Category B', 'Product 6', 'john', 17);
insert into TestAnalytics values('Category B', 'Product 7', 'john', 14);


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:
Code:
SELECT
  TestAnalytics.Category AS C0,
  TestAnalytics.Owner AS C1,
  Sum(TestAnalytics.Amount) AS C2,
  sum(sum(amount)) over(partition by category) AS C3
FROM TestAnalytics
WHERE 1=0
GROUP BY TestAnalytics.Category,TestAnalytics.Owner
ORDER BY TestAnalytics.Category ASC,TestAnalytics.Owner ASC,Sum(TestAnalytics.Amount) ASC,sum(sum(amount)) over(partition by category) ASC


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...


epf  
#2 Posted : Friday, January 19, 2018 1:49:34 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 605
Switzerland

Thanks: 9 times
Was thanked: 88 time(s) in 85 post(s)
Another powerfull way to have sub-totals in the result table is to use the Final Script of the model producing the table.
Here is an example:

Code:
    //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, cols = dataTable.Lines[0].Length;
		string breakValue = "";
		while (i < dataTable.BodyEndRow) 
		{
			var currentValue = dataTable.Lines[i][0].DisplayValue;
			if (currentValue != breakValue) {
				var newSubTotalLine = new ResultCell[cols];
				for (int j=0;j<cols;j++) {
					newSubTotalLine[j] = new ResultCell() { IsTotal = true };
				}
				newSubTotalLine[0].Value = "Subtotal";
				newSubTotalLine[cols-1].Value = dataTable.Lines[i][cols-1].DoubleValue;
				newSubTotalLine[cols-1].FinalCssStyle = "text-align:right;";

				if (subTotalLine != null) {
					log.LogMessage("Old value:'{0}', New value:'{1}', SubTotals:{2}", breakValue, currentValue, subTotalLine[cols-1].DoubleValue.Value);
					dataTable.Lines.Insert(i, subTotalLine);
					dataTable.BodyEndRow++;
					i++;
				}
				breakValue = currentValue;
				subTotalLine = newSubTotalLine;
			}
			else {
				subTotalLine[cols-1].Value =  subTotalLine[cols-1].DoubleValue + dataTable.Lines[i][cols-1].DoubleValue;
			}
			i++;
		}
		dataTable.Lines.Insert(i, subTotalLine);
		dataTable.BodyEndRow++;
		log.LogMessage("Data table:{0} lines, body start:{1}, body end:{2}", dataTable.Lines.Count, dataTable.BodyStartRow, dataTable.BodyEndRow);

	}


This code creates a line with a sub-total when the value of the first column changes...

In a future release, this could be integrated in the Report Designer and Engine itself.

CPBOURG  
#3 Posted : Monday, February 18, 2019 4:34:24 PM(UTC)
CPBOURG

Rank: Member

Groups: Registered
Joined: 9/13/2018(UTC)
Posts: 28
Belgium
Location: Ottignies

Was thanked: 1 time(s) in 1 post(s)
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);

}

}
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.