If you use flexible forecast per dimension in Business Central, you can build a filter that automatically shows only the dimension values that are still active. This means you avoid scrolling through long lists of dead dimension values that nobody uses anymore.
You build the filter by linking the forecast view to the customer table. You filter on salesperson code first, then pull the global dimension one code from the matching customers and use that as your dimension filter.
The customer table is table number 18. The salesperson code field is field number 29, and the global dimension one code is field number 16. You use these table and field numbers to set up the selection.
The result is that the forecast list shows only the dimension values tied to the customers belonging to the salespeople you filtered on. In the example this narrows the list down to five or six dimension values instead of every value that ever existed.
Why filtering forecast per dimension is useful
If you use the flexible forecast per dimension, you typically use it for campaign handling or some other kind of dimension-based handling. One of the issues here is that the system normally displays items without forecast. That gives you a view that runs through all your global dimension one values, for example all your project values.
In a demo this list looks short. In real life it tends to be very long, because you accumulate many dimension values that effectively die once you are done using them. They stay on the list and clutter the view.
What you really want is a filter that shows only the dimension codes that are still active. Active in this context means the dimension values that belong to customers handled by specific salespeople.
How the salesperson and dimension filter works
The idea is to filter on a couple of salespeople, find all of their customers, and then pick the dimension value from those customers to build the filter. If a customer has a particular project code or dimension value code, you want that code to become part of the filter on the forecast list.
You set this up in the items selection for the forecast view. Inside the forecast per dimension you create a selection. In the example it is called “salesperson and dimension”.
The selection points to the customer table, which is table number 18. On the customer table you first look at field number 29, the salesperson code, and apply the salesperson filter. This finds all the customers that belong to the salespeople you are interested in.
After that, you look at field number 16 in the customer table, the global dimension one code. You match that against your code, which is the dimension value in the dimension value table.
The result of the automatic dimension filter
When you run the “salesperson and dimension” selection, the system finds all the relevant customers and returns the dimension values tied to them. In the example this comes down to five or six different dimension values instead of the full, unmanageable list.
This is a straightforward way to build complex automatic dimension filters. The filter itself is simple to set up, but it saves you from working through long lists of dead dimension values every time you forecast.
Q&A
What problem does the forecast per dimension filter solve?
When you display items without forecast, the view runs through all your global dimension one values. In real life this list gets very long because dimension values accumulate and stay on the list even after you stop using them. The filter limits the view to only the dimension values that are still active.
Which table and fields do you use to build the filter?
You use the customer table, which is table number 18. The salesperson code is field number 29, and the global dimension one code is field number 16.
How does the filter decide which dimension values are active?
You filter the customer table on salesperson code to find all customers belonging to the selected salespeople. Then you take the global dimension one code from those customers and match it against the dimension value table. Only those dimension values appear in the forecast view.
Where do you set up the selection?
You create the selection in the items view for forecast per dimension. In the example it is named “salesperson and dimension”.