Report Data Retrieval Logic Block

QAD 2017 Enterprise Edition > User Guides > Reporting Framework > Implementing a Progress Data Source Program > Developing the Data Source Program Code > Report Data Retrieval Logic Block

The data retrieval code block is used to populate temp-tables with data. A dynamic query must be used if there are any search fields, since the search conditions come from the client request and can vary at run time based on the user’s selections on the prompt page of the report viewer. The data retrieving logic should be coded into the designated place in the data source program.

Defining the Dynamic Query

Here is an example that illustrates how to structure the code for the dynamic query. Note that the search fields all reside in the so_mstr table.

define variable queryString as character no-undo.

define variable hSOQuery as handle.

define query SOQuery for so_mstr.

hSOQuery = query SOQuery:handle.

queryString = "for each so_mstr no-lock "

+ " where so_mstr.so_domain = " + QUOTER(global_domain).

run FillQueryStringVariable in reportHandle (input "ttSalesHeader", input "so_nbr", input-output queryString).

run FillQueryStringVariable in reportHandle (input "ttSalesHeader", input "so_cust", input-output queryString).

run FillQueryStringVariable in reportHandle (input "ttSalesHeader", input "order_date", input-output queryString).

queryString = queryString + ":".

hSOQuery:query-prepare(queryString).

hSOQuery:query-open().

hSOQuery:get-next().

Note: The FillQueryStringVariable function will get the search conditions sent from the client request (each consisting of the search field, operator, and value entered by the user) and then construct the corresponding where clause fragment and add it to the query string dynamically at run time.

The following lists the input parameters for the FillQueryStringVariable function:


Name	Input/Output	Data Type	Description
bufferName	Input	Character	Temp-table name
fieldName	Input	Character	Field name in the temp-table
queryString	Input-Output	Character	Dynamic query string

Note: For the function to work, the temp-table fields defined as search fields in the metadata definition should use exactly the same names as those in the database.

Note: If other static filter conditions are desired in the query string, they can be added in place of the “where true” part of the query string in the above example. For example, if we wanted this report to filter its query to only include orders whose so_site value is “SITE1”, we could use the following statement to begin the query string:

queryString = "for each so_mstr no-lock where so_site = " + QUOTER("SITE1").

The QUOTER() function returns the input string wrapped in quotes, and is useful when specifying text in a dynamic query string that must appear quoted in the final query string.

If static sorting is desired, a “by” clause can be added at the end of the dynamic query string. For example, the following change to our above example will cause records to be sorted primarily by so_cust:

queryString = queryString + " by so_cust:".

There is another procedure called FillQueryString that will automatically add the filter conditions for all the fields in a given temp-table. This is often the most concise way to implement filter fields. The following code statement illustrates how to use this procedure:

run FillQueryString in reportHandle (input "ttSalesHeader", input-output queryString).

The following lists the input parameters for the FillQueryString function:


Name	Input/Output	Data Type	Description
bufferName	Input	Character	Temp-table name
queryString	Input-Output	Character	Dynamic query string

Retrieving Data

Loop over the so_mstr records to create the ttSalesHeader temp-table and retrieve detailed information from each record. The inner loop over so_det records is used to retrieve all detail records for each master record.

repeat while not hSOQuery:query-off-end:

create ttSalesHeader.

assign

ttSalesHeader.so_nbr = so_mstr.so_nbr

ttSalesHeader.so_cust = so_mstr.so_cust

ttSalesHeader.so_ord_date = so_mstr.so_ord_date

ttSalesHeader.sales_order_slspsn1 = so_mstr.so_slspsn[1]

ttSalesHeader.sales_order_slspsn2 = so_mstr.so_slspsn[2]

ttSalesHeader.sales_order_slspsn3 = so_mstr.so_slspsn[3].

ttSalesHeader.sales_order_slspsn4 = so_mstr.so_slspsn[4].

for each sod_det no-lock

where sod_det.sod_nbr = so_mstr.so_nbr:

create ttSoLine.

assign

ttSoLine.sales_order_number = sod_det.sod_nbr

ttSoLine.sales_detail_line = sod_det.sod_line

ttSoLine.sales_detail_item = sod_det.sod_part

ttSoLine.sales_detail_unit_measure = sod_det.sod_um

ttSoLine.sales_detail_due_date = sod_det.sod_due_date.

end.

hSOQuery:get-next().

end. /* Repeat query */

Dynamic Report Settings by Data Source Program

Starting with the QAD .NET UI 2013 EE release, you can have your data source program dynamically set report settings. This approach allows most report settings to be set by the data source, including the layout.

Important: This is an improved and generalized mechanism that supercedes the dynamic layout selection by data source mechanism (see Dynamic Layout Selection by Data Source). This improved mechanism not only allows the data source program to determine the layout (sys_default_report_definition setting) but also can be used to determine other report settings as well. The previous technique that was specific to choosing the report layout is still supported for back compatibility; however, a data source program cannot use both techniques in the same program.

Here are the steps to follow to enable your data source program to set report settings:

1 Add a special table called DataSourceReportSettings to your data set, with the following exact definition:

define temp-table DataSourceReportSettings

field setting_name as character

field setting_value as character

You should add it to the data set definition without relations (as in the following example, where the business tables ttSalesHeader and ttSoLine are related, but DataSourceReportSettings is not):

define dataset dsReportResults for ttSalesHeader, ttSoLine, DataSourceReportSettings

data-relation drLine for ttSalesHeader, ttSoLine

relation-fields (so_nbr, sales_order_number)

2 Populate the DataSourceReportSettings table with one row per setting in the RunReport procedure. The following example illustrates this, showing how to change various language, date, and number format settings:

/* Set setting for label language */