Report Entity Example - Master/Detail Report with Multiple Datasources

In this example, we will create a master/detail report displaying customer cost versus revenue by period for two different datasources.  

First, we must create another datasource from which to pull the data.

Adding Datasources

  1. In the Explorer Tree, select the Datasource Information object.

  2. Right click on the Datasource Information object and select New->Datasource.

  1. The Create New Datasource wizard will be displayed.

  2. In the Name property, enter the name of the datasource.  For this example, enter DEMO as the name of the datasource.

  3. In the Type property, select Oracle 9i as the database type.

  4. In the Login property, enter the log in used to access this database.  For this example, enter fusion.

  5. In the Password property, enter the password used to access this database.

  6. Press the Ok button to create the datasource.

 

  1. The datasource is created, but needs some modifications before it will retrieve data.

  2. In the Description property, you can enter a user-defined description of the datasource.  For this example, enter Xephr Demo database.
     

  3. In the Server property, the name of the server on which the database is installed needs to be entered.  The fusion database is installed on the server named dev9, so for this example, enter dev9.

  4. In the SID property, enter the service identifier (SID) for the database.  The service identifier for the fusion database is dev9.

  5. In the Table Query property, enter the SQL statement used to populate the list of tables available when creating a new block.  Since all of our blocks need to be generated from views, and the objects in the oracle database are stored in the user_objects table, enter the following statement:

    select object_name from user_objects where object_type = 'VIEW' order by object_name

  6. In the Initial Connections property, enter the number of database connections with to start with in the connection pool when the system is started. Users share the connections created in the connection pool, and this is the number of connections created when the first connection is made.  For this example, we will leave the setting to its default of 2 initial database connections.  For more information on the database connection pool, see the Database Connection Pool section.

  7. In the Max Size property, enter the maximum number of connections that can be created in the connection pool.  This is the total number of connections needed during peak system usage.  For this example, we will leave the setting to its default of a maximum of 10 connections. For more information on the database connection pool, see the Database Connection Pool section.

  8. In the Min Size property, enter the minimum number of connections that can exist in the connection pool.  This is the lowest number of connections that can exist when the connection pool is shrunken during low usage periods.  For this example, we will leave the setting to its default of a minimum of 0 connections. For more information on the database connection pool, see the Database Connection Pool section.

  9. Press the Save button in the main toolbar to save your new datasource.

Creating the Report

The next step is to create the new report, now that the second datasource has been created.

  1. Select the Group Information object.  Expand it, and select the group to which you wish to add the new report entity.

  2. Right click on the group and select New->Wizard->Entity.

  3. Click on the Report bullet.

  4. Click on the Next button at the bottom right of the screen.

  5. In the Entity Name property, type CUST_HIST_BY_PERIOD as the entity name.

  6. Leave the Datasource property set to (use default) since we are going to define the datasource used by block.

  7. In the Based On property, select REPORT_TEMPLATE, which is the template that we created earlier.

  8. Set the Default Mode property to Run Report, since we are not going to use the parameter form for this report.   

  9. Leave the Body Style set to (from parent).

  10. Leave the Field Style set to (from parent).

  11. Leave the Link Style set to (from parent).

  12. Leave the Paper Size set to US_LETTER.

  13. Leave the Document Security set to Not Secure.

  14. Click on the Next button at the bottom right of the screen.

  15. In the Block Name property, enter MASTER as the name of the block.

  16. Leave the Block Type property set to Standard Block.

  17. Leave the Datasource property set to (use default).

  18. Set the Query Type property to Complex Query.

  19. Leave the Create Heading checkbox set to off since we do not want a heading to be created for the block.\

  20. In the Scheme Selection section, set the Form bullet to on since we want the labels and database fields displayed in the same block.

  21. Click on the Next button at the bottom right of the screen.

  22. The Setting Complex Query properties screen is displayed.

  23. When the Query Type is Complex Query for any block, you must enter the select statement, filters, and ordering and grouping information manually, defining the columns to be selected and the table from which the columns will be selected.  The query we are entering will select the year and period and join them into one value that will be displayed.

  24. In the Select Statement property, enter

    select to_char(ceil(a.nds_row/12)+to_number(to_char(sysdate,'YYYY')-2))       ||ltrim(to_char(mod(a.nds_row-1,12)+1,'00')) year_month from nds_counter a

  25. In the Filters property, to have only periods from 2003 displayed, enter

    where a.nds_row < 16

  26. In the Ordering and Grouping property, enter

    order by year_month

  27. Click on the Next button on the bottom right of the screen to continue.

  28. The Adding Fields screen is displayed, including the columns defined in the complex query.

  29. Set the Key, Field, and Column checkboxes to on for the YEAR_MONTH column.

  30. Click on the Next button on the bottom right of the screen to continue.

  31. The Ordering Fields screen is displayed next.  We only have one column in this query.  

  32. Click on the Next button on the bottom right of the screen to continue.

  33. The Option to Add More screen is displayed next.  Next we need to add the first block that will display customer sales and cost information for the PRODUCTION datasource by period.  Set the Add Block or Labels checkbox to on.  The entity in its current form will be displayed.   

  34. Expand the CUST_HIST_BY_PERIOD entity and left click on the MASTER block to select it.  

  35. Click on the Add Block button at the bottom of the screen.

  36. In the Block Name property, enter PRODUCTION_CUST_HIST as the name of the block.

  37. In the Block Type property, select Standard Block.

  38. Set the Datasource property to PRODUCTION, because we want the data for this block to be taken from that datasource.

  39. Set the Query Type property to Simple Query.

  40. 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 CUST_HIST_VW2_SUM.

  41. Leave the Create Heading checkbox set to on as we want a heading label to be generated for this block.

  42. In the Scheme Selection section, set the List bullet to on as we are displaying multiple records and the labels and fields need to be displayed in different blocks.

  43. Click on the Next button at the bottom of the screen.

  44. The Adding Fields screen is displayed.  Select the columns that you wish to include in the report.

  45. Click on the name of the column to select both the Field and Label checkboxes for that column.  Do this for the following columns
    CUST_NO
    SLS_COST
    SLS_REV
    YEAR_MONTH

  46. A key must be selected to continue.  Set the Key checkbox to on for the YEAR_MONTH field as we wish to join this block to the MASTER block by that value.

  47. In the Filters property, you could enter code to restrict the data returned from the table. We do not need any additional restrictions, so leave this property empty.

  48. 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 to display the data in order by customer number.  Enter the following:

    order by cust_no

  49. Click on the Next button at the bottom right of the screen.

  50. 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.  These columns are in the proper order.

  51. Click on the Next button when you have finished moving the fields.

  52. The Option to Add More screen is displayed next.  Next we need to add the first block that will display customer sales and cost information for the PRODUCTION datasource by period.  Set the Add Block or Labels checkbox to on.  The entity in its current form will be displayed.   

  53. Expand the CUST_HIST_BY_PERIOD entity and left click on the MASTER block to select it.  

  54. Click on the Add Block button at the bottom right of the screen.

  55. In the Block Name property, enter DEMO_CUST_HIST as the name of the block.

  56. In the Block Type property, select Standard Block.

  57. Set the Datasource property to DEMO, because we want the data for this block to be taken from that datasource.

  58. Set the Query Type property to Simple Query.

  59. 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 CUST_HIST_VW2_SUM.

  60. Leave the Create Heading checkbox set to on as we want a heading label to be generated for this block.

  61. In the Scheme Selection section, set the List bullet to on as we are displaying multiple records and the labels and fields need to be displayed in different blocks.

  62. Click on the Next button at the bottom of the screen.

  63. The Adding Fields screen is displayed.  Select the columns that you wish to include in the report.

  64. Click on the name of the column to select both the Field and Label checkboxes for that column.  Do this for the following columns
    CUST_NO
    SLS_COST
    SLS_REV
    YEAR_MONTH

  65. A key must be selected to continue.  Set the Key checkbox to on for the YEAR_MONTH field as we wish to join this block to the MASTER block by that value.

  66. In the Filters property, you could enter code to restrict the data returned from the table. We do not need any additional restrictions, so leave this property empty.

  67. 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 to display the data in order by customer number.  Enter the following:

    order by cust_no

  68. Click on the Next button at the bottom right of the screen.

  69. 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.  These columns are in the proper order.

  70. Click on the Next button when you have finished moving the fields.

  71. The new entity is added to the initially selected group.

  72. Click on the Save button in the main toolbar to save the new report entity.

  73. In the Explorer Tree, left click on the CUST_HIST_BY_PERIOD report entity and display the property sheet.

  74. In the Title property, enter the title for this report - Customer History by Period

  75. All other settings come from the report template.  

  76. Expand the HEADER_BLOCK block next, select the HEADER_TEXT field, and display the property sheet.

  77. In the Contents property, enter the name of the new report - Customer History by Period.

  78. We do not need a parameter form for this report, so we are going to delete it.

  79. Left click on the PARAMETER_FORM block in the Explorer Tree.  Then, click on the Delete Record button in the main toolbar to delete it.

  80. The Confirm Delete pop-screen will be displayed.  Click Yes.

  81. The Delete Results pop-screen will be displayed.  Click Ok.

  82. Click on the Save button in the main toolbar to save the changes.

  83. Now, right click on the CUST_HIST_BY PERIOD entity and right click.  Select Show Designer Frame from the menu.

  84. Within the Designer Tool, you can move, align, and resize the objects.  For specific instructions on the Designer Tool and its functionality, see the Designer Tool section.

  85. When the Designer Tool is displayed, expand it so that you can see the entire entity.  The Designer Tool displays the default mode of the entity selected.  The pop-list in the upper right of the Designer Tool indicates that we are viewing the lay out of Run Report.

  86. The modifications that we need to make include entering header text for both blocks, assigning format masks to the cost and revenue fields, and hiding the year_month column in the detail blocks, since it's already displayed in the header blocks.

  87. Left click on the field that reads PRODUCTION_CUST_H...  This is the header field.

  88. Double click to display the property sheet.

  89. In the Contents field, enter Customer History for PRODUCTION.

  90. Left click on the field that reads DEMO_CUST_HIST_HE...  This is the header field.

  91. Double click to display the property sheet.

  92. In the Contents field, enter Customer History for DEMO.

  93. These fields are displayed a little small.  In the Designer Tool, drag the right edge to make them larger.

  94. The SLS_COST and SLS_REV fields for both blocks need to be assigned format masks. Hold down the Shift key on the keyboard and select them.  Double click to display the property sheet.

  95. In the Format Mask property, select $#,###,##0.00

  96. In the Designer Tool, hold down the Shift key on the keyboard and select the two fields that read Year and the two fields that read YEA.  

  97. Display the property sheet.

  98. Set the Visible property to false.

  99. Click on the Save button in the main toolbar to save the changes.

  100. 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.

  101. Click on the Run button.

  102. The report will be displayed in the browser.

  103. Additional formatting changes can be made to the layout so that the final version looks like this: