How to perform drill through the transactional data in the Cross-Tab
You can use the drill through operation on cross-tab to retrieve detailed row-level and transaction-level data from cubes. You can access the underlying transactional or row-level view of selected cross-tab columns, row, or cell.
Drill through on row dimension
The below Sales cross-tab shows the Product category wise Gross sales over different years. To get the transactional data (having Product Category, Product Name, Sales Date and Gross Sales columns) of the Product Category 'Fruit Juices', follow the below steps.
In the Sales cross-tab, right-click on the 'Fruit Juices' cell.
The system displays the Manage Drill through Columns dialog box if columns are not selected for drill through earlier. If drill through columns are selected earlier, the system will straight-away open the drill through view.
Note: The drill through columns can be selected from the Manage Drill through option available in the cross-tab Settings.
On the Manage Drill through Columns screen, drag and drop the columns - Product Category, Product Name, Date and Gross Sales - from the Available Columns section to the Selected Columns section.
You can set the order of the selected columns by moving up and down the columns from Selected Columns section.
Click OK. The system displays all the transactional data of 'Fruit Juices' as below.
TRANSACTIONAL DATA OF FRUIT JUICES
Since the records are of 49 pages, you can navigate through each page or convert it into CSV or XML format for further cross-tab.
By default, the data will be sorted on the first column (Date). This can be changed from the Sort columns drop-down box.
Drill through on column dimension
To get the transactions (having Year, Quarter, Month and Gross sales) for the year 2013, follow the below steps.
The below sales cross-tab shows State wise Gross sales over different years. (The Year - 2013 - cell is selected in the cross-tab).
Set the Manage Drill through columns as 'Year, Quarter, Month, and Gross sales' from the cross-tab settings and follow the Procedure of Drill through.
The detailed transactional data will be displayed as below.
Drill through on data dimension
Lets see how to display all the row-level records that contribute to the sales (13,663,087) of the state 'Florida' for the year 2014.
The below sales cross-tab shows State wise Gross sales over different years. (The Gross sales cell is selected in the cross-tab).
Set the Manage Drill through columns as 'Date, State, City and Gross sales' from the cross-tab settings and follow the Procedure of Drill through.
The detailed Drill through dialog will be displayed as below.