First off, forgive me if this is a naive question, but I've been at this for over a week and have made no progress, I am hoping that someone out there has been through this and can point me in the right direction.
Does anybody have a recommendation on approach/best practices for aggregate tables that are built using Oracle's grouping functions to create masked columns? Our aggregates are structurally identical to those in example 20-6 of I've pasted the relevant section of that article here for reference (please forgive the alignment) but recommend going to the link for the sake of readability:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Example 20-6 GROUPING to Mask Columns
This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-3. The mask columns are easy to analyze programmatically.
SELECT channel_desc, calendar_month_desc, country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) AS Ch,
GROUPING(calendar_month_desc) AS Mo, GROUPING(country_iso_code) AS Co
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, countries.country_iso_code);
channel_desc calendar co sales$ ch mo co
-------------------- -------- -- -------------- ---------- ---------- ----------
Internet 2000-09 GB 16,569 0 0 0
Internet 2000-09 US 124,224 0 0 0
Internet 2000-09 140,793 0 0 1
Internet 2000-10 GB 14,539 0 0 0
Internet 2000-10 US 137,054 0 0 0
Internet 2000-10 151,593 0 0 1
Internet 292,387 0 1 1
Direct Sales 2000-09 GB 85,223 0 0 0
Direct Sales 2000-09 US 638,201 0 0 0
Direct Sales 2000-09 723,424 0 0 1
Direct Sales 2000-10 GB 91,925 0 0 0
Direct Sales 2000-10 US 682,297 0 0 0
Direct Sales 2000-10 774,222 0 0 1
Direct Sales 1,497,646 0 1 1
1,790,032 1 1 1
A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
So with sales$ as the only metric, here is the ideal reporting/drill scenario we're trying to implement. Report starts @ highest level, total sales, so the SQL looks like:
SELECT SUM(sales$)
FROM agg_table
WHERE ch = 1
AND mo = 1
AND co = 1
From here, user drills down to include channel_desc, so SQL should look like this:
SELECT SUM(sales$), channel_desc
FROM agg_table
WHERE ch = 0
AND mo = 1
AND co = 1
GROUP BY channel_desc
I can easily create a new conditional metric that uses ch=1 in lieu of ch=0, but that forces the end user to first be aware of the complexity and know that he/she needs to drag a different metric into the report. But there must be a better way, so that I have a single metric that recalculates at the appropriate level of aggregation by changing the condition once we drill down to lower levels of granularity. What is the best way to do this?
Any help or guidance would be greatly appreciated. I have scoured the MSTR documentation (project design, basic reporting advanced reporting) and knowledge base, but have not found an example of how to do this.
Thanks very much,
Anilh
Does anybody have a recommendation on approach/best practices for aggregate tables that are built using Oracle's grouping functions to create masked columns? Our aggregates are structurally identical to those in example 20-6 of I've pasted the relevant section of that article here for reference (please forgive the alignment) but recommend going to the link for the sake of readability:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Example 20-6 GROUPING to Mask Columns
This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-3. The mask columns are easy to analyze programmatically.
SELECT channel_desc, calendar_month_desc, country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) AS Ch,
GROUPING(calendar_month_desc) AS Mo, GROUPING(country_iso_code) AS Co
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, countries.country_iso_code);
channel_desc calendar co sales$ ch mo co
-------------------- -------- -- -------------- ---------- ---------- ----------
Internet 2000-09 GB 16,569 0 0 0
Internet 2000-09 US 124,224 0 0 0
Internet 2000-09 140,793 0 0 1
Internet 2000-10 GB 14,539 0 0 0
Internet 2000-10 US 137,054 0 0 0
Internet 2000-10 151,593 0 0 1
Internet 292,387 0 1 1
Direct Sales 2000-09 GB 85,223 0 0 0
Direct Sales 2000-09 US 638,201 0 0 0
Direct Sales 2000-09 723,424 0 0 1
Direct Sales 2000-10 GB 91,925 0 0 0
Direct Sales 2000-10 US 682,297 0 0 0
Direct Sales 2000-10 774,222 0 0 1
Direct Sales 1,497,646 0 1 1
1,790,032 1 1 1
A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
So with sales$ as the only metric, here is the ideal reporting/drill scenario we're trying to implement. Report starts @ highest level, total sales, so the SQL looks like:
SELECT SUM(sales$)
FROM agg_table
WHERE ch = 1
AND mo = 1
AND co = 1
From here, user drills down to include channel_desc, so SQL should look like this:
SELECT SUM(sales$), channel_desc
FROM agg_table
WHERE ch = 0
AND mo = 1
AND co = 1
GROUP BY channel_desc
I can easily create a new conditional metric that uses ch=1 in lieu of ch=0, but that forces the end user to first be aware of the complexity and know that he/she needs to drag a different metric into the report. But there must be a better way, so that I have a single metric that recalculates at the appropriate level of aggregation by changing the condition once we drill down to lower levels of granularity. What is the best way to do this?
Any help or guidance would be greatly appreciated. I have scoured the MSTR documentation (project design, basic reporting advanced reporting) and knowledge base, but have not found an example of how to do this.
Thanks very much,
Anilh