Database for demonstration

This tutorial is based on the popular Microsoft Access demonstration database: Northwind. This setup installs also the Microsoft Access demonstration database Northwind.mdb.

The database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from all around the world. The management department wants to draw up reports and statistics on the product sales.

Description about datawind can be found @ http://bit.ly/datawind_description.

1. Business Domain Creation

A business domain is a set of data used to edit and execute reports. It is an abstraction layer for the database, turning raw data in Dimensions and Elements (Business Entities). It contains the following information:

Target database description (table description, report elements, report types and templates),
Users, security and public folders,
Output device definition to redirect report results to special devices.

A business domain is stored in a dedicated (*.bdo) file or directly into a database (SQLServer or Oracle only).

1.1. Business Domain Manager Wizard

On the File menu, click Business Domain Wizard (or Ctrl+W )

Select the type of the target database from the dropdown and click next.

In this case select “Microsoft access”.

Select the location of the data source and provide “username” and “password” if required to access the database and press next.

On the right of the "Select the database Access file (*.mdb)" box, click "Browse" and select the Northwind.mdb file (installed by default in the "C:\Program Files\Ariacom\Business Reports\Northwind" directory),

A warning dialog box (only for Access databases) appears for "Foreign Keys": click yes.

Select the tables in the Tables list (Order Details, Orders, Products, Suppliers) which are to be used for Report creation and then Click ">".

In the "Enter the business domain" box, type "Sample" (name of domain) and click "Finish",
An information dialog box appears to acknowledge the creation of the domain, and to propose the edition of a report on the domain. Click "No",
On the File menu, click Save and save the “sample.bdo” file on the disk.

1.1. Foreign key definition

Foreign keys define relationships between tables of your database. They consist of a simple SQL join that is applied when 2 tables are used for a query.

In the left tree view, expand the node "Sample/Metadata/Databases/Database of sample/Tables/Order Details" and select the node "Foreign Keys".
Right-click "Foreign Keys" and then select "New join".

A new join appears in the right panel. In the "Parent Table" list, select "Orders" and then click "Edit Join".

The "Edit Join" dialogue box appears.

In the "Child Column" list, select ""Order Details".OrderID",
In the "Parent Column" list, select "Orders.OrderID" and then click "OK",
Click "Check Join" to check that the join is correctly defined,

The join clause contains the condition that defines the current join.
"Order Details".OrderID=Orders.OrderID

Similarly:

Create a new join for the "Order Details" table with the parent table "Products" and the Join Clause Order Details.ProductID=Products.ProductID
Create a new join for the "Products" table with the parent table "Suppliers" and the Join Clause "Products.SupplierID=Suppliers.SupplierID"

The joins have now been defined between the Order, Order Details, Products and Suppliers tables.

1.1. Enumerated lists definition

Enumerated lists are used to present only explicit values of a report element to the user (during report edition or in report results).

Enumerated lists may be used for:

Translate database values with their corresponding display values (for example, the database value „1‟ corresponds to the display value „low‟, „2‟ corresponds to „medium‟, etc.).
To propose a list of values when editing a report restrictions for an element.
The enumerated lists can be either static, when the user enters values directly, or dynamic, when the values come from the database.

To create a new enumerated list:

· In the left tree view, expand the node "Sample/Metadata/Databases/Database of Tutorial" and select the node "Enumerated lists"

Right-click "Enumerated lists" and then select "New enumerated list". A new enumerated list appears in the right panel,
In the "Enumerated list name" box, type "Product Categories"
Check the "List is loaded dynamically from the database" box,
Check the "List refreshed upon database connection" box.
In the "SQL Statement for dynamic list" box, type the following SQL statement:
SELECT DISTINCT CategoryID, CategoryName FROM Categories ORDER BY 1 ASC

Click "Show/Edit Values" and check the display values of the product categories.
Show values window displays the DB value and display value. Click "OK".

In the left tree view, expand the node "Sample/Metadata/Databases/Database of Tutorial/Tables/Products/Elements" and select the node "Category Id".
The element detail appears in the right panel.

In the "Definition" tab, select "Product Categories" in the "Enumerated list" list,
Uncheck the "Element is nullable" box,
Select the "Format" tab. In the "Element Name box", type "Product Category

1.1. Dimensional elements creation

An element describes a column in a table and how it is displayed in the report result.

In the left tree view, expand the node "Sample/Metadata/Databases/Database of Tutorial/Tables/Orders" and select the node "Elements".
Right-click "Elements" and then select "New element". The "Select Column" dialog box appears
Check "User defined column" and then click "OK",
The "Select Report Types" dialog box appears. Check "Default Report Type" and then click "OK". The new element detail appears in the right panel

In the "Column Name" box, type:
DatePart('yyyy',[OrderDate],1,0)
This is not the name but query that return result which will be content of that column.
Click "Check Column" to check the column syntax,
In the "Element Type" list, select "Numeric",
Select the "Format" tab. In the "Element Name" box, type "Order Year".

1.1. Define and create measure elements

A measure is an element that will be often aggregated in reports (e.g. user requests the Sum of Order Amounts). The standard aggregate functions available are: Sum, Min, Max, Average and Count. Some additional functions are also available.

On the Start menu, point to Programs, Ariacom Business Reports and then click Business Domain Manager ,
On the "File" menu, click "Open" and browse to the "Tutorial.bdo" file then click "Open",
The "Domain login" dialog box appears,
In the "User Name" box, type "su" and click "OK". The business domain is open,
In the left tree view, expand the node "Tutorial/Metadata/Databases/Database of Tutorial/Tables/"Order Details"/Elements" and select the node "Quantity". The element detail appears in the right panel,
In the "Definition" tab, click "Set element as a measure",
The "%AGGRFUNC%" keyword is added to the column name and the default aggregate function is set to "Sum",

In the left tree view, expand the node "Tutorial/Metadata/Databases/Database of Tutorial/Tables/"Order Details"" and select the node "Elements",
Right-click "Elements" and then select "New element",
The "Select Column" dialog box appears,
Check "User defined column" and then click "OK",
The "Select Report Types" dialog box appears,
Check "Default Report Type" and then click "OK",
The "New Element Detail" appears in the right panel,
In the "Column Name" box, type
(1-Discount)*"Order Details".Quantity*"Order Details".UnitPrice
Click "Check Column" to check the column syntax,
In the "Element Type" list, select "Numeric",
Click "Set element as a measure",
Click "Check Column" to check the column syntax,
Select the "Format" tab. In the "Element Name" box, type "Amount",
In the "Display Alignment" list, select "Right Justified",
In the "Display Format" box, type "$ "#,##0.##

Two measures representing the Quantities and the Amounts have been defined.

1.1. Create output devices

In the left tree view, expand the node "Tutorial" and select the node "Output Devices",
Right-click "Output Devices" and then select "New Output Device",
The "Add New Output Device" dialog box appears,
In the "Device Type" list, select "Folder" then click "OK". A new output device appears in the right panel,
In the "Name" box, type "Output Folder",

If you have an Email server (SMTP), right-click "Output Devices" and then select "New Output Device",
The "Add New Output Device" dialog box appears,
In the "Device Type" list, select "Email" then click "OK". A new output device appears in the right panel,
In the "Name" box, type "EMail",
In the "SMTP Server" box, type the SMTP server name of your company,
In the "Sender Email Address" box, type a generic email address that will be used to send reports,
On the "File" menu, click "Save" (or Ctrl+S) and save the "Tutorial.bdo" file on the disk.

An output device on the folder "{Directory of Tutorial.bdo}\Output" has been defined.

2. Report Creation

Before reading further, please note that the "Report Editor" is composed by 4 panels, in order from left to right and top to bottom:

The Available Elements panel (top left)
The Selected Elements panel (top right)
The Restriction Definition panel (bottom left)
The Selected Restrictions panel (bottom right)

The Selected Elements panel itself is divided in four containers:

The Page container (top left)
The Column container (top right)
The Row container (bottom left)
The Data container (bottom right)

See the image below to locate each panel and each container.

2.1. Create a first report on sales

On the "Start" menu, point to "Programs, Ariacom Business Reports" and then click "Report Manager",
In the left tree view, expand the node "Tutorial". The "Domain Login" dialog box appears,
In the "User Name" box, type "su" and click "OK". The public folders of the business domains appear,
Select the node "Published",
Click on the "New Report" icon (top-left in the toolbar ), type "Ins",
The "Report Editor" dialog appears,
In the "Report Name" box, type "Product sales" and click "Next",
In the Available Elements panel, expand the "Orders" node, then drag the "Order Year" (or "Order Date\Order Year") element and drop it in the "Page" container of the Selected Elements panel,
In the Available Elements panel, expand the "Products" node, then drag the "Product Category" element and drop it in the Column container of the Selected Elements panel,
Right-click "Product Category" in the Selected Elements panel and then select "Sort Ascending",
In the Available Elements panel, expand the "Suppliers" node, then drag the "Country" element and drop it in the "Row" container of the Selected Elements panel,
Right-click "Country" in the Selected Elements panel and then select "Sort Ascending",
In the Available Elements panel, expand the "Order Details" node, then drag the "Amount" element and drop it in the "Data" container of the Selected Elements panel,

Click "Execute",

The report is executed and displayed per order year, the sales amount per product category and per supplier country,

2.2. Add charts to the report

Once a dimension element (Row or Column) and a measure element (Data) are selected in a report, a chart can be defined.

Steps to add charts to an existing report are listed below:

On the "Start" menu, point to "Programs, Ariacom Business Reports" and then click" Report Manager",
On the "File" menu, click on the "Tutorial:\Published\Product Sales" menu item,
The "Domain Login" dialog box appears,
In the "User Name" box, type "su" and click "OK",
The "Report Editor" dialog appears,
In "Chart Type" list, select "Pie",
Right-click "Product Category" in the Selected Elements panel and then select "Pie Axis",
Right-click "Sum of Amount" in the Selected Elements panel and then select "Pie Series",
Click "Edit Options",
The "Chart Options" dialog box appears,
In the "Legend Alignment" list, select "Top" and click "OK",
Double-click "Sum of Amount" in the Selected Elements panel ,
The Options for "Sum of Amount" dialog box appears,
Select the "Series" tab. In the "Explode biggest slice" box, type "50" and click "OK",
Click "Execute",

For each year, a pie chart is displayed,

2.3. Define restrictions for the report

Restrictions allow filtering the data used to generate the report result.

Steps to set restrictions to an existing report are listed below:

On the "Start" menu, point to "Programs, Ariacom Business Reports" and then click "Report Manager",
On the "File" menu, click on the "Tutorial:\Published\Product Sales" menu item,
The "Domain Login" dialog box appears,
In the "User Name" box, type "su" and click "OK",
The "Report Editor" dialog appears,
In the Available Elements panel, expand the "Products" node,
Click "Product Category" in the Available Elements panel,
Select "Prompt at Run-Time" in the Restrictions panel for "Product Category",
In the list, select "Beverages, Condiments, Confections" and then click ">",
The Selected Restrictions panel contains restrictions on the product categories,

Click "Execute",
The "Enter Run-Time Restrictions" dialog box appears,
Check or uncheck product categories, then click "OK", the report is executed with restrictions.

2.4. Define an output and a schedule

Once defined, a report can have several outputs to redirect the result to an output device defined in the Business Domain (e.g. email, printer, etc.). Report execution to output can be also scheduled if the Report Scheduler is installed.

Steps to define an output and a schedule to an existing report are listed below:

On the "Start" menu, point to "Programs, Ariacom Business Reports" and then click "Report Manager",
On the "File" menu, click on the "Tutorial:\Published\Product Sales" menu item,
The "Domain Login" dialog box appears,
In the "User Name" box, type "su" and click "OK",
The "Report Editor" dialog appears,
Click "Next", the Report Options screen is displayed,
Click "Next", the Report Output and Schedule screen is displayed,
In the "Output Group" box, click "New", a new report output of type folder appears,
In the "Output Name" box, type "Output",
In the "Output Device" list, select "\Published",

On the right of the "Schedule" box, click "New",
The "Edit Schedule" dialog box appears,
In the "Schedule Name" box, type "Schedule1",
In the "Schedule Type" group box, select "Regularly", information for the schedule appears,
In the "Schedule Regularly" group box, type "1" in the "Every … minutes" box,
Click "Done", the Report Editor dialog appears,
Click "Save", the report result Product Sales is displayed in the file list,
Double click on "Product sales/HTM File" to view the report result.

If the Report Scheduler is running, the report result is generated every minute from 09:00:00 to 19:00:00 in the specified output device.

This tutorial will help user in understanding and working out the basic features of Ariacom Business Reports

Ratan

Sunday, 8 April 2012

Ariacom Business Reports - Tutorial