Introduction

The database reporting demonstration is based on the popular Microsoft demonstration database (installed with Access and SQLserver): Northwind.

The business domain of this demonstration is part of our free version

Main demonstration topics

The Northwind Database

The Microsoft Access documents provide an exhaustive description of Northwind.

The database contains the sales data for a fictitious company called Northwind Traders, which imports and exports speciality foods from all around the world.

  • Suppliers: Suppliers' names, addresses, phone numemers and hyperlinks to home pages.
  • Products: Product names, suppliers, prices and units in stock.
  • Categories: Categories of Northwind products.
  • Orders: Customer name, order date and freight charge for each order.
  • Order Details: Details on products, quantities and prices for each order in the Orders taemle.
  • Employees: Employees' names, titles and personal information.
  • Customers: Customers' names, addresses and phone numemers.
  • Shippers: Shippers' names and phone numemers.
Northwind ERD

Northwind Company Requirements

After a fictitious analysis of the needs of the company Northwind, the following requirements have been expressed by the different departments:

Sales
  • The sales team wants to be able to consult information about their customers and orders they are managing. One member of the team cannot consult the information of another member.
  • The department would like their best customers to be able to consult their current order details and list of products via the Internet.
Buyers
  • A buyer can be assigned to one or many product categories.
  • Each buyer wants to consult information about his products and suppliers.
  • A buyer of one category cannot consult detailed information about another buyer's category.
  • The department wants to publish a summary of all products per category every day.
  • Buyers want to be notified by Email or via an automatic print-out when they have to order a product that is low in stock.
Management
This department wants to draw up reports and statistics in various areas:
  • Employees (product sales, customer sales, shipping countries, etc.)
  • Product sales (in quantity or money totals by quarter, etc.)
  • Shipping delays (country, product categories, etc.)
  • One of the members of management is French and would like to use the applications in French.
  • One of the members of management is German and would like to use the applications in German.
  • One of the members of management is Spanish and would like to use the applications in Spanish.
IT department
The IT department wants to propose a Directory via an Intranet. All employees can access information about other Northwind employees: LastName, FirstName, Title, Extension.

From these needs, we have defined and configured the Northwind domain.

The Northwind Domain

The domain describes all the tables of the Northwind database.

The following report types have been defined:

Orders
Contains all elements necessary to query information on orders and order details.
Products
Contains all elements necessary to query information on company-stored products.
Statistics
Used to perform general statistics for different subjects like: Orders, Customers, Products, Employees.
Employees
Used to implement the directory.

The following public folders have been defined:

Published
For all published reports like reports for customers and demonstrations or directory consultation.
Order reports
For storing default reports on 'Orders'
Product reports
For storing default reports on 'Products'
Statistics
For storing default reports on 'Statistics'
Common share
A folder used to transfer or publish reports between users.

The following users have been defined (no passwords have been set):

davolio
  • The sales person 'Davolio'.
  • He has a personal folder in which he can define and execute 'Order ' type reports.
  • He has an Execute right on 'Order reports' and 'Published' folders and full rights on 'Common share'.
  • A user restriction has been set so that he can only query the orders he is managing.
peacock
  • The sales person 'Peacock' is also a buyer for the 'Beverages' and 'SeaFood' categories.
  • He has a personal folder in which he can define and execute 'Orders' or 'Products' type reports.
  • He has an Execute right on 'Order reports', 'Product reports' and 'Published' folders and full rights on 'Common share'.
  • A user restriction has been set so that he can only query orders he is managing and 'Beverages' and 'SeaFood' products.
fuller
  • The director.
  • He has all rights on all report types and public folders.
dupont
  • The French member of the management team.
  • He has visibility over all report types, but only has execute rights on public folders (except 'Common share').
  • His regional locale parameter has been set to 'French (France)'.
schneider
  • The German member of the management team.
  • He has visibility over all report types, but only has execute rights on public folders (except 'Common share').
  • His regional locale parameter has been set to 'German (Germany)'.
rodriguez
  • The Spanish member of the management team.
  • He has visibility over all report types, but only has execute rights on public folders (except 'Common share').
  • His regional locale parameter has been set to 'Spanish (International)'.
directory
The login used to execute a directory search.
lamaisondasie
  • The French customer 'La maison d'Asie'.
  • A dedicated login Web page has been set for this user.
  • The company's regional locale parameter has been set to 'French (France)'.
  • It has an Execute right on 'Orders' and 'Products' type reports.
  • It has an Execute right on 'Published' folders.
  • A user restriction has been set so that it can only query its own orders.

Special remarks

  • To set up the outer join in the employees table (to select the manager), a view is always created in the database Pre SQL statement and dropped in the Post SQL statement.
    Note that from the 2.4 version the use of outer join is directly supported (in this case 2 tables aliased on the Employees table joined together).
  • The security requirements have been implemented by creating dedicated elements in the domain:
    Last Name in the EmployeeView table (the element is hidden).
    Category Name and Product Name in the Products table.

Features Demonstrated

The reports defined illustrate some of the features of this database reporting software. However, other requirements and databases would have shown other possibilities.

Here is a non exhaustive list of features demonstrated along with the corresponding reports.

Drill navigation
Login with 'fuller' and view the report 'Statistics/on Products/Product sales'
Mixing line, bar and point charts
Login with 'fuller' and view the report 'Order reports/Orders amount total and per country'
List of values
Login with 'fuller' and view the report 'Product reports/Suppliers list'
Result publishing
Login with 'fuller' and view the result files:
  • 'Published/Products list HTML'
  • 'Published/Products list CSV'
  • 'Published/Products list TEXT'
  • 'Published/Products list XML'
  • 'Published/Products list PDF'
Parameters
Login with 'fuller' and view the report 'Orders/Orders search'
Report output, Report schedule
  • Login with 'fuller' and view the report 'Published/Products list'
  • Login with 'peacock' and view 'Personal (peacock)/Products to reorder'
Cascading reports
Login with 'fuller' and view the report 'Product reports/Suppliers list'
Multi-language support
  • Login with the french user 'dupont'
  • Login with the german user 'schneider'
  • Login with the spanish user 'rodriguez'
Custom web pages at login
Login with the user 'lamaisondasie'

All these samples are part of our free version.