Open topic with navigation

Viewing: Creating Domains > Example of Creating a Domain Using a Virtual Data Source

Example of Creating a Domain Using a Virtual Data Source

The following example shows how to create a Domain based on a virtual data source. The Domain designer interface is very similar to the example in Example of Creating a Domain, which covers some of these options in more detail. 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.

The example in this section uses the SugarFoodMartVDS virtual data source, which combines the Foodmart and SugarCRM JDBC data sources. Normally, you combine data sources that have relationships you want to explore. This example lets you work with SugarCRM sales and Foodmart sales by city, region, or state.

To create a useful Domain using a virtual data source, you need to define relationships that connect the tables of the different data sources. This example shows how to create a Domain that shows the sales by city for each data source. To do this, you create two derived tables, one that aggregates sales by city for the Foodmart data source and another that aggregates amount by city for the SugarCRM data source. Then you join the two data sources on city to combine them.

To create a sample Domain using a virtual data source:

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 Using a Virtual Data Source
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-Foodmart Virtual Data Source as shown below.

Select a Virtual Data Source for a New Domain

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

5. Click the Create with Domain Designer link.
6. You are prompted to select the database schemas to use. Use Ctrl-Click to select multiple schemas; these can be schemas from different databases or different schemas from the same database. The example in this section uses the schemas as they appear in the PostgreSQL database bundled in the evaluation version. Select the FoodmartDataSource_public and SugarDataSource_public schemas:

Select Schemas for a New Domain Using a Virtual Data Source

In Domains that use virtual data sources, table and schema names are prefixed by the data source alias to distinguish tables and schemas that have the same name in separate data sources. Aliases are set when the virtual data source is created.

The example in this section uses the schema names as they appear in the PostgreSQL database bundled in the evaluation version.

7. Click OK. The Domain Designer appears. The Tables tab shows the database tables in the data source.

You can create a Domain by joining the two data sources on City, Region, and Country. In this example, a Domain created that way works well for a crosstab, but not so well for a chart or table. This is because the data sources include geographic information at lower level than City. To use the data for a chart or table, you need to aggregate the city values for each data source, then join those.

8. Double-click the following tables in Data Source to add them to Selected Tables:

FoodmartDataSource_public_sales_fact_1998, FoodmartDataSource_public_store,SugarCRMDataSource_public_sales_fact
, SugarCRMDataSource_public_sales_location

Make sure Inspect new tables and automatically generate joins is checked, as shown below.

Tables Tab of the Domain Designer Using a Virtual Data Source
9. Click the Derived Tables tab.
10. Create the following derived table to aggregate store_sales for the Foodmart data source by city:
a. Enter a meaningful name in the Query ID field, in this example: FoodmartSalesByCity.
b. Type the following query to aggregate the store sales by city:

select store_city FoodCity, sum(store_sales) FoodCitySales from FoodmartDataSource_public.store join FoodmartDataSource_public.sales_fact_1998 on (FoodmartDataSource_public.sales_fact_1998.store_id = FoodmartDataSource_public.store.store_id) group by store_city

SQL queries for a derived table must be valid with respect to the JDBC driver for the data source. If you are working with a virtual data source, SQL queries are validated against Teiid SQL, which provides DML SQL-92 support with select SQL-99 features. For more information, see the Teiid documentation on the Jaspersoft Support Portal.
c. Click Run Query.
d. The fields from the query result are automatically highlighted:

FoodCity, FoodCitySales

The Derived Tables tab appears as shown in the following figure.

Derived Tables Tab of the Domain Designer Using a Virtual Data Source
e. Click Save Table. The derived table, FoodmartSalesByCity, appears in the list of available objects.
11. Create a second derived table to aggregate the amount for the Sugar data source by city:
a. Enter a meaningful name in the Query ID field, in this example: SugarAmountByCity.
b. Type a query to aggregate the amount by city. If your data source supports database schemas, use the following query:

select city SugarCity, sum(amount) SugarCityAmount from SugarCRMDataSource_public.sales_location join SugarCRMDataSource_public.sales_fact on (SugarCRMDataSource_public.sales_location.id = SugarCRMDataSource_public.sales_fact.sales_location_id) group by SugarCRMDataSource_public.sales_location.city

c. Click Run Query.
d. The following fields are highlighted in the query result:

SugarCity, SugarCityAmount

The Derived Tables tab appears as shown in the following figure.

Derived Tables Tab of the Domain Designer Using a Virtual Data Source
e. Click Save Table. The SugarAmountByCity derived table appears in the list of available objects.

You can also use the Derived Tables tab to combine two tables from different databases or schemas that have identical columns. For example, if you have a virtual data source where Table1 in DataSource1 and Table2 in DataSource2 have identical columns, you can create a derived table that combines these two tables using the following syntax:

select * from DataSource1.Table 1 UNION ALL select * from DataSource2.Table2

12. 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.
13. In the left table, expand the FoodmartSalesByCity table and select the FoodCity column.
14. In the right table, expand the SugarAmountsByCity table and select the SugarCity column.
15. Click the Inner icon to specify an inner join.

Joins Tab of the Domain Designer Using a Virtual Data Source
16. Click the Display tab. Here you can specify the way your fields will appear to users.

Display Tab of the Domain Designer Using a Virtual Data Source
17. Create a hierarchy of sets and items from the tables and columns in JoinTree_1:
a. Expand JoinTree_1.
b. Select FoodmartSalesByCity and click to add it to Sets and Items.
c. Select SugarAmountsByCity and click to add it to Sets and Items.
18. Select FoodCity under FoodMartSalesByCity in the Sets and Items panel. Click Edit in the Properties panel and change the Label to City. Click Save.
19. Click OK to finish creating this Domain. The Add New Domain page appears again.
20. Click Submit. 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. You can use this Domain to create Ad Hoc views based on this combined information.

Open topic with navigation