Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Add Custom OLAP Drillthrough Capabilities to a Cube


Drill-through is often used to limit the level of details that can be accessed directly in a report. This is done to ensure the returned result set is not big and could cause the OLAP server coming to a standstill. An example could be InvoiceID. We can create our cube so the end user cannot drag and drop the InvoiceID. Instead the user can execute a drill-through action returning the information in a tabular form.

This article will describe how to enable drillthrough (drilldown) in the Jet Data Manager.


  1. In SQL Server BIDS open the OLAP database

    Type in server and database name and hit OK.
  2. In the Solution Explorer open the cube where the drill-through will be applied
  3. Click "New Drillthrough Action" on the Actions tab
  4. Set up the drill-through action
    Property Description
    Name This is the name the front-end users will see.
    Measure group members Select <All> if the action should apply for all measure groups.
    Condition (Optional) Add a condition to limit the scope of the action. The action will not be available in the front-end if the condition is not met. This is optional.
    Drillthrough Columns Select the columns to be displayed when the drill-through action is executed. Only dimensions and measure in the cube are available. If more details are needed from the underlying fact table this has to be added as a dimension to the cube. The dimensions could be made invisible to prevent the user to use it for drag and drop purposes. For further information on how to hide a cube dimension please see: Hiding a cube dimension
    MaximumRows If the returned result set needs to be limited the MaximunRows property in the Properties window can set set. This is property should be set if large result set could be returned by mistake.

    MaximumRows property:
  5. Save the changes
    Once the drill-through action has been setup the project needs to be saved. Hit CTRL-S to save the changes
  6. In SQL Server Management Studio connect to the OLAP database
  7. Script the cube
  8. Locate and copy the Actions tag
  9. Create an OLAP Script Action in the JDM project
  10. Paste the script into the script window
    Add <InsertEnd Node="Cube"> </InsertEnd> tags and paste the script between the two tags
    Modify the <Actions> tag in the beginning of the script to: <Actions xmlns:xsi="">

    To avoid breaking the code in case of changes in the names of objects it is best practice to set up parameters for all referenced cube objects in the script. For details about parameters please see: Stored Procedures, User Defined Functions, and Script Actions
  11. Add the script to the cube
  12. Deploy and execute the cube or the whole project.

  13. Open Excel (or other front-end) and connect to the cube

Was this article helpful?
0 out of 0 found this helpful