O4W Reporter

From the O4W Main Menu choose O4W Reporter.

From the O4W Reporter menu choose Create/Modify.

From the Report Selection tab you can either select an O4W Report to modify from the drop down box, select an OpenInsight ReportBuilder report to convert, enter the name of a new report to create or you can manually enter an R/List statement. Click the Next button to continue.

We will now explore each component on the Layout tab.

A textbox is provided to enter a description of this report. Choose the table to be associated with this report from the drop down list. This drop down list will contain all tables attached to the application that O4W is logged into.

HTML ‘template’ - All the O4W wizard routines (O4W Forms, O4W Reports, O4W Dashboards, etc.), as well as the O4W API’s, allow the developer to specify one of more “templates” on which their output can be displayed. A template contains all the additional html elements needed to generate the full web page that a browser can display. This includes images, colors, and style elements that both surround, and may be used by, the O4W routine’s output. O4W includes several sample templates. These templates are stored in the TEMPLATES directory located within OpenInsight at \Revsoft\OInsight\O4W\TEMPLATES.

Background image - If a template is not used you can specify the path of a background image to watermark each O4W page in the textbox provided.

Header – Specify the report header in the textbox provided.

Footer – Specify the report footer in the textbox provided.

Procedure to run first – The “run first” process is designed to work in 2 different ways:

1. Using an r/list statement, or

2. Using a stored procedure

So, you can specify as the runfirst command:

SELECT 5 MYTABLE BY SOMEFIELD

_or_ you can specify a stored procedure name PRECEDED BY “@”:

@MYRUNFIRSTROUTINE

If specified, this stored procedure should take three parameters.

subroutine MY_RUNFIRST_PROCEDURE(STMT, selstmt, passed_selector)

where "stmt" is the sort statement, "selstmt" is the selection statement (if any), and "passed_selector" is an @FM delimited list of values that correspond to any user-entered prompts. The runfirst procedure should generate an active select list.

Procedure for reading – If specified, your stored procedure should have 4 parameters: TableName (passed in), recorded (passed in), record (set by subroutine), and IsNewFlag (set by subroutine to “1”) if this is a new (not on file) record. For each record that it is going to process, O4W Report will call your stored procedure, passing in the tablename and ID, and expecting to get back a fully populated record. What you do to build that record – a direct read, or multiple reads to build a ‘psuedo-record’, or a read and then modification of that record, etc. – is up to you the developer.

Number of lines/page – You can specify the number of lines per each O4W page generated. The default is 20.

Number of lines/page if called as popup – Any O4W report can be called as a popup from an O4W Form. You can specify the number of lines per popup page. The default is 10.

‘Zebra-stripe’ report – Selection of this option will cause each detail line of the O4W report to alternate color. This option will not work if the report utilizes BREAK-ON.

A drop down list is provided to select a menu to appear on the report. If no menu is desired choose None.

Enter your report permissions from the drop down box. You can create additional menu permissions in the O4W Maintenance section of O4W.

You can display the number of records to be processed prior to running the report.

If desired, the report runner can invoke a customized commuter module when various report events occur. The commuter module can be invoked when the report runner starts, before any other processes (CREATE), before the records are selected (SELECT), when any in-page search button is pressed (SEARCH), before each record is read for display (PRE-READ), and before (PRE-PAGE) or after (POST-PAGE) each page of output is generated

Links can be provided at the bottom of every page on a report. A description of Link To options can be found at the back of this reference guide.

Template to use if called as a popup – If this report is to be used as a popup in an O4W Form, you can specify a template to use. In most cases the report as a popup will not use a template.

Background color – A color picker control is provided to select the report background color. You can choose one of the colors provided or enter a specific hex color value.

Foreground (text) color - A color picker control is provided to select the report foreground text color. You can choose one of the colors provided or enter a specific hex color value.

Link color - A color picker control is provided to select the report link color. You can choose one of the colors provided or enter a specific hex color value.

Visited link color - A color picker control is provided to select the report visited link color. You can choose one of the colors provided or enter a specific hex color value.

Report alignment – General report alignment can be chosen from the radio set provided.

Show border around cells – An option to show borders around each report cell is provided.

Allow CSV download? – An option is provided to allow the user to download the report details as a CSV file.

Allow XML download? – An option is provided to allow the user to download the report details as a XML file.

Allow PDF download? – An option is provided to allow the user to download the report as a PDF document.

Suppress Detail? – The choices are Yes for DETAIL-SUPPRESS (only summary lines are displayed), No (all detail lies are displayed), Yes, but link to detail (a summary report of report sub-totals is created with links to the report detail).

Paginator Routine – O4W will use the pagination routine specified on the configuration screen by default. If you wish to use a different pagination routine for this report, select the name of the pagination routine to use here. Developers can create their own pagination routines; O4W provides six pagination routines: “jQuery TableSorter Plugin” does the pagination entirely on the browser, while “Local Pagination” uses the server to paginate the results. You may choose to have the jQuery plugin placed on the top or bottom of the page, and the local paginator may be displayed at either the top or bottom of the page, with or without page numbers.

We will now explore each component on the Field Selection tab.

As changes are made to your report you can click the Refresh Statement button to redisplay the R/List statement being created.

The All Fields multiline textbox contains all the dictionary names contained in the table associated with this report.

Fields must be selected for three categories: Sort, Select and Display.

For Sort choose the dictionary fields you want to sort by from the All Fields box and click the Sort button to add fields to the Sort Fields section. Click the Remove button under the Sort Fields section to remove sort fields.

For Selection choose the dictionary fields you want to select on from the All Fields box and click the Select button to add fields to the Selection Fields section. Click the Remove button under the Selection Fields section to remove selection fields.

For Display choose the dictionary fields you want display on your O4W Report from the All Fields box and click the Display button to add fields to the Display Fields section. Click the Remove button under the Display Fields section to remove display fields.

We will now explore each component on the Sorting tab.

The sort order can be changed by left clicking on a field and dragging that field to a new position.

You can also specify the sort type: ascending or descending for each sort field.

We will now explore each component on the Selection tab.

The Join choices are as follows:

The Modifier choices are as follows:

Selection fields are displayed in the order that they were selected. You can change the selection order by left clicking on a field and dragging that field to a new position.

The selection comparison types are as follows:

· <implicit> - user can enter a comparison operator and value

· equals - =

· greater or equal - >=

· less or equal - ⇐

· greater - >

· less - <

· starts with - ] xxxx

· ends with – [ xxxx

· contains – [] xxxx

· is not equal to - #

The Prompt/Value types are as follows:

· Prompt – The user will be prompted to enter a value for use with the selection criteria.

· Stored Procedure – A stored procedure will be called to return a value for use with the selection criteria. No visible prompt will be displayed; the value returned by the stored procedure will be used in that portion of the selection criteria. Note that the stored procedure should take two parameters; the first parameter can be ignored, but the stored procedure must return the desired value to use in the second parameter. For example, you may define the following subroutine:

SUBROUTINE MySubroutine(ignoredParam, rsltParam)

The calculated value you wish to use in the selection should be returned in the rsltParam parameter.

· Explicit – An explicit value will be used with the selection criteria. When specifying a default value for a field in the form wizard, please note that if a literal value is specified, it should be entered in “internal format”, as the form wizard will apply any specified output conversions on the data before it is displayed. For example, when using a literal default value on a field that has a date conversion, the default value should be specified as an internal date (16488) rather than an external date (02/20/2013).

· ComboBox – An OpenInsight subroutine will be called that will return the prompt text, codes and descriptions for use with a drop down list box. A sample subroutine is provided at the back of this reference guide.

Enter your prompt text, stored procedure name or subroutine name in the textbox provided.

If Yes is selected and no data is entered for this selection criteria the selection option is skipped.

The Convert Response types are as follows:

· No change – All entered data will be used as entered.

· UPPERCASE – All entered data is converted to upper-case.

· lower-case – All entered data is converted to lower-case.

· case-insensitive – Specific case will not be utilized for selection.

If the user should be allowed to enter multiple values for the selection value, select Yes from the “Multiple Select?” column. If a list of possible values is displayed (either programmatically or because the current field is indexed), multiple selections can be made by holding down the shift or control key when clicking; if a textbox is displayed, multiple values can be entered by comma-delimiting them in the textbox.

If this is an indexed field you can set the maximum number of records to display in the dropdown list.

You can select the display order of the index results

The DUP button allows you to repeat selection criteria for the same field. If for example you are selecting on ORDER_DATE you can repeat the selection criteria for this field to accomplish a select such as: SELECT ORDERS WITH ORDER_DATE >= “MM/DD/YY” AND WITH ORDER_DATE ⇐ “MM/DD/YY”. The Del button allows you to delete this section of the selection criteria.

We will now explore each component on the Display tab.

The fields to display on your O4W Report appear in the order that they were selected. You can change the display order by left clicking on a field and dragging that field to a new position.

Each field label will use its dictionary description by default. You can change the label description by entering the label text in the textbox provided.

You can set the Label Properties for each field by clicking the button.

We will now explore each component for setting Label Properties:

· Change ALL label properties to match this? – If this box is checked, all label properties for fields on this report will be duplicated.

· Font name: - You can specify a font name for this label (e.g. Tahoma)

· Font Size: - You can specify the font size as a “pixel size” using the “px” suffix (ie, “15px”), “point size” using the “pt” suffix (ie, “12pt”), or as a percentage of the normal font size (ie, “180%”)

· Background Color: - A color picker control is provided to select the label background color. You can either choose one of the colors provided or enter a specific hex color value.

· Text Color: - A color picker control is provided to select the label text color. You can either choose one of the colors provided or enter a specific hex color value.

· Alignment: – The text in the label can be aligned to the left, center or right.

· Bold? – The label text can be set to bold by checking the Yes box.

· Italic? – The label text can be set to italic by checking the Yes box.

· Sortable? – The column for this label can be made sortable by checking the Yes box.

· Column width - You can set the column width by entering a pixel value, followed by “px” (for example, 400px).

· Display Permissions – You can customize the output so that certain fields are displayed only to users with appropriate permissions. If the user running the report does not have sufficient permissions (according to their login), then this field will be suppressed in the output.

You can set the Value Properties for each field by clicking the button.

We will now explore each component for setting Value Properties.

· Change ALL value properties to match this? – If this box is checked, all value properties for fields on this report will be duplicated.

· Font name: - You can specify a font name for this value (e.g. Tahoma)

· Font Size: - You can specify the font size as a “pixel size” using the “px” suffix (ie, “15px”), “point size” using the “pt” suffix (ie, “12pt”), or as a percentage of the normal font size (ie, “180%”)

· Text Color: - A color picker control is provided to select the value text color.

· Alignment: - Values can be aligned within a cell with left, center or right justification.

· Output Conversion: - If checked, the output conversion from the dictionary will be applied to the data; otherwise, you can explicitly enter an output conversion code to customize the displayed output

· Return the value of this field if called as a ‘popup’? – Checking this box sets this field as a return value when this O4W Report is utilized as a popup with an O4W Form.

· Suppress splitting at spaces? – By default, the browser will attempt to make each output column fit the allotted display area; to do this, it may “break” the output column at space delimiters. You may check this box if you wish to suppress this default behavior of splitting of this column at spaces.

The background color of a field can be set with the following criteria:

· for all values – All values will have the background color selected.

· for values = explicit value – You can enter the explicit value in the Comparison textbox

· for values = user prompt – You can enter the user prompt in the Comparison textbox

A color picker control is provided to select the value background color. You can either choose one of the colors provided or enter a specific hex color value.

You can set the Value Modifier Properties for each field by clicking the button.

We will now explore each component for setting the Value Modifiers.

· Modifier type: – The modifier type can be set with the following criteria.

· Modifier color: - The modifier color can be set with the following criteria.

· Comparison value or prompt: - If the modifier color criteria is “for break values = explicit value”, enter the explicit value that will be compared with the break value. If the modifier color criteria is “for break values = user prompt”, enter the prompt that will be displayed (whatever value is entered for this prompt will be used for the comparison).

· Break text: - Enter the output text for the break-on line (e.g. Subtotal).

You can set the Value Links Properties for each field by clicking the button.

We will now explore each component for setting the Value Links.

The link types are as follows:

· No Link – There is no link for this value.

· O4W Form (Key) – The value of this field is a key link to an existing O4W Form. Select the O4W Form Name from the drop down listbox provided.

· O4W Procedure –The value of this field will be passed into an O4W stored procedure via the PARAM1 parameter. Select the O4W stored procedure from the drop down listbox provided. Note that this does not directly call the specified procedure; rather, it places a link by which the specified procedure can be invoked through normal O4W means.

· Explicit URL – Enter the explicit URL in the textbox provided (e.g. http://www.revelation.com). You may use the wildcard character “^” to indicate that the current displayed value should be embedded in the URL.

· Value is URL – The value of this field is a URL.

· Email Link –The value of this field is used to place an email link (e.g. info@revelation.com) on the browser

· Embedded URL – The value of this field is used to create an “embedded” browser window within the existing browser window; this embedded browser window can be used to display information from another site (for example, a Google Docs spreadsheet or text document) side by side your current form information.

· LinkedIn Profile – The value of this field is used to display the LinkedIn profile

· LinkedIn Share – The value of this field is used to display the LinkedIn “share” link

· Call commuter for URL – The value of this field is used to envoke the commuter module.

The Open link in types are as follows:

The Display Image properties are as follows:.

Value is URL – The contents of the field are a full or relative URL to a picture; for example, the field contains “http://www.example.com/images/employee1234.jpg” for one record, “http://www.example.com/images/employee3456.jpg” for another record, etc.

Explicit Link – The contents of the field contain some information that can be used in a URL to retrieve an image, but the full or partial URL must be specified separately. For example, the records contain “1234”, “3456”, etc. in this field; you could enter as the explicit link “http://www.example.com/images/employee^.jpg” (note the use of the wildcard character “^” to represent the current field’s value). O4W will in this case combine each field’s value with the explicit link to generate a full URL.

Call commuter for URL – Calls the commuter module.

An O4W Report column can be repeated by pressing the DUP button or deleted by pressing the DEL button.

A color picker control is provided to select the Grand-Total color. You can either choose one of the colors provided or enter a specific hex color value.

Enter the Grand-Total text in the textbox provided.

We will now explore each component on the Completion tab. The O4W Reporter will display the SELECT statement to be passed into OpenInsight for processing. Clicking the Save button will save your report.

Once your report is saved you can run it by pressing the link shown below.

For styling purposes:

· The ‘collector’ window has a table with class “prompts”

· The main results table has a class named “results”

· The drill-down details have a class named “details”

FUNCTION O4WCMR_ORDER_REPORT(CtlEntId, Event, Request)

 

* Auto-generated by O4W_DEFINE_REPORT at 13:51:06 27 JUL 2018

 

* Standard equates

$Insert O4WEquates

$Insert O4WReportEquates

$Insert O4W_COMMUTER_COMMON

 

rtnValue = 1

Begin Case

 Case event _eqc 'CREATE'

 * called when the report is first created

 * variable 'ctlentid' is a unique identifer for this report

 * To abort report processing at this point, set rtnValue to 0 (rtnValue=0) and optionally set statMsg@ to desired error text

 Case event _eqc 'READY'

 * called when the report is first drawn

 Case event _eqc 'SELECT'

 * called when records are being selected

 * variable 'ctlentid' is name of list record (in SYSLISTS table) to be generated

 * userFields@<1> is the rlist statement that the report would normally run to sort the contents

 * userFields@<2> is the rlist statement that the report would normally run to select the matching records

 * userFields@<3> is the list of values for each selector specified in the report definition

 * set variable 'rtnValue' to 0 (rtnValue=0) to abort selection

 * set variable 'rtnValue' to 1 (rtnValue=1) if the commuter module is generating an active select list

 * set variable 'rtnValue' to -1 (rtnValue=-1) if generating list of matching ids in the commuter module

 * set variable 'rtnValue' to null (rtnValue ='' ) to continue normal selection processing

 * (if generating list of matching ids in this code, and you intend to set rtnValue=-1, you should create a list record with the id passed in CtlEntId)

 * set variable 'statMsg@' to desired error text (if no items are selected)

 Case event _eqc 'PRE_SEARCH'

 * called during page generation

 * variable 'ctlentid' is name of the section where the search area is being created

 * set variable statMsg@ to the prompt text to use in the search area

 * set variable 'rtnValue' to 0 (rtnValue=0)to skip creation of search area prompt and controls

 * set variable 'rtnValue' to -1 if commuter module creates its own search area prompt and/or controls

 * (If creating the search area in this code, and you intend to set the rtnValue=-1, you should:

 * - use a button with the ID 'BTN_SEARCH', qualified for the CLICK event

 * )

 Case event _eqc 'SEARCH'

 * called whenever a button with the ID starting 'BTN_SEARCH' is pressed

 * this routine should generate a new selection of records, based on user-entered text, from the prior list of selected records

 * variable 'ctlentid' is the name of the button that was pressed

 * userFields@<1> contains the name of the list record (in O4WTEMP table) to be created

 * userFields@<2> contains the name of the list record (in O4WTEMP table) created during initial report selection

 * userFields@<3> contains the user-entered text in the default search textbox

 * userFields@<4> contains the name of the section where the search area was created

 * set variable 'rtnValue' to 0 (rtnValue=0) to indicate no records found

 * (Set statMsg@ to any message you wish to display)

 * set variable 'rtnValue' to -1 (rtnValue=-1) to indicate search processing is not complete

 * (Used if this routine generates a dialog that requires further processing; be sure all controls that generate an event to capture use

 * an ID starting 'BTN_SEARCH')

 Case event _eqc 'PRE_READ'

 * called whenever record is to be read

 * variable 'ctlentid' is ID of record

 * examine variable bIsNew@ to determine if this is a new record (bIsNew@=1), or a record that is supposed to exist (bIsNew@=0)

 * set variable 'rtnValue' to 0 (rtnValue=0)to disable further record processing

 * set variable 'rtnValue' to -1 (rtnValue=-1) to skip READ but continue processing

 * (If performing the read in this code, and you intend to set the rtnValue=-1 or rtnValue=1, you should:

 * - fill the @RECORD variable with the desired record contents

 * )

 Case event _eqc 'PRE_DRAW'

 * called before each page has been generated

 * variable 'ctlentid' is either 'DETAILS' (for the drill-down details page) or 'RESULTS' (for the overall results)

 * userFields@<1> is the row number to begin retrieval at

 * userFields@<2> is the number of rows to retrieve

 * userFields@<3> is the number of rows/page

 Case event _eqc 'POST_DRAW'

 * called after each page has been generated

 * variable 'ctlentid' is either 'DETAILS' (for the drill-down details page) or 'RESULTS' (for the overall results)

 * userFields@<1> is the row number to begin retrieval at

 * userFields@<2> is the number of rows to retrieve

 * userFields@<3> is the number of rows/page

End Case

 

Return rtnValue

From the O4W Reporter Menu choose Create/Modify AdHoc.

Select a previously defined AdHoc Report or enter the name of a new AdHoc Report.

We will now explore each component of the Definition tab.

Description – An internal description of the Ad Hoc report for your own reference.

Title – The “bookmark title” that appears at the top of the browser page, near the URL.

Header – The title that appears at the top of the form.

AdHoc Report permissions – Enter your Ad Hoc permissions from the drop down box. You can create additional permissions in the O4W Maintenance section of O4W.

Display menu on page – A drop down list is provided to select a menu to appear when running this Ad Hoc report. If no menu is desired choose None.

We will now explore each component of the Details tab.

O4W Form – Choose the name of the O4W Form that will serve as the source of the prompts for this Ad Hoc report.

Label for this tab – Enter the label you wish to use for the O4W Form tab of the Ad Hoc report.

Support subroutine – If desired, enter the optional name of a stored procedure that contains O4W API calls to display additional prompts, questions, notes, etc.

Label for this tab – If the optional stored procedure support routine has been specified, enter the label to use for the tab that will contain its prompts, questions, etc.

O4W Report – Choose the name of the O4W Report that will serve as the “skeleton” for the output of the selected records.

Stored procedure to use – If the chosen O4W Report has a commuter module defined, you may choose to run a different stored procedure as the commuter module when the O4W Report is invoked by the Ad Hoc reporter. Alternatively, you can also check the “Disable” checkbox to disable the use of the commuter module entirely.

You can save and run your AdHoc Report from the Completion tab.

We will now explore how to run your AdHoc Report.

When the Ad Hoc report definition is run, each tab of the selected O4W Form will become its own section of fields; within each section, users of the Ad Hoc report can select which fields they wish to display, and how they wish the data to be selected for output.

If a field is defined in the O4W Form as having a list of choices, then users will be presented with the option to select from those choices if they wish to use this field as part of the selection criteria.

Once all the fields that the user wishes to display have been selected, and all the selection criteria has been specified, the user will press the Run Report button to actually generate the output.

Below is the result of your AdHoc Report.

From the O4W Reporter Menu choose Register BRW.

Select a previously registered Banded Report definition to modify or enter the name of a new Banded Report for use with O4W.

We will now explore each component of the Definition tab.

Description – An internal description of this BRW definition for your own reference.

Report Group – Select the BRW report group that contains the individual report you wish to register for use through O4W. Note that each individual report in the report group must be registered separately.

Banded Report permissions – Select the minimum required permissions for running this BRW report from the drop down box. You can create additional permissions in the O4W Maintenance section of O4W.

We will now explore each component of the Details tab.

Specific Report – Select the individual report (contained within the previously specified Report Group) that you wish to register.

Allow Override Saved List – If “No” is selected from the drop down list, then O4W_BRWREPORT will ignore any “override save list” names that might be passed into the BRW report; if “Yes” is selected, then the parameter OverrideList can be specified with the name of the saved list to use when generating the BRW output.

Allow specific permissions on PDF – If checked, any generated PDFs will have the specified password protected restrictions.

PDF Owner Password – Enter the optional owner’s password to apply to generated PDFs.

PDF User Password – Enter the optional user’s password to apply to generated PDFs.

Click the Save button to register your BRW Report for use with O4W.

You also have the option to run your registered BRW Report.

From the O4W Reporter Menu choose Copy/Delete.

Select the report name you want to copy. Enter the name of the report you want to create in the textbox provided and click the Copy button. To delete an existing O4W Report, select the form name you want to delete and press the DELETE button.

From the O4W Reporter Menu choose Copy/Delete AdHoc.

Select the AdHoc report name you want to copy. Enter the name of the AdHoc report you want to create in the textbox provided and click the Copy button. To delete an existing O4W AdHoc Report definition, select the Adhoc report you want to delete and press the DELETE button.

From the O4W Reporter Menu choose Run.

Select the name of the O4W Report you want to run and press the Run button. You can also enter a R/List statement in the text box provided and press the Run button.

From the O4W Reporter Menu choose Run AdHoc.

Select your AdHoc Report definition, set your criteria and then choose Run Report.