Report S102 - Client and supplier costs - By effective date
This report lists projects with all client and supplier invoices. The report includes month columns and invoice totals are listed in the respective columns. An invoice total is assigned to the month corresponding to its effective date. Contrary to report S100, client and supplier invoice details are listed in the same columns which makes this report more compact. Supplier amounts are listed as negative values.
Report content
When creating the report you select a date range and the system will select all projects that have either their date of creation or their start date (change of status to In Progress) within this date range. It then fetches all the client and supplier invoices.Cancelled invoices or quotes are disregarded. Aggregation invoices are not listed to avoid showing amounts twice. Instead we list the original invoices and quotes only.
Also read Languages, Dates and Currencies for important information.
Template
Screenshot of default template:
The month columns are to the right:
Global placeholders
These can be placed in any location in the Excel and are general properties applicable to all the report.
| Placeholder name | Description |
|---|---|
| <#StartDate> | The start date selected when creating the report. The date is adjusted to the time zone you have configured. |
| <#EndDate> | The end date selected when creating the report. The date is adjusted to the time zone you have configured. |
| <#GeneratedDate> | The date when the report was generated. |
| <#MasterCurrency> | Your default currency as 3 letter ISO code (such as EUR or USD). This is taken from the default price list you configured in the My Company page of your platform. Read more. |
Data rows and placeholders
Each row in the report corresponds to either a client invoice or a supplier invoice. In other words, all information of a specific invoice will be contained in one single row.
The placeholders of the row must be placed within the Excel table range named "___Lines___" (3 leading and trailing underscore characters). Click here for more details on table ranges.
A report prints the raw data in rows. Each row, you can use any of the following placeholders:
| Placeholder name | Description |
|---|---|
| Project fields | The project to which an invoice refers to. |
| <#Lines.ProjectFirst> | Prints a * (star) if this is the first line for the project. The report contains as many lines per project as there are client or supplier invoices. |
| <#Lines.ProjectReference> | The project reference. |
| <#Lines.ProjectClient> | The client company name. |
| <#Lines.ProjectStatus> | The project status title. |
| <#Lines.ProjectManager> | First and last name of the project manager (if any). |
| <#Lines.ProjectInProgressDate> | The date when the project status was last changed to In Progress. This is a native Excel date. |
| <#Lines.ProjectCompletionDate> | The date when the project status was last changed to Completed. This is a native Excel date. |
| <#Lines.ProjectComments> | The internal comments saved with the project. |
| <#Lines.ProjectType> | The type of project. Prints either "Standard" or "Codyt". The former is for standard projects and the latter for Codyt projects. This title is not localized. |
| <#Lines.ProjectCreationDate> | The date when the project was created. This is a native Excel date. |
| Client details | Additional client fields. |
| <#Lines.Company> | The name of the supplier company (if this line is a client invoice). |
| <#Lines.ClientCode> | The optional "Code" field that can be supplied in the client details page. |
| <#Lines.ClientAccCode> | The optional "Accounting code" field that can be supplied in the client details page. |
| <#Lines.ClientDebtorNo> | The optional "Debtor no" field that can be supplied in the client details page. |
| <#Lines.ClientCountry> | The country code of the client. |
| Supplier details | Additional supplier fields. Included ONLY if this line is a supplier invoice (see Lines.InvoiceType) |
| <#Lines.Company> | The name of the supplier company (if this line is a client invoice). |
| <#Lines.SupplierCode> | The optional "Code" field that can be supplied in the supplier details page. |
| <#Lines.SupplierCountry> | The country code of the supplier. |
| Type of row | Tells if a row contains a client or supplier invoice. |
| <#Lines.InvoiceType> | The type of invoice. Prints either "CLIENT" or "SUPPLIER". This title is localized. |
| <#Lines.IsClientInvoice> | A boolean field that indicates if the invoice is a client invoice (true) or a supplier invoice (false). This field is a native Excel boolean value. |
| Invoice Services | Whether a row is a client or supplier invoice, these fields summarize all tasks and languages of the invoice (i.e. we look at the details lines of the invoice). |
| <#Lines.TaskName> | The list of all task contained in the invoice separated by commas. Tasks are "Translation", "Revision", etc. Note that the task name is localized. For example, if an invoice contains details for translation and revision then this field will be "Translation, Revision". |
| <#Lines.TaskCode> | The list of all tasks with their code separated by commas. Tasks codes are e.g. TR, RV, PROOF etc. These codes can be viewed in Wordbee under Settings > Task categories. |
| <#Lines.SourceLocaleName> | The list of all source languages contained in the invoice and separated by commas. Note that the language names are localized. |
| <#Lines.TargetLocaleName> | The list of all target languages contained in the invoice and separated by commas. Note that the language names are localized. |
| <#Lines.SourceLocale> | The list of all source language ISO codes contained in the invoice and separated by commas. Note that the language names are localized. |
| <#Lines.TargetLocale> | The list of all target language ISO codes contained in the invoice and separated by commas. Note that the language names are localized. |
| Invoice fields | Each row is either a client or a supplier invoice, see Lines.InvoiceType. The fields below are the properties of that invoice. |
| <#Lines.Company> | The name of the client or the supplier company. |
| <#Lines.InvoiceReference> | The invoice reference. |
| <#Lines.InvoiceStatus> | The localized status of the invoice. The status also indicates if the invoice document is still a quotation or already an "invoice". Note that cancelled invoices are not included in this report. |
| <#Lines.InvoiceEffectiveDate> | The effective date of the invoice. he financial period to which an invoice is attributed. Assigned when an invoice status moves from quotation to "Draft invoice". |
| <#Lines.InvoiceNo> | The accounting invoice number. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted. |
| <#Lines.AmountExVAT> | The total invoice amount excluding VAT. |
| <#Lines.AmountVAT> | The amount of VAT. |
| <#Lines.InvoiceCurrency> | The currency code of the invoice, such as EUR, USD... Formatted as 3 letter ISO code. |
| <#Lines.AmountExVATConv> | The total ex VAT amount converted to your currency. See also below. |
| <#Lines.ConvertToCurrency> | The currency to which the above amount is converted. Same as <#MasterCurrency>. Formatted as 3 letter ISO code. |
| <#Lines.InvoiceChangeDate> | The last status change of the invoice. |
| <#Lines.InvoiceMonth> | The last change of the invoice with the year and month. Such as: 2016-10 or 2015-01. |
| <#Lines.InvoiceDate> | The accounting date of the invoice. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted. |
| <#Lines.AmountIncVAT> | The total invoice amount including VAT. The sum of ClientAmountExVAT and ClientAmountVAT |
| <#Lines.VATRate> | The VAT rate percentage. Provided as a string with percentage sign: "10%", "22.5%"... |
| Month columns | In each row, the invoice total excl. VAT is contained in exactly one month field. This is the month of the invoice's effective date. |
| <#Lines.BucketNull> | Total shown here if the invoice has no effective date. Note that this is the case for all quotations. The effective date is assigned when an invoice moves beyond the "Invoice Draft" status. |
| <#Lines.BucketBelow> | Total shown here if the effective date is earlier than the date of "Bucket1", see below. |
<#Lines.Bucket1> <#Lines.Bucket2> ... <#Lines.Bucket12> | Each "bucket" represents one month. The report shows up to 12 buckets i.e. 12 months. If the the range of effective dates spans more than 12 months then the buckets are the latest 12 months and all earlier dates are assigned to the "BucketBelow" column. Bucket1 is the earliest month and Bucket 12 the latest. |