Matrix reports offer a more in depth analysis of your data, allowing you to summarize data in columns as they relate to the rows. These reports provide the option of doing analysis comparisons of your data, such as Month to Date, Year to Date, or Year over Year. This article will dive in on how to create a Monthly Sales by Customer report using the Matrix custom list option within Popdock.
Creating a Monthly Sales by Customer Matrix Report
1. Select Connectors from the left navigation to open the Connectors page.
2. Locate the connector you would like to create your Matrix Report under. This example uses the Dynamics 365 Business Central (Production) connector.
Select the Edit icon to the right of the connector.
3. In the left settings pane, select Lists.
4. Scroll to the bottom of the page to the Custom lists section. To create a new custom list, select Add custom list.
5. In the dropdown, select Matrix.
6. Use the Name field to give your list a name and then press Tab. The Item name, Item singular, and Group fields will populate. Adjust the Item name and Item singular as needed. Select a Group from the dropdown menu.
7. Scroll to the Base list section. Use the available dropdown menus to select the connector and list that will act as the Base list for your Matrix Report. In this example, you will be using the Sales Lines list coming from the Dynamics 365 Business Central connector.
8. Select Columns in the left Settings pane.
9. Select Generate columns. By selecting this option, you are able to generate a set of columns based on a field. In this case, a date field is being used to generate a column for each month within the year.
10. In the Column field, select the Document date.
11. Use the Start date and End date fields to adjust the dates to coincide with the Start and End of the Year.
12.The Date format field dictates how the columns will be broken out within the date range. Select Month to get 12 monthly columns.
13. Using the Summary method and Value field drop down menus, select the value field within each column you want summarized. You can also select the box next to Add summary column to generate a column that summarizes each individual column. In this example, Sum is selected as the Summary method and Amount as the Value field. Additionally, you could also add a summary column and give it the name Total. This will sum together all values across each column for each row.
14. Select Add to generate the columns based off the specifications.
15. A list of columns will now display, one for every month and a total column. Notice that each Month column is filtered for the days that make up the month, which will summarize the Amount that falls within each bucket.
16. Select Add column again.
17. From the dropdown menu, select Label.
18. Use the Name field to give your label column a name. The label column will be used when you generate your rows. Once you have given your label column a name, select Save.
19. Select Rows in the left Settings menu.
20. Select Dynamic row.
21. Give your row a name using the Name field. Using the Field dropdown select which field from the base list will be used to generate the rows. In this example, uses the name Customer and select Sell to customer number from the Field dropdown.
22. Under the Labels section, use the Insert field dropdown to select which fields you want used as the label for every customer. This example uses both the Sell to customer number and Sell to customer name. Both fields will display for each customer in every row.
23. Select Add to generate these rows.
24. The newly created row will be listed.
25. With the columns and rows in place, you can set a restriction on your base list. Select Restrictions on the left settings pane.
26. Under Base list restrictions, select Click here to add a restriction.
27. In the Add base list restriction window, use the available Field, Restriction type, From value, and To value fields to set the restriction so the Document date is between Start of year and End of year.
28. With the base list restriction fields complete, select Add.
29. The resulting Base list restriction will be displayed.
30. With your columns, rows, and base restriction in place, your list is now ready to be used. Navigate back to the Popdock homepage and select Add new tab to populate this list.
If you have questions on how to create a Matrix List, contact support@eonesolutions.com.