Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 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:
- Drag and drop the Amount element in Data again,
- Rename it to Running Total per category
- 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.aspxOracle http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF51200Plus a lot a samples if you Google it... Enjoy ! Edited by user Wednesday, June 18, 2014 9:20:04 AM(UTC)
| Reason: Not specified
|
