logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


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 : Wednesday, June 18, 2014 8:52:07 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
This post explains how to add running-totals using Oracle and SQL Server OVER() analytics function.
It is similar to Analytic 2 (adding a sub-totals) and I use the same 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, Product, Owner
Data: Amount

This simple report lists the table.

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 Running Total per category
  3. Expand the Custom SQL property and type the magic analytic command: sum(sum(amount)) over(partition by category order by TestAnalytics.Product ASC,TestAnalytics.Owner ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

if you check the SQL generated by pressing F8, it looks like:
Code:
SELECT
  TestAnalytics.Category AS C0,
  TestAnalytics.Product AS C1,
  TestAnalytics.Owner AS C2,
  Sum(TestAnalytics.Amount) AS C3,
  sum(sum(amount)) over(partition by category order by TestAnalytics.Product ASC,TestAnalytics.Owner ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS C4
FROM TestAnalytics
WHERE 1=0
GROUP BY TestAnalytics.Category,TestAnalytics.Product,TestAnalytics.Owner
ORDER BY TestAnalytics.Category ASC,TestAnalytics.Product ASC,TestAnalytics.Owner ASC,Sum(TestAnalytics.Amount) ASC,sum(sum(amount)) over(partition by category order by TestAnalytics.Product ASC,TestAnalytics.Owner ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ASC


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


As shown in analytic 2, you can easily add a sub-totals in a new column using the same way.

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

Enjoy !

Edited by user Wednesday, June 18, 2014 9:20:04 AM(UTC)  | Reason: Not specified

epf  
#2 Posted : Sunday, August 16, 2015 1:19:43 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Update with release 1.8,
Running totals, progressions or other calculations can be done after querying the database using the 'Cell Script' property of the report element.

Check out the sample 50-Cell Script - Progression and running totals.srex
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.