You will notice that for every query that has a sub-total (with database cache and presentation services cache disabled) BI EE will create temporary files in this tmp directory. To determine this lets look at the /tmp folder. So, the question is how BI EE does this total.
![rowwise grandtotal in oracle11g rowwise grandtotal in oracle11g](https://i.stack.imgur.com/ziIXH.jpg)
If you look at the physical query for the above logical SQL, you will notice that the report level total is not pushed back to the database. Now that we know what BI Server does for calculating the sub-totals in a table view, lets look at how the BI Server pushes the REPORT_SUM function. I will list some of the common ones hereĪfter calculating the column based total, BI Server will get the first record value in the Report Sum column and display it as Sub-Total. There are quite a few functions like REPORT_SUM. One good thing is we can take the exact SQL and fire it in the logical SQL windowĪs you see BI Server calculates the total as a column based total. REPORT_SUM is an aggregation function that BI Server uses to calculate the total (since we had chosen Report Based Total whenever applicable in the table view). If you notice, there will be a new function called REPORT_SUM which will be passed to the logical SQL. So, to get the exact SQL we have to go to the Manage->Sessions & extract the logical SQL SELECTĪs you see this logical SQL does not actually represent the true logical SQL since the aggregation or the total part of the query is not anywhere in this SQL. To understand this, lets start with looking at the logical SQL in the advanced tab of this report. Does it bring the entire data into BI Server memory and then does the sub-total or does it push that back in SQL or does it write to any temporary file. The question is how does BI Server create this Total. To demonstrate how Sub-Totals work, lets start with a very simple report shown belowĪs you see, this table view report has a Grand Total. Since this is not documented anywhere, this behavior can be different across different releases (though i think it will work the same in all 10.1.3.* releases) Lets look at what this “in-memory calculation” means and how BI Server treats totals & Pivot table calculations. In such cases, BI Server does these calculations in its own memory. But there are certain cases wherein we cannot push an entire report in the form of SQL (for example Sub-Totals & Pivot table aggregations). As with any reporting tool, we expect almost every part of a report to be pushed back to the database in the form of native SQL. The query is: SELECT * FROM TABLE(Īnd the data is below, notice I've lost the Grand Total's row.One intriguing or not-so obvious feature of BI EE is the way it does its Sub-Totals & Pivot Table calculations. GROUP BY status, ROLLUP (status), CUBE(order_source)īut when I run this in my Pivot function same way, it does not pull the GRAND TOTAL2 as another column like its doing for other rows. ,NVL(order_source, 'GRAND TOTAL2') AS order_source I want another Grand Total which will go in the Order_Source column summing as I have described.Ĭhanged the base query further to below: SELECT NVL(status, 'GRAND TOTAL') AS row_labels Is there a way to sum up the "Complete", "Order Process error", "Credit check declined" and so on so forth? My Grand Total is working fine which goes in the Row Labels column. When I run the below query: SELECT NVL(status, 'GRAND TOTAL') AS row_labels Working towards to create the query which will also select that missing column. SELECT NVL(status, 'GRAND TOTAL') AS row_labels My View is created as follows, simplified, which is giving me the current progress image: CREATE OR REPLACE VIEW TEST1 AS Struggling to get the grand total in the bottom row. I have managed to get the GRAND TOTAL of the rows as: GROUP BY order_source, ROLLUP (status) Any suggestions would be great, thank you. If I do a ROLLUP('Row Labels') in my original query and then run the pivot function, then I am getting a last null row. But basically now I would like to have another row and column be added which will give me the total. He created a view of his query, I did the same.
![rowwise grandtotal in oracle11g rowwise grandtotal in oracle11g](https://lh4.ggpht.com/_yMCw0y0mvKw/Sm1P_qTo5II/AAAAAAAABYM/i_kG7buaLBs/s400/Grand%20Total%20Conditional%20Formatting.jpg)
If I take the last example from the above article: select * from table( pivot( 'select * from city_connections' ) ) The additional step that I need is to get the grand totals of the rows and columns.
#Rowwise grandtotal in oracle11g code
The article is here, and the source code is at the bottom: I found a really nice function which does this. I have a requirement to create a query which had dynamic column data and this had to be pivoted as rows.