Example of Creating a Domain

Viewing: Creating Domains > Example of Creating a Domain

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:

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

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

Chose the Save Location – Browse to the Domains folder.

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

Click the Create with Domain Designer link.

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

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.

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

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:

Enter a meaningful name in the Query ID field, in this example: p1cases.

Type the following query:

select * from public.cases where cases.priority='1' and cases.deleted='0'

Click Run Query.

Ctrl-click to select the following fields from the query result:

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

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

12.

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:

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

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:

In Fields, expand the join tree and the public_opportunities table.

Double-click the opportunity_type column to create a filter condition.

Use Equals as the comparison operator, and select Existing Business from the drop-down.

Click OK to create the filter.

Create another filter by expanding the p1cases table and double-clicking the status column.

If you select the wrong column, click Cancel.

Choose the is not equal to comparison operator.

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.

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:

Select JoinTree_1.

Click

to add all of the JoinTree_1 tables and columns to Sets and Items.

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
(public_users1) Account Rep Primary account representative	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.

Open topic with navigation