Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

How to Manually Add Custom Drillthrough Capabilities to a Cube


Overview

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.

 

Process

  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="http://www.w3.org/2001/XMLSchema-instance">


    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
Have more questions? Submit a request

Comments