I have an ODATA data source that, simplified, has the following columns:
I want to create a dynamic table that shows the fields above in the following order:
Name, FYxxxxBudget, …, FYxxxxBudget, SelectedYearsBudget,
The FYxxxxBudgets are a range where the user can select in which years they are interested and the SelectedYearsBudget column would show a total calculated only for the years included (where LifetimeBudget comes from the data source, but contains the total budget of all years in the table – this can be recreated as a calculated field if it helps).
Name FY2020Budget FY2021Budget FY2022Budget SelectedYearsBudget LifetimeBudget HR $ 200,000 $ 200,000 $ 210,000 $ 610,000 $ 2,000,000 $ 300,000 $ 300,000 $ 310,000 $ 910,000 $ 3,000,000
And so. If the user adds the FY2023Budget column, SelectedYearsBudget changes to reflect the new column in its total. In addition, SelectedYearsBudget will be used as a filter (we are not interested in seeing any element in which SelectedYearsBudget is 0).
Ideally, it should be done as a single dynamic table, but if intermediate dynamic tables are needed, or some VB coding is needed or a display page that extracts values from the table with LOOKUP or other reference functions, that's fine. My current solution is a Frankenstein Monster from all of the above (except VBA) with the problem that the intermediate table is not updated when the underlying filters change.