Skip to main content

Report Generator 2

The article details how to use the Report Generator 2

N
Written by Noel

Overview

The Report Generator 2 allows users to build custom reports using a wide range of filters and data sources.

It is setup in such a way that you can add fields to the report with a single click and the report will create the data links in the background for you.

Important: It's important to note that the report generator 2 only has access to Transactions and their linked Invoices. This means it cannot be used to report on active jobs, or pull through details for accounts, addresses or suppliers etc Additionally, Report Generator 2 does not support waste percentage reporting as a configurable column. To include this metric, a custom report would need to be developed..


Accessing the Report Generator screen

  1. Click on the Reports button at the top of the screen.

  2. In the top-right corner, click on Generator 2.


Navigating the Screen

  • Left-Hand Side

    • Report - This allows to you to select and load a previously saved report.

    • Dataset - This allows you to switch the available data fields between Transaction and Invoice.

    • Columns - These are the columns that are available to be added to the report

    • Pre Set Column - These are pre-loaded columns allow you to easily add summaries (for example adding the "movements" column will provide a total number of jobs for the given filters.

  • Top Rows

    • Export to CSV - Clicking on this will export all the results for the current report to a new CSV (excel) file.

    • Export to Pivot - Clicking on this will export all the results for the current report to a new CSV (excel) file.

    • Report - Clicking on this will make the main section of the screen display the report columns and filters.

    • Options (top) - Clicking on this will display the options for exporting and emailing the report.

    • Schedule (top right) - This allows you to schedule the report to be emailed out on a regular basis.

      • For more details on scheduling reports please refer to the Scheduling A Report knowledge article.

    • Switch Mode - This allows you to switch between the main "Report Builder" mode which shows the columns and the "Filter" mode which will only show the filters and allow them to be changed

  • Main Screen (centre)

    • Column - The name of the field that is pulling through data. Each entry here will be displayed as a column on the report and its exports.

    • Or - Allows to select multiple set filters and make report grab data that means, for example, filter 1 or filter 2 rathe than both at once.

      • For example - have the report display results where the account is "001" or the ProductCode is "Waste"

    • Where - This is where filters can be added, edited or removed from a column.

    • Select - Unticking this box will hide the column from the report while still applying any filters that are in place. This is used if you are looking to group entries

      • For example if you want to group all jobs in a date range product you would set a ate range filter but not show the actual date itself as that would make the jobs be split on the report.

    • Aggregate - this allows you to set how the results of a column are shown when the report is using grouping.

      • For example if you wanted the report to show the total Quantity across a group of jobs you would add the Quantity column and set this "SUM"

    • Sorting - This allows you to display the results of that particular column in Ascending or Descending order.

    • Format - This allows you to change the format of the date displayed on the report for any column.

    • Delete button - Clicking on this will delete the column (and any filters set against it) from the report.

  • Main Screen (lower section)

    • Grid View (Top 10) - This section displays the top 10 results of the report based on the columns and filters you have set up.

      • There is no way to view the entire report on-screen - they have to be exported using the CSV or Pivot buttons.

    • SQL View - This screen is the code behind the report. It cannot be automatically edited but will updated as the columns filters are amended.

      • This is mostly for support use.

    • Options (bottom) - Displays further options for the report formatting

      • By default these are all enabled.


Open an existing Report

  • Go to the generator 2 screen as normal, then click in the "Report" drop-down menu.

  • You will see a list of Generator 2 report to which you have access

Note: You will only be able to see reports to which you have access. See the "managing Reports Access" section of this article for more details.

Once you click on the report it will be loaded on your screen.

Important: Reports are shared by all users so be aware that is you make changes to the report and hit Save those changes will be enabled for every other user of the system.


Starting a New Report

When you go to the Report Generator screen you will be presented with a new blank report containing just a "Date" field.

You can edit this immediately and then save it as a new report.


Editing the Contents of a Report

Adding columns to the report

The screen will start with the "Transactions" dataset pre-loaded in the top-right corner, this controls the fields available for selection. You can switch the dataset to "invoices" by selecting it from the drop-down and clicking on "Load". The available fields will automatically update.

To add a column to the report you just need to click on it on the left-hand list.

For example, if you want the report to include the ProductCode then locate the item in the "Columns" section an click on it. The selected columns will be added to the main screen (new columns always appear at the bottom of the list) and the grid View at the bottom of the screen.

Note: As mentioned above, the available fields are drawn from the Transaction and Invoice areas of the system. If there are data fields from those areas missing that you would like to have added then please reach out to support for it to be looked into for you.

Moving the Fields

You can reorder the columns by clicking on the up or down arrows to the left of the items.

Add a filter to a column

The "Where" column allows you to add filters to a column. For example if you wanted to only show results for a specific account you would add a filter to the Account column.

In this example we will be setting a filter to only show results for a specific account.

  1. Click on the "Add" link in the Where column for the field.

  2. Set the Type to "Equals"

  3. Click in the next box and select the required account

  4. Click Add to apply the filter

You can also add multiple filters to the any column. This is useful for adding a date range where you date the date column to be "equal to or greater than" a start date and also "less than or equal to" an end date. For example, to include data from 14/09 to 15/09, you would:

  • Add a clause with "Greater than or equal" = 14/09.

  • Add a clause with "Less than or equal" = 15/09. This approach allows you to define any date range for your reports.


Saving the Report

Once you have added the fields and set the layout you require, ensure you have put a name for the report in the Name field and click on "Save As" at the top of the screen.

That will save the current report as a new file.

Important:

  • New Report: If you are saving a new report for the first time first enter a name for the report in the "Name" field and then click on "Save As". The report will be saved under than name.

  • Existing Report: If you are editing an existing report then just hit "Save" otherwise you will end up with duplicate reports all under the same name.


Deleting a Report

If a report is no longer required you can delete it via the below steps:

  1. Open the report as normal

  2. Click on the Delete button in the top-right
    ​

  3. Click "Ok" in the confirmation prompt that appears

The report will then be deleted from the system.

Important: This will delete the report from the system for all users.


Managing Report Access

When you create a new report it will, by default, only be available to your user.

To add or remove report access to other users you can click on the "Manage reports" link in the top-right corner.

  • In the screen select the report you want to share or restrict from the drop down menu.

  • The lower screens will then display the users who do not currently have access in the left-hand box, and the users who do have access in the right-hand box.

  • To add or remove a user tick the box against their username and click the "Change" box at the bottom. That user will then be moved to the opposite box.


Tips & Tricks

Adding a Dynamic Date Range

Rather that setting a specific start and end date it is possible to add a "dynamic" date range. This is especially useful if you intend to schedule the report to be emailed out as the dynamic values allow the system to determine things like "last month" or "this week" when you set up a schedule.

To set the dynamic date range you need to add filters to the Date column as normal an use the below options.

  • From Date

    • Type = Date From

    • Date = @fromdate

  • To Date

    • Type = Date Less Than

    • Date = @todate

The filters will end up looking like this when viewed:


Example Reports

Below are screenshots of some examples of reports that are commonly requested.

Total Sales and Job Count by Product

This report looks through the data and grabs all transactions within a date range set by yourself, then displays the total number of jobs and total sales value within that rage for each product.
​


Available Fields

The fields available for "Gen 2" report are split into groups on the left side of the screen. You can either scroll through the list or use the Search box to search for them manually.

Account Details Group

Field

Description

AccountCode

Customer account code (account lookup search)

AccountName

Customer account name

AccountShortName

Short name of the account

AccountNoLedger

Account ledger number

AccountAddressName

Account address name

AccountAddress1 / AccountAddress2 / AccountAddress3

Account address lines

AccountTown

Account town

AccountPostcode

Account postcode

CollectionAddressNo

Collection/site address number (address lookup search)

CollectionAddressName

Collection/site address name

CollectionAddressShortName

Short name of collection address

CollectionAddress1 / CollectionAddress2 / CollectionAddress3

Collection address lines

CollectionTown

Collection town

CollectionPostcode

Collection postcode

ContactName

Contact name on the transaction

ContactEmail

Contact email

ContactTel

Contact telephone

CollectionAddressType

Type of the collection address

Transaction Group

Field

Description

ToBeLabTested

Flag: transaction requires lab testing

OutletOverrideAddressNo / OutletOverrideAddressName / OutletOverrideAddressLine1–4 / OutletOverrideAddressCounty / OutletOverrideAddressPostcode

Outlet override address fields

AddressReportGroupCode / AddressReportGroupDescription

Address reporting group code/description

AccountManagerCode / AccountManagerName

Account manager (account manager lookup search)

BatchNo

Batch number

BatchTicketId

Batch ticket ID

Broker

Flag: transaction is a broker job

Cancelled

Flag: transaction cancelled

CancelledReason

Cancellation reason text

SourceCode / SourceDescription

Waste/job source code and description

CashSale

Flag: cash sale

ConsignmentNoteCode

Consignment note reference

DefraWasteTrackingId

Defra waste tracking ID

ContractInvoiceType

Contract invoice type

ContractItemOrderNo / ContractOrderNo

Contract item / contract order numbers

Created / CreatedBy

Record creation timestamp / user

CustomerOrderNo

Customer's order number

Date

Transaction date (EventTimestamp in DB)

Day

Day name of the transaction

Deleted

Flag: record deleted

DemurrageId

Linked demurrage record ID

DriverFeedback

Driver feedback text

DriverHandheldActual

Driver handheld "actual" value

EWCCode

European Waste Catalogue code (EWC lookup search)

ExceptionText

Exception message text

FirstPaymentDate

First payment date

HaulierRef / HaulierCode / HaulierName / HaulierShortName

Haulier reference, code and names (haulier lookup search)

HaulierSelfBilled / HaulierSelfBilledDocId

Haulier self-billing flag / document ID

Job Type

Transaction event type (EventTypeId, job type lookup search)

LastModified / LastModifiedBy

Last modified timestamp / user

ManualContractNo

Manually entered contract number

Notes / NotesMobile / NotesWeighbridge

Notes fields (office, mobile/driver, weighbridge)

OnboardGross / OnboardNet / OnboardTare

Onboard weighing weights

OnStop

Flag: account on stop

OriginalTicketNo

Original ticket number

PermitRequired

Flag: permit required

Round / RoundType / RoundTypeCode

Round and round type (round lookup search)

SalesRepCode / SalesRepName

Sales rep code/name (rep lookup search)

SealNo

Seal number

SicCode

SIC code (SIC lookup search)

SkipOrderId

Linked skip order ID

SmallNotes

Short notes field

SupplierFromInvoiceName / SupplierFromInvoiceNo / SupplierFromName / SupplierFromTicketNo

"Supplier from" fields (haulier/supplier of the waste)

LoadingAdditionalJobReference / UnloadingAdditionalJobReference

Additional job references for loading/unloading

TicketCreated / TicketCreatedBy

Ticket creation timestamp / user

Transaction Item Group

Field

Description

Disposal Rate

Disposal charge rate on the line

Disposal Unit

Unit for the disposal rate

ActivityCode / ActivityDescription

Activity code on the line (activity lookup) and its description

AreaCode / AreaDescription

Area code (area lookup) and description

CertificateOfDestructionReqd

Flag: certificate of destruction required

CHBuyPrice

Contract hire / cost-of-hire buy price

CHProductPrice / CHProductPriceTotal

Contract hire product price and line total

CHHaulBuy / CHHaulSell

Contract hire haulage buy and sell prices

CHSupplierCode / CHSupplierDescription

Contract hire supplier (supplier lookup) and description

CHTonnageOverTNS

Contract hire tonnage over TNS

CHUnitPrice

Contract hire unit price

CompanyCode

Company code (company lookup)

ContainedInProductCode / ContainedInProductDescription

Parent/"contained in" product (product lookup)

ContractItemId

Linked contract item ID

Created / CreatedBy

Line creation timestamp and user

CreateInvoice / CreateInvoiceSelfBill

Flags: include on sales invoice / on self-bill invoice

CreditedQuantity / CreditingType / CreditTransactionItemId

Crediting details for credited lines

CustomerOrderNo

Customer order number on the line

Deleted

Flag: line deleted

Trans Item Type

Line item type (rendered via CASE format)

EWCCode

EWC waste code on the line

GrossDateTime / GrossTallyNo / GrossType / Grossweight

Gross weigh details (weighbridge lines)

HandheldCalcPrices

Flag: prices calculated on the handheld

HaulBuyPrice / HaulBuyUnit / HaulBuyUnitPrice

Haulage buy price, unit and unit price

HaulSellPrice / HaulSellUnit / HaulSellUnitPrice

Haulage sell price, unit and unit price

HaulierSelfBilledDocId

Haulier self-bill document ID (line level)

PurchaseDocId / InvoiceId

Linked purchase document / sales invoice IDs

InvoiceIncludeOverride

Flag: override invoice inclusion

InvoiceNotes

Notes printed on the invoice

IsNonFerrous

Flag: non-ferrous material

IsProductExchange

Flag: product exchange line

LastModified / LastModifiedBy

Line last-modified timestamp and user

LoadingPoint

Loading point

LocationCode / LocationDescription

Depot/location code and description

MaxPrice / MinPrice / MinMaxPriceAdjusted

Min/max price cap fields and adjusted flag

ModeOfDisposalRecoveryCode

Disposal/recovery mode code

Netweight

Net weight of the line

EstimatedWeight

Estimated weight

Notes

Line notes

OnHoldSelfBill

Flag: self-bill on hold

OrderNo

Order number

OriginalTransactionItemId / OriginalUnitPrice

Original line ID and unit price (for amended lines)

OutletName

Outlet/tip name

OutletCode / OutletShortName / OutletType

Outlet code (outlet lookup), short name, type

BayCode / BayDescription

Weighbridge bay

PhysicalForm

Physical form flag

PortalCreated / PortalId

Created via customer portal / portal ID

PriceId

Price record used

PricePerUnitModifiedAtWeighbridge

Flag: unit price edited at weighbridge

ProductCode / ProductDescription

Product (allproducts lookup) and description

ProductGroupCode / ProductGroupDesc

Product group (producttype lookup) and description

ProductPrice

Product sell price

QtyContainer

Number of containers

Quantity / QuantityRequired

Quantity on the line / required quantity

QuoteItemNumber

Linked quote line number

Rebate / RebateQuantity

Rebate amount and quantity

Revenue

Line revenue

RoundDay / RoundWeek / RoundTemplateName

Round schedule details

Signature1 / Signature1SignedBy / Signature2 / Signature2SignedBy

Captured signatures and signer names

SingleInvoiceType

Flag: invoice this line separately

SkipCollectedNo / SkipDeliveredNo

Skip numbers collected/delivered

SurchargeBuyOrSell

Whether surcharge applies to buy or sell

TareDateTime / TareTallyNo / TareType / Tareweight

Tare weigh details

SampleNo

Sample number

Tax / TaxCode / TaxCodeEndSite / TaxInclusive / TaxLiable / TaxRate

Aggregate/landfill tax fields (amount, codes, inclusive flag, liable amount, rate)

TippNett

Tipping net value

TonnageOverTNS

Tonnage over TNS threshold

Tonnes

Tonnes

TotalPrice / TotalProductPrice

Line total / product total

UnitOfSaleCode / UnitOfSaleDescription

Unit of sale (unit lookup) and description

UnitPrice / UnitPriceCost / UnitPriceOverride

Unit sell price, cost, override price

VatCode / VatRate

VAT code (vatcode lookup) and rate

Volume

Volume

WasteType / WasteTypeDescription

Waste type (wastetype lookup) and description

ZoneCode / ZoneDescription

Pricing zone (zone lookup) and description

Analysis1 / Analysis2 / Analysis3

Analysis code fields

TurnaroundTime (M)

On-site turnaround time in minutes (calculated)

Did this answer your question?