 |  |
How to Configure - User Defined Reports
Objective:
The User Defined Report functionality is a powerful tool for the user to create custom reports. These reports are configured using the User Defined Report Wizard. The User Defined Reports can group, sort, and total data in the manner that makes the most sense to the target audience of the report. The user Defined Reports can also include simple mathematical expressions. It should be noted that the User Defined Reports are presently limited to only one table or source of data. Should you require custom reports with data from two or more tables please contact a Caribou Software representative.
Creating the Report:
We will generate a report that shows the tonnage for each block ordered by destination, species and product. We will suppress the subtotals for species and product. We will add columns for Truck No., Date In, and Ticket number. We will add a formula to convert the net into tonnes. Our end result will be:
While in Loggers Edge, open up the Reporter application by going to REPORTS | BUSINESS REPORTS.
To add a new user defined report, go to the USER DEFINED menu item and click Add.
This will fire up the User Defined Report Wizard.
Click NEXT
You will now be prompted to pick the data table from which you would like to make your report. Most of the tables are self-explanatory but if you need more information on which table the data you require is stored, you can contact your Caribou Software support associate.
Once you pick your desired table, click Next. In this example, we will pick the LOADSLIP table.
Once you have chosen a table, all of the available fields for your report will be shown in the window in Step 3. Within this window you can do a number of things. You can select the fields or items that you would like to see as columns on your report. To do so, simply click on the check box in the SELECT? column. The display order of the columns can be adjusted using the DISPLAY ORDER column. The first column to be displayed would be 1 followed by 2,3 etc. The TOTALS? column allows you to select data that needs to be totaled (e.g., you may want to see weights totaled). If you want totals to be grouped (i.e. totals by block, date, etc.) you must group those fields in the next window.
A
common practice when ordering your columns for display is to have them match
your grouping and sorting order that you will indicate in the next window. (For
example, if you plan to group and sort on Block, then Destination, then Product
and finally species, your display order would be 1-Block, 2-Destination,
3-Product, and 4-Species. The other columns can be ordered in whichever
order is most sensible given the purpose of the report.
This ordering approach will ensure that the totals align and the data will be
much easier to review. (See sample report at end of document.)
The SHOW RELATED DESCRIPTION? column will allow you to show the description vs. the code for an item in the report. This might prove useful when your codes are somewhat cryptic and you need more information on the report.
You can add an
additional column to the list of available columns shown in Step 3 above, where
the new column is a calculated value based on other columns included in your
report.
For example, you may want to show the net weight in tonnes as well as (or in lieu of) kilograms. You can use simple mathematical expressions (+,-,*,/, etc) in a field using other fields as a reference. To add a column that is a calculated value based on other columns, click the Add button in the Select Data Field window above. This action will add a blank field at the bottom of the window:
You can also used SQL functions in these calculated fields. This doesn't depend on which database you're using, but not all functions work. The functions are taken as Access functions, and if required, they are mapped over to the MS SQL equivelants.
Another addition is the addition of entering just a string that will print out on every line. To do this you simply surround the text you want to appear on every line between single quotes.
For example, if you want the text 'Company Name' to appear on every line. You would put it as shown and it will come out without the quotes on the report.
Put the desired expression or formula in the Field column, as shown in the screen below, which shows [Net]/1000. The syntax rule is to select the Field code (e.g., Net) surrounded by [ ] brackets. For example, in the picture below (row 21), we have added a column that divides the "Net" field by 1,000. We have used "Tonnes" as the column name that will be displayed in the report.
Note: the column
name you use for a newly added column cannot contain spaces in the column name.
Remember to select the new field you have created in the SELECT? column (and the TOTAL? column if that field is to be totaled).
Note: if there
is a chance that the denominator in your formula could take on a value of zero,
you need to allow for that event in your formula by using an "If" statement.
For example, suppose your formula had actually been: 1000/[Net].
(Yes, I know --- that is not a good example, but it allows me to illustrate the
point!). Because your net weight may actually take on a value of zero, you
would need to use the following "IF statement" formula instead: IIF([Net]=0,0,1000/[Net]).
In English, this statement tells the computer: "if the net weight is zero, then
display the result as zero, otherwise display 1000 divided by the net weight."
You can also use number
formatting for the numerical fields. In this example we have used
#,##0.00. This will allow commas to be displayed and two decimal places.
You can also elect to format to currency standards.
The next step (Step 4) allows you to indicate which fields you want the report to Group & Subtotal by. For example, if were building a report to give you a list of tickets for each truck driver, you would most likely want to "group by" truck driver. If you want to have more than one level of grouping, you can also configure that in this window.
In the report we are building in this example, we want tickets from the same block to be grouped together in the report (and subtotaled). Within the tickets that share the same block, we then want to group (and subtotal) by destination, then by product, and finally by species.
You can suppress the subtotaling for some of the fields in order to
de-clutter the report.
The next step allows you to control the way the data are sorted in your report.
You
almost always want to have the grouping and sorting match
(For example, we have chosen to group by
Block, Destination, Stratum and Species. We will also sort by the same
four fields in the same order. )
The next step allows you to dictate your selection criteria (i.e., your "filters") each time you run the report. Filtering options are important, as they let you determine the criteria for information to be shown on the report. If you choose no filters, all the information in the LOADSLIP table for all time will be shown. By choosing Filters, you can narrow down the scope and size of the report. For example, if you only needed certain blocks and certain dates, you would check Block and "Date In" for filtering. This will prompt you for Date and Block selections when you run the report.
You will now be prompted to enter in a report name, report header, and report description. Please notice the fields are yellow. This means the fields must have valid entries for the report to be saved. You can elect to have the date shown as the footer by using the check box. Once all the required information is entered, click the Finish button to save your report
Running the Report
To run your newly create report, you can either use the menu item at the top of the window User Defined or you can also use the User Defined Reports Tab on the left side of the window. Please note that if you have just saved your report, it will not immediately show under the left hand side tab. You must first close and restart the Report application in order for it to appear under the left hand side tab/folder.
Once you have clicked on the new report a window will pop requesting the selection criteria for the report (if you have selected fields for filtering)
The ALL:ALL block is
NOT a wildcard selection to choose all the blocks. This is a separate
block in the system used for some generic information (conversions, etc.).
The reports can also be run in "Summary Mode". This is checked in the Report Selection Criteria window.
The summary report will only show the grouped totals you have chosen.
You are now an expert in User Defined Reports!!
