Scrolling & Paging in Long Grids
by Michael Raam, Principal Data Analytics, Pyramid Analytics
and David Novick, Technical Writer, Pyramid Analytics
Challenged by a monster grid? Overwhelmed by a mile long scroll bar? Lost in the matrix?
We have good news !! This blog provides details on how to create a MDX-driven paging mechanism for easy navigation through long grids. This paging mechanism operates in an interactive fashion based on the current query content within the Data Discovery and Story Board modules. The mechanism does not operate in the Publication module.
Most computer users are familiar with standard scrolling techiques, and indeed we can say that BI Office provides the standard kind of scroll functionality that is available in most modern applications:
- Use vertical bar (or mouse wheel) to scroll up/down and view rows.
- Use horizontal bar to scroll right/left and view columns.
Example of vertical scroll bar used to scroll manually through multiple rows.
MDX-Driven Paging Mechanism
When working with large grids, manual scrolling is possible but can be cumbersome 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 slicer allows the user to choose the desired page number, while the second slicer allows the user to specify the number of rows per page.
Example of paging mechanism with two slicers: Page Number and Page Size.
The paging mechanism is not a standard BI Office function, but rather is created using simple MDX code, as described below. This MDX example shows how pagination 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 )
By adding the set to a report, 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.
In this example, the user has displayed page 23 of the grid.