Tutorial - Custom View

Note: Before reading this tutorial, please read Custom View Setup first.

In this tutorial we will show you how to create a custom view in ASP Report Maker. We will use the demo database for demonstration.

We will build a SELECT statement which can show the customer's company name, the product name and total price of an order item. Four tables, "Order Details", "Orders", "Customers" and "Products" table.

Fields in Table "OrderDetails".

Fields in Table "Orders".

Fields in Table "Customers".

Fields in Table "Products".

Steps to Setup Custom View

1. Loading ASP Report Maker

Open ASP Report Maker and connect to the demo.mdb database.

2. Creating the custom view

Right click [Custom Views] in database pane and select [Add Custom View], or click [Edit] in the main menu and then select [Add Custom View].

The Custom View Setup window will show up:

Build the SELECT statement as follows:

1. Drag "Customers", "Orders", "OrderDetails" and "Product" tables from table pane into builder area,
   
   
2. Create the joins, always start from the main table. In this case, the main table is "Order Details", others are just lookup tables. (We join Orders and then Customers table to lookup the CompanyName, and join the Products table to lookup the ProductName.) So we drag [Order Details].OrderID to Orders.OrderID, and [Order Details].ProductID to Products.ProductID, then drag Orders.CustomerID to Customers.CustomerID,
   
   
3. Check the required fields.

Click [SQL] tab and edit the SELECT statement in the SQL editor directly, add a calculated field in the SELECT clause:

[Order Details].UnitPrice * [Order Details].Quantity * (1 - [Order Details].Discount) AS [Extended Price]

Change the name of the custom view from the temporary name "CustomView1" to that you want, in this example, we use "Order Details Extended".

On the toolbar, there is a checkbox labelled [Copy field settings from source table (when applicable)]. We keep this checked so the Field level setup (e.g. View Tag settings) will be copied from the field's source table.

Click [OK] to finish. The custom view is added under [Custom Views] in database pane. Select the Custom View in the database pane to generate scripts for it.

Format the field as follows:

• Select the field "Unit Price", in the [View Tag] panel, select "Currency" for [Format] and then change the [No. of digits after decimal] to "2".
• Select the field "Discount", in the [View Tag] panel, select "Percent".
• Select the field "Extended Price", in the [View Tag] panel, select "Currency" for [Format] and then change the [No. of digits after decimal] to "2".

3. ASP Script Generation

Go to the [Generate] tab, click the [Generate] button and ASP Report Maker will generate the required ASP scripts automatically.

4. Running the ASP Application

Run the scripts, click the "Reports" button and select "Order Details Extended" to see the result.