Track 2: Creating Business Views
Business views are the data sources used for creating web reports and library components in Logi JReport Designer, as well as creating ad hoc reports and performing visual analysis in Logi JReport Server. They enable report developers and end users to build reports and analyze data based on a set of view elements that they can easily understand. For example, they do not need to worry about where the data source is from (SQL, stored procedure, view, etc.), or what the data source is (Oracle, Sybase, XML, etc.). Business views also enable IT professionals to maintain control of business data and to ensure its integrity, while presenting end users with an intuitive view of the underlying data.
Business views are created and managed in Logi JReport Designer. A business view is built from four kinds of elements:
- Group objects are typically DBFields and formulas by which you want to group data. They present the availability and key performance of data, and characteristically return text data or dates, and answer the following question: who, when, what, where and which.
- Aggregation objects are typically numeric DBFields and formulas to which an aggregate function, such as Sum, is applied or they can be an existing aggregation from the catalog.
- Detail objects can be any DBField or formula, typically they are values that you would want to display in the Detail section of a table. They can not be used in charts and crosstabs.
- Categories are simply folders for organizing the other three elements.
A business view can also contain hierarchies. A hierarchy is defined as a hierarchy category holding one group of group objects sharing a hierarchical relationship, following the order from the highest level to the lowest. Hierarchies allow end users to drill report data up and down to particular groups at runtime.
In this track, we will create a business view based on data in Data Source 1 of the JinfonetGourmetJava catalog, which provides data information about customers, orders, orders detail and products.
This track contains the following tasks:
- Task 1: Create the Business View
- Task 2: Add Elements to the Business View
- Task 3: Define Hierarchies in the Business View
Note: A Logi JReport Live license for Logi JReport Designer is required in order to perform this track. If you do not have the license, please contact your Jinfonet Software account manager to obtain it first.
In this task we will create the business view to include the needed tables.
- Select Start > Logi JReport 15 > Report Designer to start Logi JReport Designer. The Logi JReport Designer window with the Start Page appears. Close the Start Page.
- Select File > Open Catalog to bring out the Open Catalog File dialog.
- Browse to select the JinfonetGourmetJava.cat catalog file in
<install_root>\Demo\Reports\JinfonetGourmetJava, then select the Open button. The Catalog Manager is displayed.
- Expand the Data Source 1 node, then right-click the Business View node and select New Business View on the shortcut menu.
- Type in WorldWideSalesBV in the Enter Business View Name dialog and select OK.
- In the Add Tables/Views/Queries dialog, expand the JDBC connection node and then the Tables node, then select these tables: Customers, Orders, Orders Detail and Products, select to add them to the right box, then select OK.
- The four added tables are displayed with their columns and automatically joined based on the auto join criteria in the Query Editor.
- In the Customers table, check the * checkbox to select all columns in the table, then uncheck these columns: Address2, Customers_Fax, and Annual Sales. In the Orders table, select Orders_Order ID, Order Date, and Payment Received. In the Orders Detail table, select Unit Price, Quantity, and Discount. In the Products table, select all its columns first by checking *, then unselect Inventory and Reorder Level.
- Select OK at the bottom of the Query Editor.
The Business View Editor is then displayed. The tables and table columns selected in steps 6 and 8 are displayed in the Resource Objects panel in the editor, and the formulas and summaries that are valid to them are also available in the panel. We can then create view elements based on these resources.
We will create three categories in the business view to hold customer, order and product information respectively.
- In the Business View panel of the Business View Editor, select WorldWideSalesBV and right-click, then select Add Category from the shortcut menu.
- In the Category Property dialog, input Customers as the Display Name, then select OK.
- Expand the WorldWideSalesBV node in the Business View panel to display the newly added category.
- Expand the Customers table in the Resource Objects panel, select the fields Customer Name, Customers_City, Customers_Country, Customers_Region, Customers_State, and Customers_Territory in the table, then drag them into the Customers category in the Business View panel.
- Change their display names using the shortcut menu option Rename to City, Country, Region, State, and Territory, by removing "Customers_". Then select Customer Name and select twice to move it below Country to make the objects displayed in alphabetical order.
- Select the Customers category and select New Category on the toolbar to create a sub category Address in it, which will used to hold address information. Move the Address category above the City group object by using the button .
- From the Customers table in the Resource Objects panel, select Address1, Customers_Country, Customer Name, and Customers_Phone and drag them into the Address category.
When adding elements to a business view by dragging DBFields from the Resource Objects panel, the elements will be used as Group object by default. So next we will manually edit the type of the elements in the Address category to Detail so that they can be used to show detail information in reports.
- Select Customers_Country, right-click it and select Edit on the shortcut menu. In the Edit View Element dialog, edit its display name to Country and type to Detail, then select OK. Edit the display name of Customers_Phone to Phone and type to Detail using the same way.
Then select Address1 and Customer Name, right-click and select Edit to edit their type to Detail at the same time in the Edit View Element dialog.
By now the Customers category is finished. Next, we are going to create the Orders Detail category.
- Select WorldWideSalesBV in the Business View panel, then select New Category on the toolbar to create a category Orders Detail. Add these fields from the Resource Objects panel into the category: all the fields from the Orders and Orders Detail tables, and Cost from the Products table. Use the shortcut menu option Rename to edit the display name of Orders_Order ID to Order ID. Arrange the order of the added fields to make them sorted in alphabetical order. Edit the type of Cost, Discount, Order Date, Quantity and Unit Price to Detail using the Edit View Element dialog.
See the result:
View elements can also be added into a business view by using a dialog instead of dragging and dropping. Next, we are going to add more objects to the Order Detail category using a dialog.
- Select the Orders Detail category, right-click it and select Add View Element from the shortcut menu.
- In the Add View Element dialog, select next to the Mapping Name text field, in the View Element Resources dialog expand the Formulas node and double-click Total to add it as the mapping field, then set Type to Detail and select OK to create the detail object.
- Select the Orders Detail category again and select New Aggregation on the toolbar.
- In the Add View Element dialog, type Total Cost in the Display Name text field, select next to the Mapping Name text field to double-click Cost in the Products table, select Sum from the Aggregate Function drop-down list. Select OK to create the aggregation.
- Create another two aggregations Total Quantity which sums on Quantity and Total Sales which sums on the formula Total using the same way.
- Arrange the objects in the Orders Detail category in alphabetical order:
Next, we will create the Products category to hold product related information.
- Create a category Products in WorldWideSalesBV, add Category, Product Name, Product Type Name, and Products_Product ID from the Products table in the Resource Objects panel to it, modify the display names of Product Type Name to Product Type and Products_Product ID to Product ID, then arrange the objects in alphabetical order.
- Select Save on the Business View Editor toolbar to save the business view.
In this task, we will define two hierarchies in the business view: one containing customer's geographic information, from region down to city, and the other product's hierarchical groups.
- In the Business View Editor, selectWorldWideSalesBVin the Business View panel, then select NewHierarchyon the toolbar.
- In the New Hierarchy dialog, input Geography and select OK. The Geography hierarchy node is then added at the bottom under the business view root node.
- In the Business View panel, drag these objects Region, Territory, Country, State and City from the Customers category one by one following this order to the Geography hierarchy node. Region will be the highest level and City the lowest.
- Follow steps 1 to 3 to create another hierarchy named Product, then add Product Type, Category and Product Name to the hierarchy.
Now, two hierarchies have been defined in the business view.
- Save the business view to save the hierarchies, then close the Business View Editor.
- Select Save Catalog on the Catalog Manager toolbar to save the business view into the catalog.