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

Creating OLAP Snippets


Business Functions are utilized to create a single snippet of MDX code that can be parameterized and reused throughout the project.


Multidimensional Expressions (MDX) is a query syntax used specifically for querying and extracting data from OLAP cubes. 

Users can create their own Business Functions in addition to using the Business Functions provided by Jet Reports that are contained in the Business Function Library. Instructions on downloading and importing the Business Function Library can be viewed in a separate Knowledge Base article here .

Creating a Business Function

The example that will be covered in this article will be creating a new Business Function that will allow a user to see an amount for a prior period. The period will be dynamic, so if the current period that is selected is July 2012 this Business Function would return the value for June 2012, and if the period selected is Q1 2012 this Business Function would show the amount for Q4 2011. This Business Function could be useful as a side-by-side comparison of the current period and the prior period.

  1. To create a new Business Function, click on the Tools ribbon in the Jet Data Manager and click Create Business Function.

    The Create Business Function window will appear.

  2. The user will name the Business Function (in this example the name is Prior Period), type in a description of what the Business Function does so that other users will know what it is used for (the description is optional), and then type in the MDX formula for the Business Function.

    The MDX used in this example:

    Prior Period Business Function

    ([Date Hierarchy].CurrentMember.PrevMember, [Measure])

    The Create Business Function window will now look like the following:


  3. The user will then add parameters to be used in the Business Function by the users. In this example parameters will be added for [Date Hierarchy] and [Measure]. These parameters are dynamic and can be easily changed in each measure that will be created that will be based on this Business Function.

    Highlight [Date Hierarchy] and click the Add Parameter button on the right. The [Date Hierarchy] is added as a parameter in the Parameters section below the formula. In this example the [Date Hierarchy] will represent a date hierarchy that the user will specify so the type should be changed to Hierarchy. A description can also be added to aid the users as to what they should select when using the Business Function.

  4. Next, highlight [Measure] and click the Add Parameter button. This will be added to the Parameters section. In this example, [Measure] represents that measure that the user will select to be used with the Business Function. This could be a measure such as Sales Amount, Quantity, Ending Balance, etc. The Type will be set to Measure since this represents the measure to be used.

    If folders are currently being used for Business Functions the user can select the Library Path which will determine the folder that the Business Function will be placed in. In this example the Prior Period Business Function will be added to the Time folder since it is related to time. The finished Business Function should look like the following:

    Click OK to save the Business Function in the project repository.

Using the Business Function

  1. To use the Business Function, expand the desired cube on the Cubes tab, right-click Measures, and mouse over Add Business Function. This will expand the Business Function folder list.

  2. In this example we will navigate to the Time folder and click Prior Period. The measure that will be created in this example will be a measure that will return the Sales Amount for the prior period. The user will give the measure a name in the Name field.

    In this example we will name is Sales Amount Prior Period.  A format string and Associated Measure Group can optionally be added to control how the measure looks and where it exists in the user interface. 

    Under Parameters, the user will select the relevant value from the Value drop-down box. For the [Date Hierarchy] parameter the Date YQMD ([Posting Date]) hierarchy is selected.  For the [Measure] parameter the Sales Amount measure is selected, since that is what this Business Function measure is meant to return.

  3. Once the cube is deployed and executed the Sales Amount Prior Period measure will appear for the users to utilize.

  4. In the example below it can be seen that the Sales Amount measure returns the sales for the current period and the Sales Amount Prior Period returns the sales amount for the previous period. In this example the Sales Amount Prior Period for May 2010 is the same value as the Sales Amount for April 2010.

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