Simple Sample: Discount Amount on Sales Report

This sample demonstrates how to add a custom field to the Sales Report.

We'll use Simple Invoice Template: Discount Amount Field (c4051) as the base template.

The form c4051 adds a discount amount field to the bottom of the sheet and creates formulas to apply the discount. However, this discount doesn't show up on reports.

Here are the steps we took to add the field to the "Sales" report:

1. First, backup the template.
2. Open the template.
3. Activate the "Sales Report" worksheet.
4. Switch to the Excel "View" ribbon tab.
5. Check "Gridlines" and "Headings" in the "Show" ribbon group.
6. Click to select the "Balance Due" report heading cell ($N$11).
7. Click the "Format Painter" button in the "Clipboard" group on the Excel "Home" ribbon tab.
8. Click the cell next to "Balance Due" on the same row. In this sample, it is $R$11. This copies the format of the "Balance Due" cell to the new (empty) cell.
9. With the new cell still selected, enter the text "Discount" into this cell, and then enter the cell name "oknRsDiscountAmount" into the name box at the left end of the formula bar. This assigns the specified name to the cell.

   In this name, "oknRs" is the prefix of the name, and "DiscountAmount" is the database field name.

   When adding a custom field to the Invoice worksheet, we learned that the mapping between an Excel cell on the form and the backend database field is established by names. For example, the database field "DiscountAmount" in the "InvHdr" database table is mapped to the cell name "oknDiscountAmount" on the worksheet.

   Likewise, a report column is mapped to a database field using names too. In this example, we name the report heading cell "oknRsDiscountAmount", which maps the report column to the database field "DiscountAmount".

10. The default Sales Report is designed to be printable on A4 paper. Adding the Discount field makes it too wide in the horizontal direction. To make sure it fits on one single page in the horizontal direction, we need to hide a column. To do this, click the "Columns" button on the top, and then uncheck the column that you want to hide. In this example, we hide the "Sales Rep." column. You can also adjust the column widths to make the report suitable for the paper size.
11. Once everything is done, go to the Excel "View" ribbon tab and uncheck "Gridlines" and "Headings".
12. Save the template.

our app provides flexible reporting features that allow you to add new user-defined fields (i.e., custom fields). Please Visit Product Names and Shipping Address on Aging Report (c5150) for information on how to show items on the "Aging" report sheet.