Index Home

Supervisor administration requester

Full version 7 function	Application/Contract	Sage X3 People/ERP	Class	QUERY('code')	Representation	QUERY~code~n

The Sage X3 People supervisor includes three different tools to build requests:

The first one is managed through a grid of parameters. The corresponding Sage X3 People function name is GESALH.
The second one has a graphical interface. The corresponding Sage X3 People function name is GESALT.
The third one enables entering requests directly in SQL language. The corresponding Sage X3 People function name is GESALQ.

The requests created by the first tool (GESALH) can now be executed in the new client. This document explains this new function.

Introduction	Creating a request	How to access the request page	Entering a request page
Displaying a request	Additional parameters	Batch computation	Office links
Current limits	Use with graphs	Technical appendix

1.Introduction

In version 6, when a request is created by the grid requester (GESALH), the validation builds a window and a screen, and then generates a program that computes the request.
When the user triggers an execution, the program runs, computes the requests, stores the result in the database, and then executes an inquiry through the generated window on the computed data.

This method has the following disadvantages:

Writing a large amount of data resulting from the request execution in the database can be time and space consuming.
Usually, the computed result has a limited life time. It does not need to be managed with strong transaction constraints and does not need to be stored in a relational database.

For the reasons mentioned above, the version 7 implementation no longer stores the result in a database, but in a dedicated folder on the application server as JSON format text files. These files are indexed in a way that a direct access to a given line and a sequential access to the next lines are very efficient. This will provide in the future, the ability to also store these files in the mongodb database.

The version 7 requester is able to compute and display the result of a request at the right level of data aggregation as follows:

'1' is the most aggregated level. At this level, one line per value of the first field having Group equal to Yes is displayed, and the accumulated values provide the total for all the lines. Only the fields having a level equal to '1' will appear in the page.
'2' is the level where one line is displayed for every different set of values for the two first fields having Group equal to Yes. Only the fields having a level equal to '1' or '2' will appear in the page.
If N fields have Group equal to Yes, the level N+1 provides the detail of the request, and all the fields will appear on the page.

Links called +Lev and -Lev allows you to navigate from one level to the next or a previous one.

Two modes can be used for displaying the request:

The cube mode, where a zoom on the next level of a line will only display the details of the aggregated lines in the current line. This is the default mode.
The mode where cube has been set to false. In this case, a zoom on the next level will display all the lines of the current level starting from the first aggregated line.

In both modes, a graphical representation of the current page is available.

To display the latest level, the value of '99' can also be used. This value is used in the links generated by the V6 portal recodification tool.

2.Creating a request

Creating a request is still done through the 'GESALH' function by using an access to the Sage X3 People version 6 function, which opens a Classic screen. Nothing has changed in the way the requests are created; but when the request is validated, the supervisor creates the following:

A screen, a window, and a program as shown previously. This ensures that the version 6 tool can still be executed with an intermediate database storage.
A new Sage X3 People script file that can be called through a class and a representation with the new client. This script performs a much faster execution of the request and will be explained in the following sections.

3. How to access the request page

To do this for example, 'MYREQUEST', it is necessary to create a menu item having the following parameters:

The link type is representation.
The associated class is QUERY('MYREQUEST').
The representation is QUERY~MYREQUEST~1. This refers to the representation used when the level of details is '1' (that is the most aggregated level).
The facet is query.

This link gives access to the request including a graphical representation of the lines. If the level is not given in the representation, only the grid mode is displayed. In the previous example, the representation would be QUERY~MYREQUEST.

Additional parameters can be sent using the URL with the &parameter=value syntax. They will be explained at a later time.

Once this is done, clicking on the link associated with the menu item gives access to the request display and execution function.

4.Entering a request page

When entering the request page, the supervisor determines first if the request must be recomputed. This will happen if at least one of the following conditions is fulfilled:

The request has never been executed.
The request has the "recalculated" flag set to 'yes'.
The last execution was done with another set of range parameters.
The parameter value &forcedExecution=true is present in the URL.

If the request must be recomputed, the supervisor evaluates the cost of the computation. If this cost is small, the request is computed and the result is displayed on the fly. If this is not the case, the following message is displayed:

This request is too heavy to run directly and has been submitted in batch (xxxx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

Once the batch execution has been done, a later call of the page will display the results.

The present xxxx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx string is a UUID that has been assigned to this execution request and that can be used as an additional parameter to display the result of the computation.

The evaluation of the computation cost is as follows:

The system takes into account the table with the lowest detail in the request. If for example a request displays information coming from a header table and lines, the line table is considered.
If more than 'N' lines are present in this table, the system considers that the computation will take too much time to be done on the fly. The value of the 'N' threshold is determined by the global parameter 'SELWARN', already used to disable user memos on unindexed selections. If no value is found, 10,000 is considered the limit.

5.Displaying a request

If the request has been executed on the fly or a result was found, the page will directly display the result of the request in the following format:

A header line displays a message called Informations.
A second header line displays the Level.
A graphical representation of the request.
A grid with the columns associated with the requested data displays the computed lines.
A paging system with links on 'page numbers' and 'Previous Page / Next Page' links. This can be done as the number of computed lines is known. The number of lines per page can directly be modified by a drill down list.

It is recommended to avoid requesting more than 1,000 lines at once. Displaying the next page is usually immediate on pages of less than 50 lines, even if thousands of pages are available on a request.

Another point you have to be aware of is that the "Next" and "Previous" links are implemented with URLs that includes:

where key_values greater than the last value of the page
where key_values smaller than the first value of the page

This means that the Next/previous pages can miss lines if the sort indexes have no unique values, or if all the components that make the line unique are not displayed in the page. To have the right pagination, you should add at the end of your sorting criteria an additional criterion that makes the line unique.

The first line of the information header has the following format:

Information:Query QUERY_NAME computed the YYYY-MM-DD HH:MM:SS in TIME (T1+T2) seconds on NB_LINES lines

Where:

QUERY_NAME is the name of the query displayed.
TIME gives the total period of time spent by the computation of the request. 'T1' is the time spent by the database to compute the request, and 'T2' is the time spent by the supervisor layer to fetch the data and format it.
NB_LINES is the current number of lines for the current level of aggregation.

The second line of the information header has the following format:

Level:CURRENT_LEVEL / MAX_LEVEL >> KEY1 > KEY2 ...

Where :

CURRENT_LEVEL is the current level of aggregation in the range '1' to 'MAX_LEVEL'.
MAX_LEVEL is the maximum level of aggregation. It is equal to the number of fields in the request definition having Group equal Yes, plus one.
KEY1, KEY2, and so forth, are the value of the aggregation keys that are fixed. The number of keys is equal to the current level minus one.

On every request line, hyperlinks exist if the displayed data references another entity. For example, if an item reference is present on the line, a hyperlink to access to the item detail will be available.

Another link is present on the line that gives access to a list of two possible links called "+ Lev" and "-Lev":

+Lev displays the request at the next level of aggregation starting from the current line. The current line detail is displayed in cube mode. Otherwise, the request is not limited to the detail associated with the current line. This link is present only if the last level is not reached.
-Lev returns to the previous level of aggregation. This link is present only if the current level is not the first one.

For the following example, if a request for a total of quantity and a total value per date, per item, and per customer, the result will be the following:

At level 1

DateQuantity Amount2013/02/01 100 3000,002013/02/02 100 5000,00 -- LINE on which the click **Lev+** is done2013/02/03 180 7000,00...

At level 2, once you click the second line, the list will start at the detail of the 2013/02/02 date. This makes four lines that split the quantity of 100 and the amount of 5000 on four items. The next lines are the decomposition of the level 1 third line and so on:

DateItem Quantity Amount2013/02/02 CD10025 1500,00 + These 4 LINES2013/02/02 CD20030 2500,00 | are the decomposition2013/02/02 CD300 5 300,00 | of the previous2013/02/02 HT10040 700,00 + line2013/02/03 CD30030 1000,002013/02/03 RD678 100 4000,002013/02/03 ZE78950 3000,00...

In cube mode, only the four lines will be displayed. If the cube mode is disabled, the next lines will also be present.

**At level 3**, after clicking the fourth line, the decomposition of the quantity of 40 and amount of 700 for the item HT100 is shown on three lines for three customers. The next lines, which will appear only in non cube mode, pertain to another date and another item.

DateItem Customer Quantity Amount2013/02/02 HT100ABC1025 300,002013/02/02 HT100AGF0310 280,002013/02/02 HT100ERD76 5 120,002013/02/03 CD300ABC0020 700,002013/02/03 CD300RTG6710 300,002013/02/03 RD678AAB8980 3000,002013/02/03 RD768ERZ9020 1000,00...

6. Additional parameters

The URL of the page can accept other parameters. The default URL generated from the set up of menu item is as follows:

(server_URL)/html/main.html?url=/sdata/x3/$$prod/FOLDER/QUERY('REQ_NAM')?representation=QUERY~REQ_NAM~1.$query

Where:
* REQ_NAM is the name of the request.
* FOLDER is the Sage X3 People folder in which the request is executed.

Additional parameters can be added at the end of the URL in the following format:

&parameter_name=value

For example, you can have a URL that ends with:

...QUERY('REQ_NAM')?representation=QUERY~REQ_NAM~1.$query&count=200&startRecord=1200

When the query is defined from a menu item, these parameters are entered in the corresponding parameter array present on the item menu record.

The list of parameters available is provided below:

Parameter name	Possible values	Default value	Use	Example
cube	true, false	true	Allows to disable the cube mode if set to false.	&cube=false
forcedExecution	true, false	false	If set to true, it forces the re-execution of the request even if a relevant result has already been computed.	&forcedExecution=true
frequency	1 to 9	6	Defines that a shared or a personal request must be reexecuted if it has not been recomputed since the revalidation of the request, or since the computation time delay elapsed, according to the following values: 1=always 2=every hour 3=every day 4=every week 5=every month 6=never 7=every 10 minutes 8=every minute 9=every 10 seconds	&frequency=8
level	1 to N	1	The current level of grouping for the data. This parameter usually has the same value than the last segment in the representation value. The segment in the representation value is used to determine the columns that must be displayed at the current level, whereas the parameter defines the level of aggregation used for the displayed data.	&level=3
linesLimit	numeric value	0 (no limit)	This parameter is used to limit the number of computed lines. It is only useful if the request is executed and if the user wants to test a sample of data for the corresponding request. If the result is truncated because the maximum number of lines is reached, the information message will mention it: Line number limit exceeded (NNNN).	&linesLimit=5000
timeLimit	numeric value	0 (no limit)	This parameter is used to limit the time spent for computation. It is only useful if the request is executed and if the user wants to test a sample of data for the corresponding request. If the result is truncated because the maximum time is reached, the information message will mention it: Execution time limit exceeded (NNNN) Make sure that this does not limit the time spent by the database to compute the request, but only the cumulated time including the fetch time. When the time is limited to 100 seconds: - If the request computation takes 150 seconds and the fetch time 300 seconds, the computation will stop after 150 seconds and no line will be computed. - If the request computation takes 50 seconds and the fetch time 100 seconds, the computation will stop after 100 seconds and a partial result will be generated. - If the request computation takes 20 seconds and the fetch time 60 seconds, the computation will end before reaching the time limit and the request will be complete.	&timeLimit=20
startRecord	numeric value	1	This parameter defines the first line number displayed (used for paging).	&startRecord=3000
startIndex	numeric value	1	This parameter is generated by the paging links and is not used directly. In cube mode, the system uses the 'startRecord' value to know the current group of aggregated lines, and 'startIndex' relates to a relative page in this group.
requestID	UUID in canonical format	null UUID	This parameter defines the 'UUID' assigned to the request at the computation. If the 'UUID' is not null and not the right one, the request is recomputed. This is used in the paging and zooming links to ensure that the request was not recomputed.If the 'UUID' is not the right one, the following error message will be displayed: "The request was recomputed".	&requestID='7ca26fea-0735-4bd1-b49d-2f3dff3a4d68'
displayLineNum	true, false	false	This parameter, if set to yes, adds a column at the beginning of the grid to display the line number in the level.	&displayLineNum=true
recordPerPage	numeric value usually from 10 to 1500	user parameter 'NBRREQ' value for query (20 if no value found), and user parameter 'NBRREQBLK' in bulk mode (200 if no value found)	This parameter defines the number of lines displayed in a page. The query facet is used for a browser page, but the bulk protocol is used for the Excel client. It fetches 200 lines by default.	&recordPerPage=250
count			This parameter name can be used in place of the previous one. If both are defined, 'recordByPage' value is used.	&count=130
portview			Allows to get value of some parameters (initial level, computation frequency) from a portal view identified by its code.	&portview=MYVIEW
where	Condition in SData format	none	This condition allows the user to add filtering conditions based on the parameters that are defined in the request definition with Range equal True. Only simple conditions using "le", "ge", "eq", "lt", "gt" or "between... and" operators can be used. If several conditions are given, they must be associated with "and". When the condition changes, the request is recomputed.	&where=(ITEM gt 'ABC') and (QTY between 100 and 200)
first_XXX first_XXX	range value for XXX field	none	XXX must be one of the parameters of the query on which a range has been set. This is another way to create a range on the parameters, but these parameters will apply only if no &where clause is present.	Using &ITEM_first=ABC&ITEM_last=DEF is equivalent to &where=(ITEM between 'ABC' and 'DEF').

7. Batch computation

When the request execution is too heavy to be done immediately, the supervisor creates a batch execution request. An entry is created in the batch request table with the following information:

The execution time is set to the current date and hour.
The name of the task is 'SYRAREP'.
The parameter that are sent when the execution was requested.When the batch scheduler is on, the task will be executed if the maximum number of currently running tasks does not exceed the limit. This means that the user usually triggers the execution within seconds. If the batch scheduler is on every minute, it takes an average of 30 seconds.

8.Office Links

When the Excel link or Mailmerge link are used, the requester switches to bulk mode by using the $bulk facet. In this mode, the number of lines is not limited to 1,000 anymore.

For example, if you want to get the complete detail of a request in an Excel spreadsheet with less than 1 million lines, you can use the &cube=false&count=1000000 options from the Excel plugin.

9. Current limits

The current limits exist in this version.

the SQL requester is not available in this mode.
Calling the REQUEST executing function without giving a request code is not possible.
No direct entry of parameters is possible at the moment. The "portview" option allows to compute them.

10. Use of queries with graphs

When the query uses graphs, the following recommendations have to be followed:
* the query must have at least a breaking value (more than one level)
* the level must not be set to the last level if the user wants to see the breaking level as a title of the X series in the graph. On the last level, the serie label is always the line number (which is in most of the cases not relevant).
* the cube mode must be set to true.

11. Technical appendix

The requester generates a script (called WFname, where name is the name of the query). This script includes several subprograms called by the supervisor layer, and especially:
* a Subprog EXEC_RQT that executes the query.
* a Subprog GET_RQT that reads the result.
* a Subprog PROTO_JSON that returns the prototype describing the data returned.

This script has been generated in order to be easy to read (with comments). It has not to be modified, because it will be completely regenerated every time the query is validated. But it might be interesting to have a look at it, especially if errors happen at execution time.

This might happen in several cases:
* a query was done and mentioning a column that does no more exist in the table.
* a computation formula uses a variable that does not exist.
* a computation error (division by 0, for instance) happens during the execution.

The error, the script and the corresponding line will be mentioned in this case. Here an example of an error found at execution time:

The script WFVEN073 looks like this:

# Sage X3 People supervisor# Request VEN073 (Customers without any orders) 2015-09-09T07:46:06...# Open the tablesLocal File VIDLECUST [VIC]Local File ALISTEH [ALH]Local File AFCTFCY [Z_AFY]# Default filesDefault File "[VIC]"# Open the data file and fill them with the right headerGosub HEADRQT# Create a .lock file with UUIDOpeno filpath('RQT',REQUID,'lck') Using [LCK] : Openo Using [LCK]# Assign the additional criteriaCRITSUP(1)="([F:VIC]AUSNUM = GACTX.USER |[F:VIC]AUSNUM2= GACTX.USER)"# Entry pointLocal Char RQT_CODE(20)RQT_CODE='VEN073'Gosub EXERQT From SUBALH11# Now let's declare the request (Link) and the fields expected (Columns)Link [VIC] With & [Z_AFY]AFF0 ~='';GACTX.APRFCOD; 'GESSOH'& As [LNK]& Where evalue(CRITERE)& & evalue(CRITSUP)& Order by Key CLE = [F:VIC]ORDDATColumns [LNK] ([F:VIC]BPCNUM,[F:VIC]BPCNAM,[F:VIC]SOHNUM,[F:VIC]ORDDAT,& [F:VIC]CNTSOH,[F:VIC]REP1,[F:VIC]REPNAM,[F:VIC]REP2,[F:VIC]REPNAM2,& [F:VIC]SOPLIN,[F:VIC]SOQSEQ)

The line 229 corresponds to the Columns instruction, and the reason was that one of the columns was no more present in the view ([F:VIC] is the abbreviation of a view here).

Index Home