Understanding Datasets in AstraReports

A dataset is a collection of tables and their relationships that form the data foundation for your report. When you create a report, you select a dataset. The dataset tells AstraReports which tables are available for your report to pull data from and how those tables relate to each other.

AstraReports comes with pre-configured datasets for each major Acumatica module—General Ledger, Accounts Receivable, Accounts Payable, Inventory, Projects, and others. These pre-configured datasets include the most commonly used tables and their standard relationships. For most reports you’ll build, you’ll start with one of these pre-configured datasets.

Table Relationships Explained

Tables in a relational database don’t exist in isolation. They connect through relationships. For example, the AR_Customer table has a relationship with the AR_Invoice table. Each invoice belongs to exactly one customer. When you build a report that shows customer information alongside their invoices, you’re leveraging this relationship.

In AstraReports, relationships are represented visually in the dataset diagram. You’ll see boxes representing tables and lines connecting them showing the relationships. If the line has a single endpoint on one side and multiple on the other, that represents a one-to-many relationship (one customer, many invoices). Some relationships are one-to-one, where each row in one table corresponds to exactly one row in another table.

Understanding these relationships is important because they affect how your report data is structured. If you’re not careful about how you join tables, you might accidentally create a report that shows duplicated data or incorrect totals.

Selecting Tables for Your Report

When you start building a report, you begin by selecting a dataset, then choosing which tables you want to pull from that dataset. You don’t have to use all tables in a dataset—only the ones your report needs.

Start with your “primary” table—the main entity your report is about. If you’re building a customer aging report, the primary table is AR_Customer. If you’re building an inventory valuation report, the primary table is IN_InventoryItem.

Once you’ve selected your primary table, AstraReports shows you which related tables you can add. If you’re working from the AR_Customer table, you can add AR_Invoice because there’s a direct relationship. You can also add AR_AppliedPayment because it relates through AR_Invoice, creating a chain: Customer → Invoice → Applied Payment.

Common Table Relationships in Acumatica

Customer-Invoice Relationship: Each AR_Invoice relates to exactly one AR_Customer. When you report on invoices, you typically include customer information (customer name, ID) even though that information comes from a different table.

Invoice-Detail Relationship: Each AR_InvoiceLine relates to exactly one AR_Invoice. An invoice typically has multiple line items (each line is a detail record). When you build an invoice detail report, you show both invoice-level data (like the invoice date) and line-level data (like item descriptions and amounts).

Invoice-Payment Relationship: AR_AppliedPayment records track which payments have been applied to which invoices. Each applied payment relates to both an invoice and a payment. This relationship lets you build reports showing which invoices have been paid, partially paid, or remain unpaid.

Item-Warehouse Relationship: IN_InventoryItem relates to IN_InventorySubaccount through warehouse and location. This relationship lets you report on inventory quantities by warehouse or by location within a warehouse.

Joining Tables Without Direct Relationships

Sometimes you need to combine data from tables that don’t have a direct relationship in Acumatica’s data model. For example, you might want to relate AR_Invoice data to GL_Account data (to show which GL accounts each invoice was posted to). These tables might not have a pre-configured relationship.

In the AstraReports dataset designer, you can create custom joins between tables. A join tells the system how to match rows from one table to rows in another table. For instance, you might join on a common field like the account number or transaction ID.

Custom joins require careful thought. You need to understand which fields are valid join keys—which fields contain matching data that correctly links the tables. Joining on the wrong field creates incorrect, duplicated, or missing data in your report.

If you’re not sure how to join two tables, consult your system administrator or an AstraReports consultant. A poorly designed join can produce misleading reports that appear correct but contain subtle errors in the data.

Handling One-to-Many Relationships in Reports

One-to-many relationships require special handling in reports. Consider a report that shows each customer and their invoice count. The AR_Customer table has one row per customer. The AR_Invoice table has many rows (one per invoice) because customers have multiple invoices.

If you’re not careful, you might accidentally show customer data repeated once for each invoice, inflating the apparent number of customers. To avoid this, use appropriate grouping and aggregation. Group by customer and count invoices within each group. This produces a correct report where each customer appears once with their invoice count.

This is a common source of report errors, so be mindful of one-to-many relationships when designing your report structure.

Creating Custom Datasets

While pre-configured datasets cover most scenarios, sometimes you need to create a custom dataset tailored to your specific needs. Custom datasets are typically created by power users or report developers, not by casual report builders.

To create a custom dataset, navigate to Administration > Datasets in AstraReports. Click “Create New Dataset.” You’ll be guided through selecting tables and defining their relationships. You can also define calculated fields—fields that combine data from multiple tables or apply formulas to existing fields.

Once you create a custom dataset, it’s available for all future reports. This is useful if you often build reports that need the same combination of tables. Create the dataset once, then reuse it across multiple reports.

Tips for Working with Complex Data Relationships

Document your datasets and their relationships. If you have a custom dataset with non-obvious joins, add a description explaining why those joins are necessary. This helps future report developers understand your design choices.

Test your relationships with sample data. Build a test report using your dataset and verify that the data looks correct before building mission-critical reports. Look for duplicates, missing values, and incorrect totals.

Start simple and add complexity gradually. Build a basic report with a single table, verify it works correctly, then add a related table. Each step should be tested before you add the next table.

If you’re struggling with a complex relationship, draw a diagram of the tables and how they relate. Seeing the relationships visually often clarifies how to structure your data retrieval.