Report Wizard Tutorial

1. Creating a Basic Report

The fastest and easiest way to create a report in CAPITAL Visual Builder is to use the Report Wizard. That is, if the report doesn't already exist. CAPITAL Office comes equipped with hundreds of reports with thousands of variations. Check the Reference Guide and the CAPITAL report menus to check if such a report has already been created. If an appropriate report can't be found, next check the Visual Builder Forms library. The forms library has many additional predefined reports that may suite your requirements.

If you've looked carefully and still can't find the report you want, or would like to create a report similar to one that already exists but isn't quite right, the next best option is to use the Reports Wizard.

To start the Report Wizard click on the Wizard box when Visual Builder starts. You will be presented with a set of Wizards. Select the Report Wizard by clicking on it with the mouse.

The Report Wizard begins by presenting you with a series of report "templates". A template is a pre-defined set of instructions on how to create a report. The important thing to do at this point is to select the option that most closely matches the type of report you wish to create. Knowing which template to choose from is half the battle.

Back Orders Pending

If you wish to create a report based on the stock items currently held on back order, this is your best choice.

Bank Accounts

Select this option to print information about your bank accounts, such as the bank name or bank balance. If you wanted to know how much money was in your bank accounts this selection would provide a good basis for such a report. Don't select this option if you want to create a report on the individual transactions that belong to each bank account. See the options that follow.

Cash Book Transactions

Select this option to create a report that would list the transactions assigned to one or more bank accounts. This would be useful if you wanted to design your own cash book transaction list report or perhaps a bank deposit list. Another useful report might be one that only listed transactions of a certain transaction type or payment method (i.e., all transactions paid using a certain credit card).

Commission Records (not sales)

Select this to create a report that presents commission record details, such as the salesperson's name and personal details. Don't pick this option if you want to create a sales commission report. See below for more appropriate suggestions.

Customer Records

As the title suggests, select this option to create any type of report that involves producing a list of customers. A customer list report can be useful for checking customer details. Another use might be to create a report for salespeople by listing customers found in certain territories. This template isn't suitable for creating aged trial balances/account balances reports or reports that involve amounts owed to you. (Although you can display the total for all periods owed and the total sales to date if you insist.)

Better options for those types of reports include using the Customer Statement selection in the Forms Wizard or the Customer Transactions template in the Report Wizard.

Customer Transactions

Customer transactions are those entries that make up the invoices, adjustments, payments and credits found under each customer account. This template is useful when you want to produce a history report showing all transaction activity with a particular customer, or perhaps more specialised reports, such as all payments issued to certain customers.

Deposits & Lay-By Sales

Use this template to create a list of all lay-by transactions and customer deposits.

Invoice Sales

The Invoice Sales template will allow you to create reports that list whole transaction information, without stock details. (You can't list individual sale items using this template. See the Product Sales Analysis template instead for that.) A report that listed the date, invoice number, reference, total sales amount and freight amount could be created using this selection.

Its also useful for creating commission reports where sales commission depends on total sales rather than sales for individual products. This template will also allow you to create a report that lists the tax related to each transaction. But once again, only in the form of a total. Since tax rates are assigned to stock items, the Product Sales Analysis template would be more suitable.

Hired Stock

Use this to create reports that show what stock is on hire or has been on hire.

Hire Job Card Details

Allows you to create reports based on, as the name suggests, hire job details. These include the hire job number, reference information, account information, address details, balances owing and so forth. If you need to report information on the individual stock items that were, are, or have been hired, use the Hired Stock template instead. You should also consider looking at the Form Wizard which allows you to create hire job cards that combine both hire job card details and hire stock information.

Periodic Billing Items

Periodic bill items are items that you bill over and over again. Usually they represent services rather than actual goods, but any stock item that can be used up or expires is also a good candidate. Periodic billing in CAPITAL is handled through the Service Manager. This template can help you create a report that lists service manager stock that is marked for repeat or periodic sales.

Price List

As the name suggests use this template when you want to create price lists. Price lists essentially list the contents of the stock file, but are sensitive to other factors as well, such as whether an item is marked in stock control for exclusion from a price list.

Purchase Orders

This template will let you list all details relating to purchase orders, except for the actual stock on order. (See the option below for that.) These include such things as the purchase order date, account code, supplier name, on order (purchase) total, and status of the order.

Purchase Order Stock Pending

Select this option to produce a list of stock items assigned to purchase orders. This might be for creating a report showing all undelivered stock items, or partially delivered items, or perhaps the cost of stock not yet delivered. If you want to create a report about stock that has been delivered, i.e., the total value of all stock added to the system between two dates, use the Stock Delivered template selection instead.

Product Sales Analysis

This is a very popular selection. Use it when you need to report on detailed stock information relating to customer sales. This is useful when you need to report on total sales for a particular stock item or group of items, as well as costs, tax rates and amounts for those items.

Quotations

This template will let you list all details relating to quotations, except for the actual stock items quoted. (See below for that.) These include such things as the quotation date, account code, prospect name, address, other cross-references, the total dollar value of the quote and estimated cost.

Quotation Stock

Select this option to produce a report focusing on stock items assigned to quotations.

Requisitions

This template will let you list all details relating to requisitions, except for the actual stock items associated with them. (See the option below for that.) These include such things as the requisition date, department code, authorisation reference, the total dollar value and estimated cost.

Requisition Stock

Select this option to produce a report focusing on stock items assigned to stock requisitions.

Sales Orders

This selection will let you list all details relating to sales orders, except for the actual stock items ordered. (See the option below for that.) These include such things as the order date, account code, order number, customer reference, the total dollar value and estimated cost.

Sales Order Stock

Select this option to produce a report focusing on stock items assigned to sales orders.

Service Job Cards

Select this option to list reference information relating to job cards. Supplier expenses, labour and stock information are only listed on this report in summary/totals form.

Service Job Card Stock

This template will allow you to create a report producing detailed information on all stock/parts/materials relating to service manager job cards.

Service Job Card Labour

This template will allow you to create a report producing detailed information on all time sheet entries associated with service manager job cards.

Service Job Card Expenses

This template will allow you to create a report that produces detailed information on all direct expenses (supplier invoices) associated with service manager job cards.

Stock

Choose this selection to create a stock report.

Stock Delivered

This report shows purchase history information from each of your suppliers. It can be used to produce detailed information on transaction numbers, order numbers, account codes, goods received dates, quantities, costs, products receipted, type of delivery and so on. Choose this template when you want to produce a report about goods arriving into your business, regardless of whether they were delivered on purchase orders or via stock receipts.

Stock Groups

This selection produces a report listing information about stock groups.

Stock Notes

This selection is used to create a stock report that focuses on stock note information.

Stock Returns

This selection will let you list all details relating to stock returns, except for the actual stock items ordered. (See the option below for that.) These include such things as the return date, account code, return number, reference, the total dollar value and value of the returned stock.

Stock Return Stock

Select this option to produce a report focusing on stock items assigned to stock returns.

Stock Receipts

This template will let you list all details relating to stock receipts, except for the actual stock being receipted. (See the option below for that.) These include such things as the receipt date, account code, supplier name and receipted total.

Stock Receipt Stock

Select this option to produce a report focusing on all stock items assigned to stock receipts.

Supplier Records

As the title suggests, select this option to create any type of report that involves listing supplier details. A supplier list report can be useful for checking supplier details. This template isn't suitable for creating aged trial balances/account balances reports or reports that involve amounts owing. (Although you can display the total for all periods owed and the total purchases to date if you insist.) Better options for those types of reports include using the Supplier Statement selection in the Forms Wizard or the Supplier Transactions template in the Report Wizard.

Supplier Transactions

Supplier transactions are those entries that make up the invoices, adjustments, payments and credits found under each supplier account. This template is useful when you want to produce a history report showing all transaction activity with a particular supplier, or perhaps more specialised reports, such as all payments issued to that supplier. (For stock related reporting see the Stock Delivered template.)

Other Types of Reports

If the report you want to create is not listed on the Report Wizard's list of selections you'll still be able to create that report, but you won't be able to utilise the Report Wizard to help you. Begin learning about Visual Builder by working with the Report Wizard. Study the tutorial that follows; it covers key subjects you need to become conversant with in order to create reports successfully.

2. Selecting the Fields to Include in the Report

Once you have decided which Report Wizard template is most suitable for the type of report you wish to create, you should highlight it and press the Next> button. In this tutorial we will be building a sales report, so do the following:

1. Select Invoice Sales by clicking on it with your mouse.

2. Click on the Next> button to proceed to the next window.

If you make a mistake, click on the <Back button and correct your selection.

We will be including the following information on our report:

This type of report is going to run more slowly on a very large database--because every record in the database has to be checked no matter how we sort it--and there is little we can do to improve that situation. (Short of running the report on a faster computer!)

Ensure that the entry:

DTOS(Date) + Invoiceno

is selected and click on the Finished button. Visual Builder may take a few moments, then our basic report will be created.

4. Working in the Report Editor

The first thing we want to do is see if our basic report works! Visual Builder will have transported you to the Forms Editor. Something very close to the following should be appearing on your screen:

generate/repwiz21.gif

Let's see what this report looks like. Move the mouse over the print preview button generate/preview1.gif and click on it. After a few seconds our report will be displayed in the print preview window.

Notice that the Wizard has formatted a lot of the basic elements of the report already. Each column has a heading and the invoice total will have a grand total at the bottom when the report comes to an end.

If you wish to print a hard copy of the report, click on the print button. generate/print1.gif When you are finished previewing the report click on the Close button or press ALT-C to return to the editor window.

The form editor window is where all the "action" involving changes to your reports take place. The other sections of this manual describe the operation of the editor in great detail. For the sake of this tutorial a basic introduction will serve our purposes for the time being.

Everything appearing in the editor window is what Visual Builders calls an "object". An object can be a picture, a text message, a field, a column of a report, a bar code, etc. You can manipulate these objects by pointing to them with the mouse. Clicking on an object will select it and a series of dashes plus a set of blue boxes (called handles) will appear around the object. You can then manipulate the selected object in various ways:

1. You can move the object around by holding down the left mouse button and dragging it with the mouse. When you want to "drop" the object into its new position just let go of the left mouse button.

2. You can edit the properties of an object. Do this by double-clicking (clicking the mouse button twice quickly).

What kind of properties does an object have? That depends on the nature of the object. A text message object has these properties:

That's a lot of properties just for a text message! Fortunately, for the vast majority of the time, we seldom have to worry about most of these things. All we want to do at this point is change the text message that represents the name that appears at the top of the report. We want to change the message from "Enter the report title here..." to "Sales Report". To do this do the following:

1. Move the mouse cursor over the text message "Enter the report title here..." and click on the left mouse button.

2. A series of blue dashed lines will appear around the object to indicate that it has been selected. If nothing happens try repositioning the mouse closer to the message and click on the left mouse button again.

3. Press the right-mouse button. A pop-up menu will appear.

4. Select Properties... from the pop-up menu.

5. A Text Object Properties Dialog Box will appear. Change the message by typing: Sales Report in the Text box. You can remove the old/original message by pressing the Del or Delete key on your keyboard.

6. Click on the OK button.

7. The report heading will no longer be aligned at the center of the report. To align it select Align from the pull-down menu.

8. Select Center from the Align menu. (Make sure that the text message is still highlighted when you do this.)

We are now ready to select the printer which will print our report.

5. Selecting Your Printer

If you have only one printer installed on your system then this will become the default printer that the report will use. Otherwise we will need to select which printer to send the report to. Do the following.

1. Select Form Properties by clicking on the generate/setting2.gif button.

2. If the printer selected is not the printer you wish to use, click on the down arrow button next to the Print To list box. Then select the printer you wish to use.

At this point also take note of the Title of the report. It should say "Invoice Sales Report".

The Title of the report is different from the report heading. The report heading is what will appear at the top of the report when you print on screen or to paper. The Title of the report is the name of the report that will appear on the report menu when you run the report.

6.Prompting Users to Answer Questions

We want our sales report to only report on a range of dates. The range of dates will vary depending on when the report is run. We therefore need to be able to query the user running the report and ask for the date information we need. In this case, this information happens to be the reporting start date and the reporting end date.

Therefore, we have two pieces of information we require. We can obtain this information by adding "questions" to the report. From Form Properties do the following:

1. Click on the Questions tab button.

2. Click on Wizard...

3. The Question Wizard will prompt you to tell it what type of question you are asking. In this case we are asking the user for a date. So click on the A Date radio button.

4. Now click on the edit box below the message "Enter the question you will prompt users for". Type in the following: Enter the start date of the report

5. Click on the Finished button.

We are now going to create the next question:

1. Repeat steps 2-3 as above.

2. Now click on the edit box below the message "Enter the question you will prompt users for". Type in the following: Enter the end date of the report

3. Click on the Finished button.

4. Click on the OK button to save what we have done so far.

If what we've done is successful, the report should prompt us to enter a start and end date when we run it. To test this click on the generate/preview1.gif print preview button.

You should now be prompted to enter the start and end date. Click on Cancel or press the ESC key to return to the form editor window.

7. Establishing Filters or Rules

A report is often only truly useful if it summaries or only shows us the information we actually want to see. We can define "rules" in our report to exclude irrelevant information, such as sales outside the date range we want to report on. A rule is also sometimes called a "filter" because unwanted data is filtered out.

Let's add a rule so that only the sales data between the two dates specified by the user will be shown on the report:

1. We first need to tell Visual Builder where the rule applies. Do this by clicking on the "report body". The report body is the set of columns with the red dashed line underneath it. Select this object by pointing to it with the mouse and clicking on the left mouse button.

2. Press the right-mouse button to activate the pop-up menu.

3. Click on Body Properties...

4. Click on the Rules tab button.

5. Click on the Wizard... button.

You are now in the Rules Wizard dialog box. We use this dialog to create a rule that tells the report what data to exclude. Previously, we created two questions using the Questions Wizard. Each time a question is created using the Questions Wizard, a "variable" is automatically created that is used to "hold" the contents of the user's input. The first question is called Answer1, the second question Answer2, and so on. So far we have instructed Visual Builder to set-up the following relationships:

Answer1 = user input - start date of report

Answer2 = user input - end date of report

All you need to keep in mind at this point is that the "variable" or label called Answer1 is the short-hand code for the start of the report and Answer2 is the short-hand code for the end date.

We now want to create the following rule:

Only include a transaction on the report if the date of the transaction is greater than or equal to the start date of the report (Answer1) and if the date of the transaction is less than or equal to the end date of the report (Answer2).

This is really two questions:

Only include the transaction if it is equal to or greater than start date (Answer1)

Only include the transaction if it is equal to or less than end date (Answer2)

We'll now use the Rule Wizard to create these two rules:

1. Under the heading "Enter the field or variable to compare" click on the down arrow button and select Invoice->Date from the drop-down list. (Invoice->Date is the date of the transaction stored in the database.)

2. Click on the down arrow button below this and change "Is equal to" to "Is greater than or equal to".

3. Under the heading "Compare the above field or variable to" click on the down arrow button and select Answer1 from the drop-down list.

4. Click on Finished to create the rule.

The procedure for creating the second rule is very similar. Do the following:

1. Click on the Wizard... button.

2. Under the heading "Enter the field or variable to compare" click on the down arrow button and select Invoice->Date from the drop-down list. (Invoice->Date is the date of the transaction stored in the database.)

3. Click on the down arrow button below this and change "Is equal to" to "Is less than or equal to".

4. Under the heading "Compare the above field or variable to" click on the down arrow button and select Answer2 from the drop-down list.

5. Click on Finished to create the rule.

6. Click on OK to save the two rules we have created.

8. Some Finishing Touches

Changing Column Positions

If we later decide that we need to change the arrangement of the columns on the report it is easy to do so. Let's say we want to swap the positions of the Invoice->Acccode and Invoice->Date columns so that the date appears before the account code. Do the following:

1. Click on the "report body". The report body is the set of columns with the red dashed line underneath it. Select this object by pointing to it with the mouse and clicking on the left mouse button.

2. Press the right-mouse button to activate the pop-up menu.

3. Click on Body Properties...

4. Click on the field Invoice->Date entry found on the Body Field List

5. Press the up arrow button generate/up1.gif below the Body Field List.

6. Click on OK.

Undoing Mistakes

Visual Builder remembers the last set of things you've done and allows you to undo them if you've made a mistake and would like to reverse your prior action. To reverse the last thing you've done do the following:

1. Select Edit from the editor menu.

2. Select Undo.

If you decide that you want to the previous change back, do the following to undo your undo:

1. Select Edit from the editor menu.

2. Select Redo.

Speeding Up the Report

As mentioned in the section of this tutorial that discussed sorting, we can make our reports run faster if the way in which the report is sorted also happens to be the way we determine what goes on the report.

In this case we are sorting by the date and we have added two rules that also deal specifically with the date. This gives us the opportunity to instruct the report to jump straight away to the first sales transaction we are interested in. There is no need to start from the very beginning of the database.

We therefore need to be able to tell the report to go straight away to the start date the user specified or at least jump right over any transactions before the start date. Because the report is sorted by date we can get the report to move instantly to any date we want. Do the following:

1. Click on the "report body". The report body is the set of columns with the red dashed line beneath it. Select this object by pointing to it with the mouse and clicking on the left mouse button.

2. Press the right-mouse button to activate the pop-up menu.

3. Click on Body Properties...

4. Click on the Links tab button.

5. In the edit box next to Seek type the following: DTOS(Answer1)

6. The check box Nearest Match should be ticked. If it isn't ticked, tick it.

7. Click on the OK button.

Recall that Answer1 is the variable that holds the user's input when prompted to enter the start date. We had to use the DTOS() function because the Sort order also used it.

The two expressions need to be equivalent. So if the sort order was: Upper(Name) and Answer1 contained the "name" that the user wanted to print a report on, then the Seek value would be set to Upper(Answer1).

It is also important that Nearest Match was ticked. This translates into "find the date or any date nearest to what was requested." If an exact match is not found, the next highest date is found.

Keep in mind that if you wanted to print a report from, say, the 1st of June to the 7th of June and there were no transactions on the 1st, the report would still work because the next date would be located. Perhaps the first transaction on the 2nd or 3rd of June. If the Nearest Match setting is turned off, an exact match must be found. If no exact match is found, the report ends with nothing reported.

So, continuing with the above example, if there was no sale on the 1st of June the report would not proceed, even though there may have been transactions between the 1st and 7th. A near match means that the report will first look for transactions on the 1st, then the 2nd, then the 3rd, and so on, until something is found, or there is nothing left to search.

Saving the Report to Disk

The last and most important step is to save the report.

1. Select File from the editor menu.

2. Select Save as... and enter a short code to identify the report. The code must not exceed 8 characters in length.

As we are now finished writing the report, we can exit the editor. Select Exit from the Files menu.

9. Running the Report

To run the report start Visual Builder and click on the Execute Form box. The report "Invoice Sales Report" will appear on the Execute Form list. Select it with the mouse and click on Run... to execute it.

____________________________

Related Topics:

Visual Builder Forms Library


CAPITAL VISUAL BUILDER