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: 557
Switzerland

Thanks: 7 times
Was thanked: 79 time(s) in 77 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: 557
Switzerland

Thanks: 7 times
Was thanked: 79 time(s) in 77 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.

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.