{"id":3766,"date":"2015-06-08T19:11:59","date_gmt":"2015-06-08T11:11:59","guid":{"rendered":"http:\/\/localhost\/help\/?page_id=3766"},"modified":"2024-06-28T14:43:05","modified_gmt":"2024-06-28T06:43:05","slug":"how-to-write-a-paged-query","status":"publish","type":"page","link":"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/creating-a-dataset-datasource\/how-to-write-a-dataset-query\/how-to-write-a-paged-query\/","title":{"rendered":"How to Write a Paged Query"},"content":{"rendered":"\n<p class=\"intro-text\">In order to use the Paging Control in IFS OI Explorer, your dataset must be written such that paging is enabled. When paging has been enabled for a dataset, IFS OI Explorer displays additional dataset parameters on the Data Selector to enable paging.<\/p>\n<p class=\"intro-text\"><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1.png\" rel=\"lightbox-0\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-14802 size-full\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1.png\" alt=\"Data Selector with Paging\" width=\"592\" height=\"493\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1.png 592w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1-150x125.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1-24x20.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1-36x30.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdataselector1-48x40.png 48w\" sizes=\"auto, (max-width: 592px) 100vw, 592px\" \/><\/a><\/p>\n<p class=\"left-bar\">Related:\u00a0<a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/controls\/paging-control\/\">Paging Control<\/a>, <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/data-selector\/\">Data Selector<\/a>, <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/creating-a-dataset-datasource\/how-to-write-a-dataset-query\/\">How to Write a Dataset Query<\/a><\/p>\n<p class=\"intro-text\">When IFS OI Explorer retrieves a dataset from IFS OI Server, the following information is provided to IFS OI Server:<\/p>\n<ul class=\"intro-text\">\n<li>EnablePaging<\/li>\n<li>PageNumber<\/li>\n<li>PageSize<\/li>\n<\/ul>\n<p class=\"intro-text\">This information determines if the dataset has been enabled for paging in IFS OI Explorer. Explorer will only allow a dataset to return paged data if the query in the dataset contains the following keywords:<\/p>\n<ul class=\"intro-text\">\n<li>FirstRow<\/li>\n<li>LastRow<\/li>\n<\/ul>\n<p class=\"intro-text\">IFS OI Server's role is to send the number of rows involved to IFS OI Explorer.\u00a0<\/p>\n<p class=\"intro-text\">Here is an example of a query that has been written with paging in mind:<\/p>\n<pre class=\"lang:tsql decode:true \">WITH pagedQuery AS ( SELECT ROW_NUMBER() \r\n  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 \u00a0\u00a0 \r\n  FROM PRV_PRODUCTION_METRICS \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n  where PRODUCTION_ENTITY_NAME in (PARAMS(names,entity)) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n  and PRODUCTION_DATE &gt;= PARAM(prodStartTime,datetime) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n  and PRODUCTION_DATE &lt; PARAM(prodEndTime,datetime) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n  and UPPER(READING_TYPE) = PARAM(readingType,string) ) \r\nSELECT (SELECT count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.* \r\n FROM pagedQuery \r\n WHERE p2RowNumber \r\n BETWEEN FIRSTROW() AND LASTROW()<\/pre>\n<p>&nbsp;<\/p>\n<p class=\"intro-text\">The paged query consists of an <strong>inner query<\/strong> and an <strong>outer query<\/strong>.<\/p>\n<h2 class=\"page-subheading\">Inner Query<\/h2>\n<p class=\"intro-text\">The inner query\u00a0(lines 3-10) is the part that determines the data displayed in the table. The inner query is essentially the query that you will construct to return the data you need, regardless of whether or not it is displayed in pages. You can find more information on writing the inner query in the topic, \"<a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/creating-a-dataset-datasource\/how-to-write-a-dataset-query\/\">How to Write a Dataset Query<\/a>\". There is one important element of the inner query that must be used in the outer query:<\/p>\n<ul class=\"intro-text\">\n<li><strong>p2RowNumber<\/strong>, which is an identifier for the number of that rows within the dataset (this can be named anything, it doesn't have to be called <em>p2RowNumber<\/em>, but it must be used consistently). \u00a0<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2 class=\"page-subheading\">Outer Query<\/h2>\n<p class=\"intro-text\">The outer query is a <a href=\"https:\/\/msdn.microsoft.com\/en-AU\/library\/ms175972.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">CTE (common table expression)<\/a> that is used to produce a temporary result set consisting of a restricted number of rows in the dataset, which IFS OI Explorer will display as \"pages\" of data in a tabular format. Thus the outer query determines how the inner query is displayed.\u00a0<\/p>\n<p class=\"note\">Note: Though the syntax is fairly standardised, the MSDN documentation may be inaccurate when writing queries against Oracle in some cases.<\/p>\n<p class=\"intro-text\">The outer query is what we will concentrate on in this topic. The important elements of the outer query are:<\/p>\n<ul class=\"intro-text\">\n<li><strong>pagedQuery<\/strong> - an identifier for the query (this can be named anything, it doesn't have to be called <em>pagedQuery<\/em>, but it must be used consistently everywhere to identify the CTE)<\/li>\n<li><strong>P2TOTALROWCOUNT<\/strong> - needed for IFS OI Explorer to know the total number of rows returned from the inner query.<\/li>\n<li><strong>p2RowNumber<\/strong> - the row number of an individual row returned from the inner query.<\/li>\n<li><strong>FIRSTROW<\/strong> - a keyword that returns the row number that IFS OI Explorer will use as the first row in the paged dataset.\u00a0<\/li>\n<li><strong>LASTROW<\/strong> - a keyword that returns the row number that IFS OI Explorer will use as the last row in the paged dataset.<\/li>\n<\/ul>\n<p class=\"intro-text\">Let's examine each line of the outer query and explain what it does.<\/p>\n<table>\n<tbody>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">WITH pagedQuery AS<\/td>\n<td>This identifies the CTE.<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">SELECT (SELECT count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.*<\/td>\n<td>The outer query referencing the CTE name.<\/p>\n<ul>\n<li>P2TOTALROWCOUNT returns the total number of rows<\/li>\n<li>pagedQuery.* returns all of the columns resulting from the <em>pagedQuery<\/em> CTE<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">FROM pagedQuery WHERE p2RowNumber BETWEEN FIRSTROW() AND LASTROW()<\/td>\n<td>Specifies the location of the individual row in relation to the first row and last row of the table.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<h2 class=\"page-subheading\">Example Paged Query with Grouping<\/h2>\n<p class=\"intro-text\">This query\u00a0displays downtime hours grouped per entity and downtime reason, and excludes the downtime reason 'No Issues'. \u00a0It requires 2 dataset parameters: StartDate, EndDate.<\/p>\n<pre class=\"lang:tsql decode:true \">WITH pagedQuery AS ( SELECT ROW_NUMBER() \r\n  OVER (SORTBY(ENTITY ASC, downtimeReason ASC)) p2RowNumber, grouped.* \r\n  FROM ( select entity, downtimereason, sum(downtimehours) TotalDowntimeHours \r\n       from sampledata Where StartDate \r\n       Between PARAM(StartDate, datetime) \r\n       and PARAM(EndDate, datetime) \r\n       and downtimereason &lt;&gt; 'No Issues' \r\n       group by entity, DowntimeReason ) \r\n  GROUPED ) \r\nSELECT(select count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.* \r\n  FROM pagedQuery \r\n  WHERE p2RowNumber \r\n  BETWEEN FirstRow() \r\n  AND LastRow();<\/pre>\n<p>&nbsp;<\/p>\n<h2 class=\"page-subheading\">End-To-End Example<\/h2>\n<p class=\"intro-text\">Let's look at an end-to-end example of this in action. This example uses dataset queries from the <em>Sample Data <\/em>that can be optionally made available\u00a0during initial installation and setup.<\/p>\n<h3>Part 1. Create Your Non-Paged Query in IFS OI Server Management<\/h3>\n<p class=\"intro-text\">In this query, downtime data is grouped by downtime reason:<\/p>\n<pre>Select Sum(DowntimeHours) as SumDowntimeHours, DowntimeReason\r\nFrom SampleData\r\nWhere StartDate Between PARAM(StartDate, datetime) and PARAM(EndDate, datetime)\r\nAnd Entity in (PARAMS(EntitiesList, entity))<\/pre>\n<p class=\"intro-text\">It's a standard query with 3 parameters: <em>StartDate<\/em>, <em>EndDate<\/em>, and <em>EntitiesList<\/em>.<\/p>\n<p class=\"intro-text\">\u00a0<\/p>\n<h3 class=\"page-subheading\">Part\u00a02. Change the Query to a Paged Query<\/h3>\n<p class=\"intro-text\">This query is the same query as above, but has paging enabled:<\/p>\n<pre class=\"lang:tsql mark:3-7 decode:true \">WITH pagedQuery AS\r\n(\r\nSelect ROW_NUMBER() OVER (SORTBY(DowntimeReason DESC)) p2RowNumber,\r\nSum(DowntimeHours) as SumDowntimeHours, DowntimeReason\r\nFrom SampleData\r\nWhere StartDate Between PARAM(StartDate, datetime) and PARAM(EndDate, datetime)\r\nAnd Entity in (PARAMS(EntitiesList, entity))\r\n)\r\nSELECT (SELECT count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.*\r\nFROM pagedQuery WHERE p2RowNumber BETWEEN FIRSTROW() AND LASTROW()<\/pre>\n<p class=\"intro-text\">The highlighted section is the inner query (i.e. the same query that was used in the non-paged query). The rest forms the outer query (i.e. what is required for a paged query). <strong>You should\u00a0copy this exactly as written to form the paged query<\/strong>.<\/p>\n<p class=\"intro-text\">Examining the <strong>outer query<\/strong> in closer detail:<\/p>\n<table>\n<tbody>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">WITH pagedQuery AS<\/td>\n<td>This identifies the CTE.<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">SELECT (SELECT count(1) from pagedquery) P2TOTALROWCOUNT, pagedQuery.*<\/td>\n<td>The outer query referencing the CTE name.<\/p>\n<ul>\n<li>P2TOTALROWCOUNT returns the total number of rows<\/li>\n<li>pagedQuery.* returns all of the columns resulting from the <em>pagedQuery<\/em> CTE<\/li>\n<\/ul>\n<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">FROM pagedQuery WHERE p2RowNumber BETWEEN FIRSTROW() AND LASTROW()<\/td>\n<td>Specifies the location of the individual row in relation to the first row and last row of the table.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p class=\"intro-text\">\u00a0<\/p>\n<p class=\"intro-text\">There is also a small addition to the <strong>inner query<\/strong>, indicated above in black. For datasets using the SQL Server adaptor, you should copy this as written but substitute the ordering clause as appropriate for your data.<\/p>\n<pre class=\"\">Select ROW_NUMBER() OVER (SORTBY(DowntimeReason DESC)) p2RowNumber,<\/pre>\n<table>\n<tbody>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">ROW_NUMBER<\/td>\n<td>ROW_NUMBER is an SQL function; in a result set it returns the number of the row in that result set (1, 2, 3, 4, etc). The OVER keyword forms part of this construct.<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">SORTBY()<\/td>\n<td>SORTBY() is a keyword in the SQL Server adaptor, and is not standard SQL. It contains the default ordering clause, in this case DowntimeReason descending. Because the ordering clause is contained as a keyword parameter, it also informs the adaptor of where the ordering clause is. The incoming request can therefore supply a different clause to override the default.<\/td>\n<\/tr>\n<tr style=\"border: 1px solid #cccccc;\">\n<td style=\"padding-left: 5px;\">P2RowNumber<\/td>\n<td>P2RowNumber is the name of the column that the row number is returned under. Doing this means the CTE declared by the opening WITH will have numbered rows. This allows the WHERE clause at the end of the statement to determine which rows are supposed to come back.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<h3>Part 3. Implement the Paged Query in IFS OI Explorer<\/h3>\n<p class=\"intro-text\">In this example, we'll create a page in IFS OI Explorer that shows both the non-paged query and the paged query in conjunction with a Paging Control component.<\/p>\n<p class=\"intro-text\"><strong>Step 1. Prepare a Studio Page<\/strong><\/p>\n<p class=\"intro-text\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14281\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage.png\" sizes=\"auto, (max-width: 438px) 100vw, 438px\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage-400x100.png 400w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage-250x62.png 250w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage-24x6.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage-36x9.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage-48x12.png 48w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/newpage.png 438w\" alt=\"New Page button on ribbon\" width=\"438\" height=\"109\" \/><\/p>\n<p class=\"intro-text\">Before you start, click the <strong>New Page<\/strong>\u00a0button on the Home tab of the ribbon. Choose the Grid layout.<\/p>\n<p class=\"intro-text\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14282\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose.png\" sizes=\"auto, (max-width: 863px) 100vw, 863px\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose-150x93.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose-400x248.png 400w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose-768x475.png 768w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose-800x495.png 800w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose-242x150.png 242w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/01\/gridlayoutchoose.png 863w\" alt=\"Grid Layout 4.4\" width=\"863\" height=\"534\" \/><\/p>\n<p class=\"intro-text\">Let's set up the page in this format:\u00a0<\/p>\n<ul class=\"intro-text\">\n<li>Create 3 columns.<\/li>\n<li>Create 2 rows.<\/li>\n<\/ul>\n<p class=\"left-bar\">Related: <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/controls\/grid-layout\/\" data-slimstat-clicked=\"false\" data-slimstat-type=\"2\" data-slimstat-tracking=\"false\" data-slimstat-callback=\"false\" data-slimstat-async=\"false\">Grid Layout tutorial<\/a><\/p>\n<p class=\"intro-text\">\u00a0<\/p>\n<p class=\"intro-text\"><strong>Step 2. Add 2 Data Tables to the Page<\/strong><\/p>\n<p><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2.png\" rel=\"lightbox-1\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14815\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2.png\" alt=\"paging-tut-2\" width=\"1645\" height=\"637\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2.png 1645w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-768x297.png 768w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-150x58.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-1280x496.png 1280w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-24x9.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-36x14.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/paging-tut-2-48x19.png 48w\" sizes=\"auto, (max-width: 1645px) 100vw, 1645px\" \/><\/a><\/p>\n<p class=\"left-bar\">Related: <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/controls\/data-table\/\">Data Table tutorial<\/a><\/p>\n<p class=\"intro-text\">1. Set the columns for both tables to <strong>Autofill<\/strong>.<\/p>\n<p class=\"intro-text\">2. Configure the first table with the <strong>non-paged<\/strong> dataset <em>OilDowntimeByReason<\/em>:<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged.png\" rel=\"lightbox-2\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14812\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged.png\" alt=\"dataunpaged\" width=\"331\" height=\"316\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged.png 331w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged-150x143.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged-24x24.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged-36x34.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/dataunpaged-48x46.png 48w\" sizes=\"auto, (max-width: 331px) 100vw, 331px\" \/><\/a><\/p>\n<p class=\"intro-text\">3. Configure the 2nd table with the <strong>paged<\/strong> dataset <em>OilDowntimeByReason_Paged<\/em>:<br \/>\nClick the <strong>EnablePaging<\/strong> check box to see the additional parameters, as paged datasets can still be viewed unpaged if required.<\/p>\n<p style=\"padding-left: 30px;\"><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged.png\" rel=\"lightbox-3\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14813\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged.png\" alt=\"datapaged\" width=\"329\" height=\"520\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged.png 329w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged-95x150.png 95w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged-15x24.png 15w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged-23x36.png 23w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/datapaged-30x48.png 30w\" sizes=\"auto, (max-width: 329px) 100vw, 329px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p class=\"intro-text\"><strong>Step 3. \u00a0Set the Defaults<\/strong>\u00a0<\/p>\n<p><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues.png\" rel=\"lightbox-4\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14816\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues.png\" alt=\"pagingdefaultvalues\" width=\"634\" height=\"979\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues.png 634w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues-97x150.png 97w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues-16x24.png 16w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues-23x36.png 23w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingdefaultvalues-31x48.png 31w\" sizes=\"auto, (max-width: 634px) 100vw, 634px\" \/><\/a><\/p>\n<p class=\"intro-text\">At the moment, the page shows the default values that have been set for the <em>OilDowntimeByReason\u00a0<\/em>dataset in IFS OI Server.\u00a0<\/p>\n<p class=\"intro-text\">Let\u2019s change the default data that is displayed. We do this by creating defaults for the variables\u00a0specified in the components in the previous steps. The variables we are interested in are\u00a0<strong>selectedEntities<\/strong>,<strong> pageNumber<\/strong>,<strong> pageSize<\/strong>, and<strong> totalRowCount<\/strong>.<\/p>\n<ul class=\"intro-text\">\n<li>Click the <em><strong>Default Values<\/strong><\/em> button on the Studio toolbar.<\/li>\n<li>Click the <strong>Add<\/strong> button.<\/li>\n<li>Add the above variables as per the screenshot<em>.<\/em><\/li>\n<\/ul>\n<p class=\"intro-text\">You should now see the Data Tables update to reflect the new defaults. \u00a0<\/p>\n<p class=\"left-bar\">Related: <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-primers\/variables\/\" data-slimstat-clicked=\"false\" data-slimstat-type=\"2\" data-slimstat-tracking=\"false\" data-slimstat-callback=\"false\" data-slimstat-async=\"false\">Variables<\/a>, <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/default-value\/\" data-slimstat-clicked=\"false\" data-slimstat-type=\"2\" data-slimstat-tracking=\"false\" data-slimstat-callback=\"false\" data-slimstat-async=\"false\">Default Values<\/a><\/p>\n<p>&nbsp;<\/p>\n<p class=\"intro-text\"><strong>Step 4. Add the Paging Control<\/strong><\/p>\n<p class=\"intro-text\"><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol.png\" rel=\"lightbox-5\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14817\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol.png\" alt=\"pagingexamplecontrol\" width=\"1632\" height=\"560\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol.png 1632w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-768x264.png 768w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-150x51.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-1280x439.png 1280w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-24x8.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-36x12.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexamplecontrol-48x16.png 48w\" sizes=\"auto, (max-width: 1632px) 100vw, 1632px\" \/><\/a><\/p>\n<p class=\"intro-text\">Add a Paging Control component below the paged data table and configure it with the following variables:<\/p>\n<ul class=\"intro-text\">\n<li><strong>Page Number<\/strong>: pageNumber<\/li>\n<li><strong>Page Size<\/strong>: pageSize<\/li>\n<li><strong>Total Record Count<\/strong>: totalRowCount\u00a0<\/li>\n<\/ul>\n<p class=\"left-bar\">Related: <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/controls\/paging-control\/\">Paging Control\u00a0tutorial<\/a><\/p>\n<p class=\"intro-text\">\u00a0<\/p>\n<p class=\"intro-text\"><strong>Step 5. Add an Entity Selector<\/strong><\/p>\n<p class=\"intro-text\"><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector.png\" rel=\"lightbox-6\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14818\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector.png\" alt=\"pagingentityselector\" width=\"1632\" height=\"424\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector.png 1632w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-768x200.png 768w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-150x39.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-1280x333.png 1280w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-24x6.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-36x9.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingentityselector-48x12.png 48w\" sizes=\"auto, (max-width: 1632px) 100vw, 1632px\" \/><\/a><\/p>\n<p class=\"intro-text\">Finally, let's add an Entity Selector component so you can play around with the finished page. No configuration is required, you can just use the defaults here.<\/p>\n<p class=\"left-bar\">Related: <a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-studio\/controls\/entity-selector\/\">Entity Selector\u00a0tutorial<\/a><\/p>\n<p class=\"intro-text\">\u00a0<\/p>\n<p class=\"intro-text\"><strong>Step 6. All Done!<\/strong><\/p>\n<p><a href=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample.png\" rel=\"lightbox-7\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14819\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample.png\" alt=\"pagingexample\" width=\"1112\" height=\"380\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample.png 1112w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample-768x262.png 768w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample-150x51.png 150w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample-24x8.png 24w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample-36x12.png 36w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/06\/pagingexample-48x16.png 48w\" sizes=\"auto, (max-width: 1112px) 100vw, 1112px\" \/><\/a><\/p>\n<p class=\"intro-text\">Congratulations! You now have a paged data table that uses the Paging Control. When you select entities\u00a0from the Entity Selector, downtime data for the selected entities is\u00a0displayed in both the paged and non-paged tables.<\/p>\n<p class=\"intro-text\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1348\" src=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/02\/save.png\" sizes=\"auto, (max-width: 48px) 100vw, 48px\" srcset=\"https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/02\/save.png 48w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/02\/save-22x24.png 22w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/02\/save-33x36.png 33w, https:\/\/oihelp.corporate.ifs.com\/help\/wp-content\/uploads\/2015\/02\/save-43x48.png 43w\" alt=\"save\" width=\"48\" height=\"53\" \/>\u00a0Don\u2019t forget to <a title=\"Saving Your Page\" href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-explorer\/explorer-create-your-first-page\/saving-your-page\/\" data-slimstat-clicked=\"false\" data-slimstat-type=\"2\" data-slimstat-tracking=\"false\" data-slimstat-callback=\"false\" data-slimstat-async=\"false\">save your page<\/a>!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In order to use the Paging Control component in IFS OI Explorer, your dataset must be written such that paging is enabled. This article explains how to write queries that can be used with the Paging Control in IFS OI Explorer.<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"https:\/\/oihelp.corporate.ifs.com\/help\/p2-server\/connecting-your-data\/creating-a-dataset-datasource\/how-to-write-a-dataset-query\/how-to-write-a-paged-query\/\">Read more<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":4680,"parent":3775,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":"","_members_access_role":[],"_members_access_error":""},"categories":[4],"tags":[445,129,306],"class_list":["post-3766","page","type-page","status-publish","has-post-thumbnail","hentry","category-explainer","tag-paging","tag-queries","tag-tabular-data","Product-srv"],"_links":{"self":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/3766","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/comments?post=3766"}],"version-history":[{"count":3,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/3766\/revisions"}],"predecessor-version":[{"id":67731,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/3766\/revisions\/67731"}],"up":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/pages\/3775"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/media\/4680"}],"wp:attachment":[{"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/media?parent=3766"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/categories?post=3766"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oihelp.corporate.ifs.com\/help\/wp-json\/wp\/v2\/tags?post=3766"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}