Pivot Tables (Dynamic Analysis)
Pivot Tables are a set of interactive reports, one per business area (Sales, Purchases, Collections, Payroll, Production, Opportunities, and more), where you drag the fields you want to analyze into rows, columns and data, without writing a single formula or SQL query. AdmCloud recalculates the table instantly, and you can filter, reorder and export the result in whatever format you need.
What are they and what are they for?
A pivot table lets you cross the information from an area of your company in many different ways from a single base report: for example, view your sales by customer and by month, and then, with a simple drag, view them by sales rep and by item instead, without having to ask anyone for a new report. Every AdmCloud area with enough data volume has its own pivot table, already pre-configured with that area's most useful fields.
The data you see is read from a read-only copy of your information (a replica, or, in high-volume companies, an analytics data warehouse), separate from the database you work on every day. This keeps a heavy report from affecting the performance of your normal transactions. When that copy has a recent update date (less than 24 hours old), the screen shows it below the export buttons.
How to use it
- Open the pivot table for the area you want to analyze (for example Sales Dynamic Analysis or Payroll Dynamic Analysis). Each one lives inside its own module, next to that area's transactions.
- If your company has multiple Subsidiaries in different currencies, pick a Subsidiary before continuing: AdmCloud won't let you view or export the report without one, since adding up amounts from different currencies would produce a meaningless total.
- Drag the fields shown in the customization panel into one of the table's three areas: the column area, the row area (to choose how you want to categorize the report — by location, business line, month, etc.), or the data area (for the amounts and quantities you want to total).
- Click the filter icon on any field to limit it to certain values (for example, a date range or a specific customer), and change the field order to get a different view of the same report.
- If you want to keep that combination of fields and filters to reuse it later, click Create Copy, give it a name, and AdmCloud will save it as your own variant. Switch between variants (including the factory (Default) variant) from the dropdown list at the top of the screen.
- Once the result is what you need, use the Export menu to download it as Excel, PDF, Word, Html, or an image.
Only a named variant's changes are saved. While you're on the (Default) variant, you can drag and filter freely, but those changes are not kept once you leave the screen. If you want your field arrangement remembered the next time you come in, create a named copy first and work on it: every change you make on a named variant is saved automatically.
Available reports
Each report opens from the section of the module it belongs to, and requires that same area's view permission:
| Report | Module / required permission | What it lets you analyze |
|---|---|---|
| Sales Dynamic Analysis | Sales / Accounts Receivable (Invoices) | Invoiced sales by customer, item, business line, sales rep, territory, subsidiary and period. |
| Dynamic Order Analysis | Sales (Sales Orders) | Sales orders by customer, item and status, to track what's still pending invoicing. |
| Collection Dynamic Analysis | Accounts Receivable (Cash Receipts) | Collections received from your customers, by payment method, customer and period. |
| Purchases Dynamic Analysis | Purchases / Accounts Payable (Vendor Bills) | Registered purchases by vendor, item and period. |
| Account Movements Dynamic Analysis | Accounting (Journal Entries) | Ledger account movements by account, dimension and period. |
| Expenses Dynamic Analysis | Accounting (Journal Entries) | Recorded expenses by account, category and period. |
| Results Dynamic Analysis | Accounting (Journal Entries) | The income and expenses that make up your period's result, with a pivot table's flexibility. |
| Opportunities Dynamic Analysis | CRM (Opportunities) | Your sales pipeline by stage, sales rep, acquisition source and period. |
| Payroll Dynamic Analysis | Payroll | The concepts paid on your payrolls, by employee, department and period. |
| Production Dynamic Analysis | Manufacturing (Finished Product Construction) | What was produced on your production orders, by product, line and period. |
| Raw Materials Dynamic Analysis | Manufacturing (Raw Material Dispatch) | Raw material dispatched to production, by item and period. |
| Work Orders Dynamic Analysis | Customer Service (Work Orders) | Work performed for your customers, by technician, work type and period. |
| Maintenance Orders | Industrial Maintenance (Maintenance Orders) | Maintenance done on your equipment, by equipment, maintenance type and period. |
| Dynamic Analysis of Spare Parts Consumption | Inventory (Dispatches) | Spare parts consumed on your maintenance jobs, by part, equipment and period. |
| Service Requests Dynamic Analysis | Customer Service (Service Requests) | Service requests received from your customers, by technician, type and period. |
Main capabilities
- Three drag areas (columns, rows and data) to combine each report's available fields however you need.
- Value filtering on any field, with most reports defaulting to today's year, month and day so they open showing recent information.
- Personal variants: create, switch between and delete your own saved combinations of fields and filters, without affecting other users' variants or the (Default) one.
- Export the result to Excel (xlsx and xls), PDF, Word, Html or an image, keeping the field arrangement you built on screen.
- If your session has a Subsidiary, Location or sales rep assigned, the reports that require it are automatically filtered to that Subsidiary, Location or sales rep, so each user only sees their own data.
Validations when saving and exporting
- Subsidiary required if your Subsidiaries use different currencies. If you don't pick one, AdmCloud blocks both loading the report and exporting it, with a message explaining why, to avoid totals that mix currencies.
- A variant's name is required and cannot repeat for the same report and the same user; if it already exists, AdmCloud warns you instead of creating a duplicate.
- You can only view, edit or delete your own variants; you cannot modify one created by another user.
Availability, permissions and conditions
- Pivot Tables are an Enterprise plan feature; they are not available on Billing, Start, Basic, Standard, Premium, Premium Plus or Premium Plus Payroll.
- Even on the Enterprise plan, AdmCloud may keep this feature turned off for companies with a very large data volume, because of the performance impact it would have; if a report you expect isn't available, contact support to confirm whether this applies to your account.
- Each of the 15 reports also requires the view permission of the transactional module it belongs to (for example, to see the Payroll Dynamic Analysis you need the Payroll view permission), so a user will only see the reports for the areas they already have access to.
- A report only shows up if its corresponding module is active for your company (for example, Production requires the Manufacturing module active, and Work Orders and Service Requests require the Customer Service module).
Tips and common mistakes
Create one variant per view you use regularly. If you check your sales by sales rep every week and by customer every month, save both combinations as differently named variants; that way you switch between them from the dropdown list instead of rebuilding the report every time.
Don't mistake the update date for real-time data. These reports read from a copy of your information that refreshes periodically, not directly from the transactions you just saved. If you need the exact, up-to-the-minute figure, check the transaction itself or the module's traditional report instead of the pivot table.
If the report takes a moment to recalculate after you drag a field, wait for it to finish before dragging another one: every change triggers a new query against your data.