In this article, we will try to create a simple drill through report using a data source. Data source can be built from various sources so once you create a drill through report and want to switch from say spreadsheet to database, there will be no impact on the report definition and it will be hot deployed so I would recommend to start on the simple CSV and once all set, switch to data warehouse or keep CSV as is.
Let's start with the data...
In Sample Basic cube, say we have weekly sales but cube will be created with month level onward.
For simplicity, I have divided the month into 4 weeks. week1 to week4. Our goal is to drill through to these weekly sales.
Let's create a connection and data source to point to your data.
Once we create data source, go to applications tab and expand to Sample Basic cube and go to database inspector. (Actions -> Inspect...)
Scripts -> Drill Through Reports - New Data Source based.
Provide a name "weekly_sales"
Select data source
In column mapping section - select all report columns which will come in the report.
In Filter, select SKU generation for Product as we want to recursively go down till SKU level and fetch the data.
Same for Market and Month.
For Scenario, we want to pull only the selected member to be filtered so no generations selected. In the drill-able regions add - "Sales" (w/o quotes) as we want to view this report only on sales measures.
You can provide combinations of cells as well. e.g. "Sales 100" will restrict to Sales and 100 intersection.
You can add such multiple combinations by adding rows. e.g. If you want to show this to Sales and 200 as well, add a row with "Sales 200"
Save, close inspector and launch Smart View.
If you do not see colors for the drill-through enabled ranges, change option below in SmartView Tab - > Options
Once you select any cell(or multiple cells as well in coming patches) , drill through will work.
Try it out!
Let's start with the data...
In Sample Basic cube, say we have weekly sales but cube will be created with month level onward.
For simplicity, I have divided the month into 4 weeks. week1 to week4. Our goal is to drill through to these weekly sales.
Let's create a connection and data source to point to your data.
Once we create data source, go to applications tab and expand to Sample Basic cube and go to database inspector. (Actions -> Inspect...)
Scripts -> Drill Through Reports - New Data Source based.
Provide a name "weekly_sales"
Select data source
In column mapping section - select all report columns which will come in the report.
In Filter, select SKU generation for Product as we want to recursively go down till SKU level and fetch the data.
Same for Market and Month.
For Scenario, we want to pull only the selected member to be filtered so no generations selected. In the drill-able regions add - "Sales" (w/o quotes) as we want to view this report only on sales measures.
You can provide combinations of cells as well. e.g. "Sales 100" will restrict to Sales and 100 intersection.
You can add such multiple combinations by adding rows. e.g. If you want to show this to Sales and 200 as well, add a row with "Sales 200"
Save, close inspector and launch Smart View.
If you do not see colors for the drill-through enabled ranges, change option below in SmartView Tab - > Options
Once you select any cell(or multiple cells as well in coming patches) , drill through will work.
Try it out!
No comments:
Post a Comment