Track 2: Creating Business Views
Business views are the data sources used for creating interactive reports and perform dynamic analysis in Logi JReport. 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.
In this track, you will create a business view as a report developer using the data in the Jinfonet Gourmet Java company's database, which provides 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 Nusiness 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 Logi Analytics account manager to obtain it first.
In this task we will create the business view to include the needed tables.
- Select JReport Designer in the JReport folder on the Start menu 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, select these tables while holding the Ctrl key on the keyboard: Customers, Orders, Orders Detail and Products, then select to add them to the right box. select OK to close the dialog.
Logi JReport Designer allows you to combine tables, views, synonyms, queries, imported SQLs, stored procedures and user defined data sources into a business view to mash up multiple data resources in a single business view, and create distributed joins to set up inter-relationships between the data resources. In this example we only use tables for the business view.
- The four added tables are displayed with their columns and automatically joined based on the auto join criteria in the Query Editor (you can view the join options from the Query > Auto Join menu).
- 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 to finish selecting data resources for the business view.
The Business View Editor is displayed. The selected tables and table columns 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.
- Select New Category on the Business View Editor toolbar.
- 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 the display names of the added objects 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, right-click it and select New Category from the shortcut menu to create a sub category Address in it, which will be 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. 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 the four elements in the Address category, right-click them and select Edit on the shortcut menu. In the Edit View Element dialog, select Detail from the Type drop-down list and select OK. Via the dialog you can edit the Type, Tip and Description properties of multiple view elements at the same time.
- Right-click Customers_Country and select Rename from the shortcut menu to change its display name to Country. Rename Customers_Phone to Phone in the same way.
The Address category now contains four detail objects as follows:
By now the Customers category with the Address sub 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 the following 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. Rename Orders_Order ID to Order ID. Arrange the order of the added objects 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 dialog in addition to dragging and dropping. Next, we are going to add more elements to the Orders Detail category via dialog.
- Select the Orders Detail category and select New Detail on the toolbar.
- In the New View Element dialog, select next to the Mapping Name text box. In the View Element Resources dialog expand the Formulas node and double-click Total to add it as the mapping field. select OK to create the element.
- Select the Orders Detail category again, right-click it and select New View Element from the shortcut menu.
- In the New View Element dialog, type Total Cost in the Display Name text box, select next to the Mapping Name text box and select Cost in the Products table as the mapping field, select Aggregation from the Type drop-down list, then 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 elements in the Orders Detail category in alphabetical order:
Next, we will create the Products category to hold product related information.
- Select WorldWideSalesBV in the Business View panel and select New Category on the toolbar to create a category Products, drag 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 elements 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 group objects Region, Territory, Country, State and City from the Customers category one by one following this order to the Geography hierarchy node. Region is the highest level and City the lowest.
- Follow steps 1 to 3 to create another hierarchy named Product and add Product Type, Category and Product Name in the Products category 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.