Vertical Paging

Timeline Views offer two types of vertical paging. The first type can be seen in the example above. The only thing the developer needs to do is specify the number of items per page by setting VerticalPageSize attribute. Most use cases can easily be addressed by using VerticalPageSize.

However, this approach is not suitable for large datasets since paging occurs after the time line data is fetched. Additionally, this applies paging only at the root level of a column hierarchy, which can still fetch hundreds or thousands of rows if it is a multi-level hierarchy!

The second type of paging is done with the help of a SQL query and a TLVPage macro. This more performant approach requires the developer to follow these three steps:

1. Define a paging SqlDef.

   <SqlDef Name="PagingSql" GroupName="TlvSqls"><![CDATA[

   SELECT DISTINCT sys_item_id, sys_site_id FROM item_by_site

   ORDER BY sys_item_id, sys_site_id

   ]]></SqlDef>

   The paging SQL query must select all columns up to the page break level. For example, the query above will apply paging on the site level instead of item. If the query is updated to fetch only item from the table, then paging will be applied at the item level. Also, the returning values need to be distinct and should be sorted in the same order. (Columns need not be exactly the same as the ColumnHierarchy columns.)

2. Reference your paging SqlDef using VerticalPagingSqlDef.

   <ColumnHierarchy>

   <VerticalPageSize>5</VerticalPageSize>

   <VerticalPagingSqlDef>PagingSql</VerticalPagingSqlDef>

3. Insert the TLVPage macro into all your Datameasure SqlDefs.

   SELECT item, sys_item_id item_id, site, sys_site_id site_id, the_date, quantity FROM item_by_site

   WHERE ${TLVPage:item_id,site_id}

   TLVPage macro with the help of paging query will apply paging to the returning results. As mentioned earlier, columns returned by the paging query do not have to match columns defined in the macro. There is no limit on the number of columns as long as the number of columns in the macro is equal to the number of columns provided in the paging query.
   Even though the columns do not have to match, they do need to have a logical relationship. For example, ${TLVPage:site_id,item_id} in our example will work but won't return any results since the order of columns is not consistent with the ColumnHierarchy definition.

Let's look at the complete example and how it appears in the UI.