Others

How to Create a Gross Receipts Report by City in Dynamics 365

Nimra Malik Oct 10, 2026 868 views

Question details

The user needs to generate a fiscal-year sales report grouped by the specific city where work was performed, regardless of the customer.

How to Create a Gross Receipts Report by City in Dynamics 365

Product: Dynamics 365
Device & OS: not provided
Scenario: Tracking and reporting non-taxable construction sales based on the actual job location city instead of the default customer address.
Observed behavior: Standard sales tax reports are insufficient because most sales are non-taxable, requiring a custom grouping by a city dimension.

Before you start

Ensure you have administrative or appropriate reporting permissions in Dynamics 365 to add custom fields or modify report dimensions.

Solution 1Recommended

Capture Work Location and Group Sales Data in Dynamics 365

Use custom dimensions or fields on each invoice line to accurately track the work city for native D365 reporting.

To report accurately when a single customer has multiple jobs across different cities, the geographic data must be captured at the transaction or line-item level rather than the customer level.

Add a Location Field

Navigate to your Dynamics 365 customization settings to add a new 'City' or 'Work Location' dimension to your Sales Transaction, Project, or Invoice Line records.

Update Data Entry Protocols

Ensure staff input the specific city for every job on the invoice line, regardless of the overarching customer's billing address.

Create a Custom View

Go to the Reporting or Views section in Dynamics 365 and create a new custom view or dataset.

Group and Filter Data

Set the primary grouping parameter to your newly created 'Work Location' field and apply a date filter for the required fiscal year.

Capture Work Location and Group Sales Data in Dynamics 365

Community Support: For product-specific configuration of dimensions, consult the Dynamics 365 community forums as exact setup steps vary between versions like Finance & Operations and Business Central.

Solution 2

Export Sales Data for Spreadsheet PivotTable Analysis

If native reporting tools are too rigid, export the raw transaction data to a spreadsheet software to manually group and analyze receipts by city.

Advanced Data Analysis

Quickly Analyze Exported Dynamics 365 Data with WPS Spreadsheet

If you've exported your sales data from Dynamics 365, WPS Spreadsheet offers powerful PivotTable features to instantly group, filter, and summarize your gross receipts by city.

1. Open Exported Data: Launch WPS Spreadsheet and open the .xlsx or .csv sales data file exported from Dynamics 365.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon, click 'PivotTable', and select your entire sales data range.
3. Group by City: In the PivotTable fields pane, drag your 'City' or 'Location' column into the 'Rows' area, and 'Sales Amount' into the 'Values' area.
4. Filter by Fiscal Year: Drag the transaction 'Date' column into the 'Filters' area to easily isolate the specific fiscal year you need to report on.

Easily create PivotTables to group complex sales and invoice data by city.100% compatible with exported Microsoft Excel (.xlsx) and CSV files from Dynamics 365.Apply advanced data filters to isolate specific fiscal years and non-taxable revenue.Free, lightweight, and user-friendly interface for professional reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use the standard sales tax report for gross receipts?

Standard sales tax reports generally only track taxable sales or base the geographic location on the customer's billing address. If your work is primarily non-taxable and spread across different cities for the same customer, a custom dimension is required to report accurately.

Can Power BI connect directly to Dynamics 365 for this report?

Yes, you can build a Power BI dataset that connects directly to your Dynamics 365 instance. This allows you to pull the custom city field from your invoice lines and dynamically visualize gross receipts by location.

What if a single customer has multiple jobs in different cities?

To handle this scenario, you must ensure that the 'City' or 'Work Location' field is tracked at the invoice line item or job project level, rather than at the master customer account level. This ensures each specific job's revenue is allocated to the correct city.

Office Quick Solutions