As we have seen in earlier blog posts, how easy it is to create a drill through reports in OAC - Essbase. If you have not read the earlier post yet then read it here.
In this post I am just focusing on multi-cell/multi region drill-through reports. Don't worry, there is no change required on your earlier reports. They will just keep working with single as well as multi selections.
Requirement:
You need latest SmartView for trying this out. If you need time to update SmartView on all your user machines then you can keep old SmartView and single cell based drill through will keep working as earlier.
Make sure that your instance is OAC 5.2 or later.
What is Multi-Cell :
Multi-Cell is when you select continuous cells in the Excel like B3, B4
What is Multi-Region:
Multi-Region is when you select multiple non continuous multi-cells. :)
In other words, when you select any number of regions(use ctrl+select to select multiple non-continuous ) you want in Excel.
e.g. It's very common for us to see the detailed report for all the months till September excluding quarters like below.
Now go ahead and select such cells where drill through reports are defined and run drill through.
What should you expect when you click on drill through toolbar button:
Listing:
When you select multiple cells or regions, and there are multiple drill through reports, intersections of those reports are shown.
e.g. If you select A1, A2 and A3 and here is the cell to reports mapping
A1: R1
A2: R1, R3, R2
A3: R1, R3, R4
then only R1 is common so it will be executed directly.
If you select only A2 and A3 then both R1 and R3 are listed. You can select one and then execute it.
Same will happen with multi region. Report(s) should be available for all the cells you selected.
Execution:
For Multi-Cell:
Think of it as running a SQL like select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
Where r1,r2 and r3 are the report columns and f1 and f2 are mapped columns for A and B dimension.
For single cell the query was like:
select r1,r2,r3 where f1=a1 and f2=b1
So only change is the replacement of equal operation with IN operation.
If you want sorted data etc then its better to define the data source query itself with sorting or use excel sorting once report is landed.
For Multi-Region:
Now think of it as running multiple SQL statements in parallel (one per region).
select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
+
select r1,r2,r3 where f1 in (i1,i2,i3) and f2 in (j1,j2,j3)
+
select r1,r2,r3 where f1 in (x1,x2,x3) and f2 in (y1,y2,y3)
As these queries are run in parallel and appended together in the result to improve the performance, data will remain unsorted. You can sort it in the Excel.
The combination of recursive, multi cell and multi region drill through is so fascinating!
Try it out!
In this post I am just focusing on multi-cell/multi region drill-through reports. Don't worry, there is no change required on your earlier reports. They will just keep working with single as well as multi selections.
Requirement:
You need latest SmartView for trying this out. If you need time to update SmartView on all your user machines then you can keep old SmartView and single cell based drill through will keep working as earlier.
Make sure that your instance is OAC 5.2 or later.
What is Multi-Cell :
Multi-Cell is when you select continuous cells in the Excel like B3, B4
Multi-Region is when you select multiple non continuous multi-cells. :)
In other words, when you select any number of regions(use ctrl+select to select multiple non-continuous ) you want in Excel.
e.g. It's very common for us to see the detailed report for all the months till September excluding quarters like below.
Now go ahead and select such cells where drill through reports are defined and run drill through.
What should you expect when you click on drill through toolbar button:
Listing:
When you select multiple cells or regions, and there are multiple drill through reports, intersections of those reports are shown.
e.g. If you select A1, A2 and A3 and here is the cell to reports mapping
A1: R1
A2: R1, R3, R2
A3: R1, R3, R4
then only R1 is common so it will be executed directly.
If you select only A2 and A3 then both R1 and R3 are listed. You can select one and then execute it.
Same will happen with multi region. Report(s) should be available for all the cells you selected.
Execution:
For Multi-Cell:
Think of it as running a SQL like select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
Where r1,r2 and r3 are the report columns and f1 and f2 are mapped columns for A and B dimension.
For single cell the query was like:
select r1,r2,r3 where f1=a1 and f2=b1
So only change is the replacement of equal operation with IN operation.
If you want sorted data etc then its better to define the data source query itself with sorting or use excel sorting once report is landed.
For Multi-Region:
Now think of it as running multiple SQL statements in parallel (one per region).
select r1,r2,r3 where f1 in (a1,a2,a3) and f2 in (b1,b2,b3)
+
select r1,r2,r3 where f1 in (i1,i2,i3) and f2 in (j1,j2,j3)
+
select r1,r2,r3 where f1 in (x1,x2,x3) and f2 in (y1,y2,y3)
As these queries are run in parallel and appended together in the result to improve the performance, data will remain unsorted. You can sort it in the Excel.
The combination of recursive, multi cell and multi region drill through is so fascinating!
Try it out!
