Using the report template created earlier, we will create a report listing open invoices by customer, with the customer information as the main block, and the open invoice information as a subsidiary block.
Select the Group Information object. Expand it, and select the group to which you wish to add the new report entity.
Right click on the group and select
Create->Entity.
Click on the Report
bullet.
Click on the Next button at the bottom right of the screen.
In the Entity Name property, type OPEN_INVOICES as the entity name.
Leave the Datasource
property set to (use default), since there is only one datasource for
the Xephr
In the Based On property, select REPORT_TEMPLATE, which is the template that we created earlier.
Leave the Default Mode property set to Parameter Form.
Leave the Body Style set to (from parent).
Leave the Editable Field Style set to (from parent).
Leave the Non- Editable Field Style set to (from parent).
Leave the Lov Style set to (from parent).
Leave the Link Style set to (from parent).
Leave the Document
Security set to Not Secure.
Click on the Next button at the bottom right of the screen.
In the Block Name property, enter CUSTOMERS as the name of the block.
In the Block Type property, select Standard Block.
Set the Query Type property to Simple Query.
Leave the Datasource property set to (use parent), since there is only one datasource for the Xephr install.
In the Table property, select the name of the table or view from which the data will be obtained. Since our datasource is set to use the Oracle 9i database for NDS Applications, we will select the view CUSTMAST_VW.
Leave the Create Heading checkbox set to off since we do not want a heading to be created for the block.
In the Scheme
Selection section, set the Form
bullet to on since we want the
labels and database fields displayed in the same block.
Click on the Next button at the bottom right of the screen.
The Adding Fields screen is displayed. Select the columns that you wish to include in the report.
Click on the name of the column
to select both the Field and Label checkboxes for that column. Do
this for the following columns
CITY
CONTACT
CONTACT_PHONE
CUST_ADDR1
CUST_NAME
CUST_NO
CUST_URL
E_MAIL
FAX_PHONE
SLSMAN
STATE
TERMS_CD
ZIP_CODE
A key must be selected to continue. Set the Key checkbox to on for the CUST_NO field as it is the key to this view.
In the Filters
property, you could enter code to restrict the data returned from the
table. We want to include only active customers
for the user's logged in financial enterprise.
where status_code = 'A' and org_unit_id = $$ORG_UNIT_ID$$
In the Ordering and
Grouping property, you can enter code to determine the order in
which the data is displayed in the report. We want
the report to be ordered by customer name.
order by cust_name
Click on the Next button at the bottom right of the screen.
The Ordering Fields
screen is displayed next. The columns are initially
ordered as they were listed on the previous screen. To
move a field up or down, highlight the desired field by selecting it,
and click on the Move Up or Move Down buttons on the right of the screen.
Move the fields until they are in the following
order:
CUST_NO
CUST_NAME
CUST_ADDR1
CITY
STATE
ZIP_CODE
CONTACT
CONTACT_PHONE
FAX_PHONE
CUST_URL
E_MAIL
SLSMAN
TERMS_CD
Click on the Next button when you have finished moving the fields.
The Option to Add More
screen is displayed. We need to create the detail
block next. Set the Add
Block or Labels checkbox to on. The entity
in its current form will be displayed.
Expand the OPEN_INVOICES entity and select the CUSTOMERS
block, as we are adding the detail block to this block..
Click on the Add Block button at the bottom right of the screen.
In the Block Name property, enter INVOICES as the name of the block.
In the Block Type property, select Standard Block.
Set the Query Type property to Simple Query.
Leave the Datasource property set to (use default), since there is only one datasource for the Xephr install.
In the Table property, select the name of the table or view from which the data will be obtained. Since our datasource is set to use the Oracle 9i database for NDS Applications, we will select the view ARINVSUM_VW.
Leave the Create Heading checkbox set to off since we do not want a heading to be created for the block.
In the Scheme
Selection section, set the List
bullet to on since we want the
labels and database fields displayed in the separate blocks, with the
labels at the top, and the database fields in a list below them.
Click on the Next button at the bottom right of the screen.
The Adding Fields screen is displayed. Select the columns that you wish to include in the report.
Click on the name of the column
to select both the Field and Label checkboxes for that column. Do
this for the following columns
CUR_BALANCE
CUST_NO
CUST_PO
INVDATE
INVNO
INVOICE_TYPE
A key must be selected to continue. Set the Key checkbox to on for the CUST_NO field and the INVNO field as they are the keys to this view.
Formatting can be applied to some
of these fields. In the Format
column, select the following format masks for the following fields.
CUR_BALANCE #,###,###.00
INVDATE MM-dd-yyyy
In the Filters
property, you could enter code to restrict the data returned from the
table. We want to include only invoices that have
not been paid in full for the user's logged in financial enterprise.
where cur_balance <> 0 and org_unit_id = $$ORG_UNIT_ID$$
In the Ordering and
Grouping property, you can enter code to determine the order in
which the data is displayed in the report. We want
the data on this block to be ordered by invoice number.
order by invno
Click on the Next button at the bottom right of the screen.
The Ordering Fields
screen is displayed next. The columns are initially
ordered as they were listed on the previous screen. To
move a field up or down, highlight the desired field by selecting it,
and click on the Move Up or Move Down buttons on the right of the screen.
Move the fields until they are in the following
order:
INVNO
CUST_PO
INVDATE
INVOICE_TYPE
CUR_BALANCE
CUST_NO
Click on the Next button when you have finished moving the fields.
The Create Join
screen is displayed next. If you need to create
a join between a field in the master block and a field in the detail block
that were named differently, this is where you would do it. Since
the join is on the CUST_NO field, and they are named the same in both
the master and the detail blocks, you do not need to create a join here.
Click on the Next button to continue.
The Option to Add More
screen is displayed. We need to add one more block
for the parameter form. Set the Add Block
or Labels checkbox to on. The entity in
its current form will be displayed.
Click on the OPEN_INVOICES entity to select it. We
need to add the block to the report itself.
Click on the Add Block button.
The Adding New Block screen is displayed.
In the Block Name field, enter PARAMETER_FORM.
Set the Block Type to Parameter Form.
Set the Create Heading flag to on as we want an extra field on this block for a header.
In the Editable Field Style pop-list, select DEFAULT_DATA_ENTRY.
In the Non-Editable Field Style pop-list, select DEFAULT_STYLE.
In the Body Style pop-list, select DEFAULT_STYLE.
In the Link Style
pop-list, select LINKS.
Click on the Next button to continue.
The Adding Fields screen is displayed next. A field is displayed here for each of the database fields that were selected on the database block.
Set the Include
flag to on for the CUSTOMERS.CUST_NO
field, as we want the report to be able to be restricted to a specific
customer.
Click on the Next button at the bottom of the screen to continue.
The Ordering Fields
screen is displayed Next. Since we only have one
field, there's nothing to order.
Click on the Next button at the bottom of the screen to continue.
The Option to Add More screen is displayed. We need to add a label to the parameter form in order to run the report.
Set the Add Block or Labels flag to on.
Expand the OPEN_INVOICES entity.
Click on the PARAMETER_FORM block to select it.
Click on the Add Labels button at the bottom of the screen.
The Adding Labels screen is displayed.
Click on the Add button to add a field to the section on the left.
In the field, enter Run.
Click on Next
button at the bottom of the screen.
The Option to Add More
screen is displayed.
We have no more blocks to add, so click on the Finish button.
The new entity is added to the initially selected group.
Click on the Save button in the main toolbar to save the new report entity.
In the Explorer Tree, left click on the OPEN_INVOICES report entity and display the property sheet.
In the Title
property, enter the title for this report - Open Invoices by Customer
All other settings come from the report template.
Expand the HEADER_BLOCK block next, select the HEADER_TEXT field, and display the property sheet.
In the Contents
property, enter the name of the new report - Open Invoices by Customer.
Expand the PARAMETER_FORM block next.
Select the HEADING field. In the Contents property, enter Open Invoice by Customer.
In the Width field, enter 250.
Select the CUST_NO field and display the property sheet.
In the Display As property, select Option List.
In the Option
List of Values property, select
the CUST_NO list of values that was created in the List of Values Examples
section.
In the Contents field, the global $$CUST_NO$$ is entered automatically. This global is set on the execution of the report, and if the global exists, its value will be placed in this field the next time the report is run in the same session.
In the Width field, enter 150.
Scroll down on the property sheet and in the Target
Column, the block and field that will be restricted by the value
entered in this field on the parameter form is displayed. This
is the block and field that was selected during the block creation.
Select the RUN field and display the property sheet.
Set the Display As property to Button.
Set the Field Style property to DEFAULT_BUTTON.
In the Contents
property, enter Run Report.
Scroll down until you reach the On
Mouse Click field. Enter ExecuteReport();
Click on the Save button in the main toolbar to save the changes.
In the Explorer Tree, left click on the OPEN_INVOICES entity to select it.
Right click to display the menu. Select
Designer Frame from the menu.
The Designer Tool will be displayed containing the Parameter Form. We need to modify the report itself.
In the pop-list in the upper right of the Designer
Tool, select Run Report.
The report will be displayed.
Notice that the report title is too small to display
all of the words. Left click on the field to select
it. Then, hold your cursor over the right edge
of the field until it turns into a double headed arrow. Left
click, hold down the mouse button, and drag the edge to the right until
the field displays all the words.
Hold down the Shift key and left click on the fields containing the text Cust No and CUS in the lower part of the report. These are the customer number fields, which are also displayed in the upper block, and on which the two blocks are linked. We do not need to display them in both blocks.
Double click on the CUS field to display the Multi-Select
property sheet.
Set the Visible
property to false.
Select the field that displays the text In. This
is the Invoice Type label. In the Contents
field, enter Typ.
Click on the Save button in the main toolbar to save the changes.
Modify the Contents field for the following labels.
Labels |
Content |
Invno |
Invoice |
Invdate |
Inv Date |
Cur Balance |
Current Bal |
In order to fit all of the information in the following
fields, they need to be expanded a bit. Expand
and lay out the fields in the lower block so that they look like:
To test the new report entity, highlight it by selecting it in the Explorer Tree, and click on the Run button in the main toolbar.
Since a global variable was defined in the Filtering
property, you must define the value before the screen is run. Enter
100 in the value field and press
the Run button.
The parameter screen will be run in the browser.
Select the desired customer from the Cust
No option list and press the Run
Report button. If you leave the field set
to (not selected), the report will include all customers.
The report will be generated and displayed on the screen.
Additional modifications can be made in the Designer Tool for layout and text changes using the learned functionality.
Xephr is a registered trademark of NDS Systems, LC.
Copyright