Good day.
I'm on 0.44.3. I have a dashboard with three questions - two use pivot tables to display results by month and week, respectively. The third just returns the raw data which people can then click on the question to download the results, if desired, or scroll through the rows.
The primary table is one of daily sales. It contains the posted date, fiscal period, account number, ISBN and the net totals of units and dollars for that customer, for that ISBN, on that particular day. For example, if Amazon placed several different purchase orders for the same ISBN on the same day, there would only be one record in the daily sales table which would be aggregated totals for all of the orders.
The daily sales points to the customers table via the account number, and the titles table via the ISBN.
The dashboard I've made gives the option for the user to select various attributes from both the customers and titles table to be applied to the results, as well as the date range for sales o be considered. We have history going back to fiscal 2017, so as I write this the daily sales has 693,141 rows, titles 18,371 and customers 104,389.
All works well as long as a person selects attributes in the filters from one of the joined tables (customers or titles). Once a person adds attributes from the second table, the raw rows of data update almost immediately, but the pivot table total questions get lost and eventually time out. All of the attributes that are available are indexed, and we're on MySQL 8.x.
One idea I've had is to create a series of filtering dashboards - first select the time range you want, then use the results of that as the basis for selecting title attributes (if any), then use those results to apply any customer attribute selection on. Not sure how to set all that up, or if it's even a viable way to go.
Any suggestions appreciated as I feel I'm ** that close ** to figuring it out, but am missing something.