Report S301 - Client sales - Grouped by service
This report shows orders and respective sales. Sales are split down by individual services such as cost of translation, cost of fuzzy translation, cost of revision, etc. Each line in the report contains a specific service specification and the respective amount of sales, as well as, information on the client, the order, the invoice and the project.
Report content
When creating the report you select a date range and the system will select all orders that were created within this date range. It then fetches all invoices attached to these orders.
The report groups invoice details by service specification. Invoice lines are grouped by:
- Task type (translation, revision...)
- Source language
- Target language
- Unit (per word, per page...)
- Unit price
- Discount and match rate were applicable
- Total of all lines in the invoice sharing this specification
This report does not include:
- Does not include cancelled orders
- Does not include cancelled invoices
- Does not include aggregation invoices so as to avoid showing amounts twice. Instead we list the original invoices and quotes only.
- Does not include orders not yet attached to a project.
Also read Languages, Dates and Currencies for important information.
Template
Screenshot of default template:
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 |
|---|---|
| Order fields | The order details. |
| <#Lines.First> | Prints a * (star) if this is the first line for an order. The report contains as many lines per project as there are client or supplier invoices. |
<#Lines.UserOrderId> | The order Id of the related order. |
| <#Lines.OrderReference> | The order reference. |
| <#Lines.OrderClientCompany> | The client company name. |
| <#Lines.OrderClientPerson> | The person at the client who submitted the order. |
| <#Lines.OrderStatus> | The status of the order. Any of: Request, Proposal, In progress, Work done, Results approved, Closed, Cancelled. Note that the task status title is localized. |
| <#Lines.OrderInstructions> | The free text instructions included by the client in the order form. |
| <#Lines.OrderComments> | The internal comments in the order. |
| <#Lines.OrderCreationDate> | The date when the order was created in the system. This is a native Excel date. |
| <#Lines.OrderDeadline> | The deadline in the order, if any. This is a native Excel date. |
| <#Lines.OrderReceivedDate> | The date when the order was received (status Request). This date typically is the same as the date of creation. However, the project manager can change this date in the order details page. |
| <#Lines.OrderProposalDate> | The date when the order status is set to Proposal. This means that you have confirmed or completed the quote. The next step is that the client accepts the quote. |
| <#Lines.OrderAcceptedDate> | The date when the order status is set to In Progress. This means that the proposal had been accepted by the client. |
| <#Lines.OrderDoneDate> | The date when the order status is set to Work done. This status is set by the project manager when work is completed and deliverables saved to the order. The client can optionally change the status to Approved or otherwise set back to In Progress if there are any problems. |
| <#Lines.OrderClosedDate> | The date when the order was closed by the project manager. The order status then is Closed. |
| <#Lines.OrderOption> | The order option selected when submitting the order. (Type of work clients or internal translation requesters selected) |
| <#Lines.OrderDomains> | The translation domains selected when submitting the order. |
<#Lines.OrderCF1> ... <#Lines.OrderCF50> | Order custom fields 1 to 50. As the platform administrator, you can create up to 50 custom fields for orders. To find out the ID (1, 2, ... 50) of a specific custom field, edit the custom field in "Settings" > "Custom Fields" > "Order". The ID is shown at the bottom of the editing popup. Also read: Finding Custom Field ID |
<#Lines.CCF1>... <#Lines.CCF20> | Client company custom fields 1 to 20. As the platform administrator, you can create up to 20 custom fields for companies. To find out the ID (1, 2, ... 20) of a specific custom field, edit the custom field in "Settings" > "Custom Fields" > "Companies". The ID is shown at the bottom of the editing popup. Also read: Finding Custom Field ID |
| Project details | The project to which the order is attached. |
| <#Lines.ProjectReference> | The project reference. |
| <#Lines.ProjectStatus> | The project status title. |
| <#Lines.ProjectManager> | First and last name of the project manager (if any). |
| <#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. |
| Invoice details | Contains details on the client invoice attached to the order. Otherwise, all fields are empty. |
| <#Lines.ClientInvoiceReference> | The invoice reference. |
| <#Lines.ClientInvoiceStatus> | 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.ClientInvoiceEffectiveDate> | 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.ClientInvoiceNo> | 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.ClientAmountExVAT> | The total invoice amount excluding VAT. |
| <#Lines.ClientAmountVAT> | The amount of VAT. |
| <#Lines.ClientInvoiceCurrency> | The currency code of the invoice, such as EUR, USD... Formatted as 3 letter ISO code. |
| <#Lines.ClientAmountExVATConv> | The total ex VAT amount converted to your currency. See also below and Languages, Dates and Currencies. |
| <#Lines.ConvertToCurrency> | The currency to which the above amount is converted. Same as <#MasterCurrency>. Formatted as 3 letter ISO code. |
| <#Lines.ClientInvoiceChangeDate> | The last status change of the invoice. |
| <#Lines.ClientInvoiceDate> | 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.ClientAmountIncVAT> | The total invoice amount including VAT. The sum of ClientAmountExVAT and ClientAmountVAT |
| <#Lines.ClientVATRate> | The VAT rate percentage. Provided as a string with percentage sign: "10%", "22.5%"... |
| Service specification | The service, i.e. language combination, task type, unit and unit price, reduction and more. IMPORTANT: In many cases some or all fields are not supplied with invoice details lines. In that case the respective fields below are simply empty. For example, an invoice details line may not specify the task or a language, or the unit price is missing, etc. |
| <#Lines.ServiceTaskName> | The task. Tasks are "Translation", "Revision", etc. Note that the task name is localized. For example, if an invoice contains details for translation then this field will be "Translation". |
| <#Lines.ServiceTaskCode> | The task code such as TR for translation or PROOF for proofreading. These codes can be viewed in Wordbee under Settings > Task categories. |
| <#Lines.ServiceSourceLocaleName> | The source language name. Note that the language names are localized. |
| <#Lines.ServiceTargetLocaleName> | The target language name. Note that the language names are localized. |
| <#Lines.ServiceSourceLocale> | The source language ISO code. Note that the language names are localized. |
| <#Lines.ServiceTargetLocale> | The target language ISO code. Note that the language names are localized. |
| <#Lines.ServiceUnitCode> | The unit sold, such as WD (words), PG (pages) etc. This is a code used in Wordbee Translator. |
| <#Lines.ServiceUnitName> | The print name of the unit sold: "words", "pages", .... |
| <#Lines.ServiceUnits> | Total units included in the unit price. For example, a unit price may be defined as "0,10 USD per 2 words". Here the unit name is "words" and the "Lines.ServiceUnits" would be 2. In most cases "ServiceUnits" is just 1 (that is you express prices "per word" etc.). |
| <#Lines.ServiceReductionPercent> | If a discount is applied for the service specification. Used if the unit price is reduced because we are selling pre-translations. This field contains a decimal value between 0.00 and 100.00. An empty value means that there is no reduction. |
| <#Lines.ServiceReductionPercentText> | The reduction converted to a string such as "80%". |
| <#Lines.ServiceFuzzyCategory> | An internal numeric code describing if the price is for a fuzzy match, a pre-translation and so on. Better use "ServiceFuzzyText" which prints a user readable field. |
| <#Lines.ServiceFuzzyBoundary> | In case of pre-translations or fuzzy matches, the match %. A numeric value between 0 and 100. |
| <#Lines.ServiceFuzzyText> | The <#Lines.ServiceFuzzyCategory> as a print text. |
| <#Lines.ServiceCount> | Total units sold for the specification. |
| <#Lines.ServiceUnitaryAmount> | Unitary cost. |
| <#Lines.ServiceUnitaryAmountConv> | Unitary cost converted to your currency (<#MasterCurrency> or <Lines.ConvertToCurrency>). |
| Service sales | The total sales for work of the specification listed above. Note that an invoice may specify totals in each line without specifying a unit price or other properties. |
| <#Lines.ServiceTotal> | Total sales for the specification above. |
| <#Lines.ServiceTotalConv> | Total sales for the specification above converted to your currency (<#MasterCurrency> or <Lines.ConvertToCurrency>). |
| Metrics | Delays in the handling of the order. |
| <#Lines.OrderReceivedToProposal> | Total minutes from reception of the order to sending of proposal to client. This is the delay from <#Lines.OrderProposalDate> minus <#Lines.OrderReceivedDate>. The field is empty if either one of the dates are not yet set. |
| <#Lines.OrderDoneToDeadline> | Total minutes between completion of the order with respect to deadline. A positive value means that the order was delivered before the deadline. A negative value means that the order was delivered to late. The field is empty if either <#Lines.OrderDoneDate> or <#Lines.OrderDeadline> is not available. |
| <#Lines.OrderAcceptedToDone> | Total minutes from acceptance of the quote by the client and completion of the order. This is the delay from <#Lines.OrderDoneDate> minus <#Lines.OrderAcceptedDate>. The field is empty if either one of the dates are not yet set. |