Vendor Report

This report aims to provide a detailed view of vendor transactions, which may be useful for analyzing vendor performance, tracking expenses, and identifying any unusual or significant transactions.

They can help businesses identify opportunities to improve efficiency, reduce costs, and mitigate risks associated with vendor relationships. For example, a vendor report might show that a particular vendor consistently delivers goods or services late, leading to production delays or customer complaints. Alternatively, a vendor report might reveal that a certain supplier offers better pricing or higher quality products than other vendors, leading to potential cost savings or competitive advantages.

This report shows a summation of each entry for a particular vendor

 To create this Vendor analysis report, we need information from 2 different tables…

1. Vendor

2. vendor ledger entries

Procedure

Step 1 Know where you are pulling your data from

Just as shown below, the NL(Rows) data that contains column name and file will be pulled from the tables name and filed as shown in the screenshot, while the NL(Rows) with no column name and filed will contain our source of data, which will be pulled from the table “Vendor Ledger Entry“. The NF (Keys) will be a cell reference, which will be referencing cell $I7, and the fields will be pulled from the names as shown.

Since we now know where to pull our table, keys, and fields from, I’ll show you how to do that here. Once you are done creating the functions, you can remove the names on the last row. They are just there to guide you on the name to put in the field when filling in the formulas.

Step 2 Creating Report Options for the Report

Click on the report and fill in the titles as shown or as per the report you want to create. fill in the values for the Period Start and Period End, and use “*“ for the rest as you can select what you want it to display when you want to run the report.

When you fill in all that, close the window, that will create a Report Options page to your report.

• filling the lookup formulas

Company Lookup formula

Document Type Lookup formula

Responsibilities Center Lookup Formula

Vendor No Lookup Formula

Step 3

• filling the formulas for the NL function with columns name and field.

The vendor number contains most of the filters, and every filter applied is referenced to the report options.

Since we want the report to display a summation of each entry, the trick to that will be done on Vendor no. so, instead of just rows, we use “Rows=4“.

• Referencing to report options page

I will use the Document type as an example here. select “Document Type on the filter right box, then click on the left box and go up to the window, then click on “Insert Report Option“A new window will pop out, select “Document Type“ as shown below.

• NL(First)s for vendor name is filled as shown with “No“ referencing to Cell “$E5“. Every other place where “No“ or “Vendor no“ will be used will all be referencing cell $E5.

• Filling for NL(Rows) that do not contain column names and fields.

Every other filter will reference report options except “Vendor No“

Step 4

• Filling for NF Functions and calculating the sub-total and Grand-total will be filled as shown below.

Note: use the same procedure to fill in all the NF functions. the key will be the same, just the field will be different.

• This sum is the total for each entry of a particular Vendor

Grand total

Step 5

When you click on run, the report options page will pop out. you can then select the filter you want to apply to your report.

Here, I select my company name, my document type to be filtered to just payment, and Responsibilities Center to be filtered to ODE, but I use “*“ because I want to see all vendor numbers.

When you run your report, it should look like this.