Last updated on December 2nd, 2015 at 03:31 pm
P2 Explorer and P2 Server versions 4.3 support server side paging of datasets. This blog post will work through an example of hooking up a paged query in Studio.
For this example, we are going to use the query “ProductionPivotPaged_Perth”. This is formed with the special paged syntax, as so:
WITH pagedQuery AS
(
SELECT ROW_NUMBER() OVER (SORTBY(PRODUCTION_ENTITY_NAME DESC)) p2RowNumber, PRODUCTION_ENTITY_NAME, PRODUCTION_DATE, PROD_GAS_ALLOC_GROSS_VOL, PROD_OIL_ALLOC_GROSS_VOL, PROD_WATER_ALLOC_GROSS_VOL
FROM PRV_PRODUCTION_METRICS
where PRODUCTION_ENTITY_NAME in (PARAMS(names,entity))
and PRODUCTION_DATE >= PARAM(prodStartTime,datetime)
and PRODUCTION_DATE < PARAM(prodEndTime,datetime)
and UPPER(READING_TYPE) = PARAM(readingType,string)
)
select (select count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.*
FROM pagedQuery WHERE p2RowNumber BETWEEN FIRSTROW() AND LASTROW()
To start with, create a datasource in P2 Server Management Studio that contains the above query.
Next up, go ahead and create a new page in Explorer Studio. Make a grid layout with 2 rows (add some padding), and add the above query as a dataset on the page.
The presence of the Enable Server Paging check box in the component editor indicates that Studio has detected you are using a query which supports server paging.
You can go ahead and use the query without paging, but the purpose of this example is to use paging with the explorer paging toolbar, so select the check box. You will now see that additional parameters become available. Fill out all the parameters in the component editor as follows:
- Total Row Count: totalNumberRows
- PageNumber: pageNumber
- PageSize: pageSize
- names: BOSSIER 1-1, BOSSIER 2-1, BOSSIER 3-1, BOSSIER 4-1, BOSSIER 5-1, BOSSIER 6-1
- prodStartTime: 01/01/2012 12:00:00 AM
- prodEndTime: 01/12/2012 12:00:00 AM
- readingType: DAILY
The Total Row Count parameter is an additional parameter which belongs to the dataset and which is required by the Paging Control. This parameter specifies the event name which is to be published with the total number of rows from the query execution. This allows the Paging Control to calculate the number of pages available. This parameter is not a query parameter on Server.
PageNumber controls the page number we are requesting, and PageSize controls how many items we want to request on each page.
Now, let's set defaults for the pageNumber and pageSize. This is required for pageSize as the Paging Control does not output the page size (although it could if you used a Combo Box to drive the page size).
Set Event Defaults as follows:
- pageNumber: 1
- pageSize: 10
Now let's drag and drop a Paging Control onto the page and configure it as follows:
- Page Number: pageNumber
- Page Size: pageSize
- Total Record Count: totalNumberRows
Now let's see this thing at work.
Drag and drop a Dataset Table onto the page and configure it as follows:
Now save your page and enjoy!
Because you are all so observant I know you are now asking "but what about that sort by column in the dataset?" At this stage this cannot be hooked up to the table column clicks, but you can optionally specify a column name in that field on which to sort by!