Example of Creating a Domain

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.

To create a sample Domain:

1. Log in to 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. In Name, enter a name for the Domain and an optional description. In this example:
     Name – Example Domain
     Description – Created in User Guide tutorial
3. In Resource ID, accept the automatically entered ID or enter an ID. No space characters are allowed.
4. In Save Location, click Browse, and browse to the Domains folder.
5. Under Data Source, click Browse. The Select Data Source dialog box appears.
6. Select Analysis Components > Analysis Data Sources > SugarCRM Data Source, and then click Select:

Select a Data Source for a New Domain

The path to the data source appears on the Add New Domain page.

7. Under Domain Design, click Create with Domain Designer.
8. 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

9. 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), tables 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.

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

11. Click the Derived Tables tab. A derived table is defined by a query and a selection of the columns in the result.
12. 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. Using Ctrl-click, select the following fields from the query result:

idcase_numberdate_entered

assigned_user_idnameaccount_id

statusdescriptionresolution

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.
13. 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.
14. In the left table, select the public_users table and click Copy to create the public_users1 table. Select the same public_users table, click Change ID, and rename the public_users table to public_users2.

Now, there are two table aliases for the users table to avoid circular joins:

Joins Tab of the Domain Designer

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

Inner

public_accounts_opportunities: opportunity_id

public_opportunities: id

Inner

public_opportunities: assigned_user_id

public_users1: id

Inner

public_accounts: id

P1cases: account_id

Left Outer

public_users2: id

P1cases: assigned_user_id

Right Outer

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

16. Click the Calculated Fields tab.

The Available Fields panel shows join trees, which 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 itself was not joined to the other tables, as shown in the figure Calculated Fields Tab of the Domain Designer.

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

18. Click Save Field to validate the expression and add the calculated field to Available Fields.
19. Click the Pre-filters tab.

The Fields and Filters panels appear.

20. 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 does not contain the value you want to use, type it 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

21. Click the Display tab. On this tab, you define how you want your fields to appear to users.

Display Tab of the Domain Designer

22. Create a hierarchy of sets and items from the tables and columns in JoinTree_1:
a. Select JoinTree_1.
b. Click to add all of the JoinTree_1 tables and columns to Sets and Items.
23. 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

24. 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 selected to be measures, but 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 or Count Distinct).

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

26. Click Submit in the Add New Domain page.

The new Domain is validated and stored in the location you specified in step 4. The new Domain appears in search results when you search the repository for it.

Version: 
Feedback
randomness