MDX-Driven Paging Mechanism

By Michael Raam , Principal, Data Analytics


This short blog provides how to create a MDX-driven paging mechanism for easy navigation through long tables. The paging mechanism functions interactively based on current query content within Data Discovery views and storyboards, but not within Publications.

Simple Scrolling

Most modern-day computer users are familiar with standard scrolling abilities. BI Office provides standard scrolling functionality. When too many rows are present, a vertical scroll bar appears. When too many columns are present, a horizontal scroll panel appears.

The user can scroll vertically to view more rows or scroll horizontally to view more columns. A number of scrolling techniques are available:

  • Spinning the mouse wheel
  • Clicking on the scrollbar arrows
  • Dragging the scroll bar in the desired direction

 Metadata hover showing number of rows in the grid.

 

MDX-Driven Paging Mechanism

When working with large tables, simple scrolling is possible but can be difficult or time consuming. As an alternative, BI Office allows you to design a paging mechanism for more efficient navigating. In the example shown below, the first variable slicer allows the user to choose the desired page number, while the second variable slicer allows the user to specify the number of rows per page.

The paging mechanism is not a standard BI Office function, but rather is created using some simple MDX code, as described in the next section.

 Metadata hover showing number of rows in the grid using two variables.

 

MDX Example

The following MDX example shows how paging can be introduced into the BI Office system when desired. We have created a set that is driven by two variables titled Page Number and Page Size.

 

TAIL(

       HEAD( [Date].[Month Dates].[Date].MEMBERS  ,

//base hierarchy to be paged over

[Variable].[!##Page no##!]*[Variable].[!##Page size##!]
// the input Variables to control the Page # and Page size

        ) ,

       [Variable].[!##Page size##!]

// the input Variable to control the Page size

       ) 

 

MDX used to create two variables.

 

By adding the set, the user may navigate to a specific page in the current query.

  • The first slicer can be used to enter the desired page number.
  • The second slicer can be used to specify the number of rows (or columns) per page.
  • The user can right-click the second slicer to change the range of the values.

 Set created to navigate to a page in the current query.

 

Like Follow Reply 3
Reply
1reply
    • Tim Andrews
    • Business Intelligence Manager
    • 1 mth ago
    • flag

    I think this is a very good solution.  Since multidimensional MDX scales poorly with long and wide cellsets returned to the client, this should keep the resultset pretty snappy.

    Like Reply
reply to topic
Like3 Follow
  • 3 Liked by
  • 1 mth agoLast active
  • 1Replies
  • 136Views
  • 2 Following