The following example shows how to create a Domain. For complete information about all of the options and settings available for designing reports, see Using the Add New Domain Page and Using the Domain Designer.
1. | Log into JasperReports Server as an administrator and select Create > Domain. |
Alternatively, you can select View > Repository, then right-click a folder, such as /Domains, and select Add Resource > Domain from the context menu. The Add New Domain page appears.
Add New Domain Page |
2. | Enter a name for the Domain and an optional description. In this example: |
• | Name – Example Domain |
• | Resource ID – Accept the automatic ID or change it (no spaces). |
• | Description – Created in User Guide tutorial |
3. | Chose the Save Location – Browse to the Domains folder. |
4. | Choose the Data Source – Browse to select data source and select SugarCRM Data Source as shown below. |
Select a Data Source for a New Domain |
5. | Click the Create with Domain Designer link. |
6. | Select the schema – For data sources that allow schemas (subdivisions of a database), you are prompted to select a schema. The PostgreSQL database that is bundled in the evaluation version supports schemas, so you must select the public schema, as shown in the following figure: |
Select a Schema for a New Domain |
7. | Click OK in the Select Database Schemas dialog if necessary. The Domain Designer appears. |
The Tables tab shows the database tables in the data source. You can expand each table to see its individual columns.
In databases that support schemas (subdivisions of a database), table names are prefixed by <schemaName>_ to distinguish tables that may have the same name in separate schemas. This procedure is based on the sample data in a PostgreSQL database, and therefore it uses table names with the public_ prefix. |
8. | Double-click the following tables in Data Source to add them to Selected Tables: |
public_accounts, public_accounts_opportunities,
public_cases, public_opportunities, public_users
Because this example uses the Sugar CRM data source, ignore the check box Inspect new tables and automatically generate joins.
Tables Tab of the Domain Designer |
9. | Click the Derived Tables tab. A derived table is defined by a query and a selection of the columns in the result. |
10. | Create a derived table: |
a. | Enter a meaningful name in the Query ID field, in this example: p1cases. |
b. | Type the following query: |
select * from public.cases where cases.priority='1' and cases.deleted='0'
c. | Click Run Query. |
d. | Ctrl-click to select the following fields from the query result: |
id case_number date_entered | assigned_user_id name account_id | status description resolution |
The Derived Tables tab appears as shown in the following figure.
Derived Tables Tab of the Domain Designer |
e. | Click Save Table. The new derived table, p1cases, appears in the list of available objects. |
11. | Click the Joins tab. The tables selected on the Tables tab and defined on the Derived Tables tab appear in Left Table and Right Table. |
Joins Tab of the Domain Designer |
Now there are two table aliases for the users table to avoid circular joins:
13. | To specify a join, expand the tables to see column names, select a column in the left table and a column in the right table, then click a join icon. For this example, specify the following joins: |
Left Table and Column | Right Table and Column | Join Type |
public_accounts: id | public_accounts_opportunities: account_id | |
public_accounts_opportunities: opportunity_id | public_opportunities: id | |
public_opportunities: assigned_user_id | public_users1: id | |
public_accounts: id | P1cases: account_id | |
public_users2: id | P1cases: assigned_user_id |
Each row returned by an inner join contains data from all tables involved in the join. Outer joins return rows that satisfy the join condition plus rows from either the left or right table for which no corresponding rows exist in the other table. In this example, the result of the left outer join includes accounts without P1 cases. The result of the right outer join includes P1 cases without assigned users. |
The final list of joins appears as shown below:
Joins for Example Domain |
14. | Click the Calculated Fields tab. |
The Available Fields panel shows join trees. These are the joined tables resulting from any joins you defined, and any unjoined tables, such as the cases table in this example. The cases table was used only to help create the p1cases derived table, but was not itself joined to the other tables, as shown in “Calculated Fields Tab of the Domain Designer”. |
15. | Enter the following details for a calculated field that creates unambiguous city names: |
• | Field Name – city_and_state |
• | Type – String |
• | Expression – concat( public_accounts.billing_address_city, ', ', public_accounts.billing_address_state) |
When entering the expression, you can expand the join tree and double-click column names to insert them instead of typing them. |
Calculated Fields Tab of the Domain Designer |
16. | Click Save Field to validate the expression and add the calculated field to Available Fields. |
17. | Click the Pre-filters tab. The Fields and Filters panels appear. |
18. | Define two filters as follows: |
a. | In Fields, expand the join tree and the public_opportunities table. |
b. | Double-click the opportunity_type column to create a filter condition. |
c. | Use Equals as the comparison operator, and select Existing Business from the drop-down. |
d. | Click OK to create the filter. |
e. | Create another filter by expanding the p1cases table and double-clicking the status column. |
If you select the wrong column, click Cancel. |
f. | Choose the is not equal to comparison operator. |
g. | Enter the value closed in the search field, but do not click the search icon. |
When the selection list in the Filters pane doesn't contain the value you want, type your value in the search field. |
h. | Click OK to save the filter. The filters you defined appear in the Filters panel. |
Pre-filters Tab of the Domain Designer |
19. | Click the Display tab to specify how your fields should appear to users. |
Display Tab of the Domain Designer |
20. | Create a hierarchy of sets and items from the tables and columns in JoinTree_1: |
a. | Select JoinTree_1. |
b. | Click |
21. | In the Properties panel, rename the sets and items to give them descriptions specified in the following table. |
(Set ID) Set Label Set Description | Item ID | Item Label | Item Description |
(public_accounts) Account Customer account information | name1 | Customer | Name of customer |
account_type | Type | Account type | |
industry | Industry | Primary industry | |
annual_revenue | Revenue Size | Estimated annual revenue | |
employees | Employee Size | Estimated number of employees | |
city_and_state | City, State | City and state of headquarters | |
(public_users1) Account Rep Primary account representative | first_name | First Name | Given name |
last_name | Last Name | Surname or family name | |
(public_opportunities) Opportunity Sales opportunity | date_entered2 | Date | Date opportunity opened |
amount | Amount | Anticipated amount of the contract | |
probability | Probability | Estimated chance of winning the contract | |
description2 | Description | Description of the opportunity | |
lead_source | Source | Lead source | |
sales_stage | Stage | Sales stage | |
(p1cases) P1 Case High priority support cases | case_number | Case | Case number |
date_entered | Date | Date case opened | |
name | Summary | Name or summary of the case | |
description | Description | Detailed description of the case | |
resolution | Resolution | Description of the case resolution | |
status | Status | Current status of the case | |
(public_users2) Case Rep Support case representative or engineer | first_name1 | First Name | Given name |
last_name1 | Last Name | Surname or family name |
22. | Set the data format and summary properties for the following items: |
• | Opportunity, Date: data format of Jun 28, 2012 |
• | Opportunity, Amount: data format of ($1,235) and summary of average |
• | P1 Case, Date: data format of Jun 28, 2012 |
When used in reports, these items will have the data formats and summary functions defined here as defaults.
You can also set the Field or Measure setting on any item. By default, numeric fields are set as measures. You may need to change this setting occasionally. For example, a numeric value that you use as an identifier should be set to Field, and a textual ID that you want to use for counting should be set to Measure (and the summary function set to Count All or Count Distinct). |
23. | Click OK to finish creating this Domain. The Add New Domain page appears again. |
Under Domain Design, you can click Edit with Domain Designer to launch the Domain Designer again to edit it. |
24. | Click Submit in the Add New Domain page. The new Domain is validated and stored in the location you specified in step 3. The new Domain appears in search results when you search the repository for it. |
Recommended Comments
There are no comments to display.