I’m not sure if I’m doing something wrong, but I can’t find any documentation on this issue. I know I can easily resolve this issue by creating a native SQL query, but then I would lose the ability to drill down. Thus I would prefer doing this query using the editor.
I want to add a dashboard filter either through the foreign key in a joined table or based on one of the joined tables in a question.
For example: I have four tables orderlines, orderheads, articles, stores. Orderlines has the foreign key articleId and OrderHeadId, and orderhead have the foreign key storeId. Now I want a question counting orderlines based on two filters: the store name and article name.
If I create a question using orderlines and joining orderheads I can get a dashboard filter for articles -> Name, but I can not use the foreign key in orderheads to join stores -> Name. If I do it the other way around I can use foreign key in orderhead to find stores -> Name as filter, but I can’t use articles -> Name through the foreign key in orderlines. Naturally I also tried joining both stores and articles into the question, but only the primary table and tables connected to it through foreign key are available, not the joined tables.
I’ve also tried creating a native SQL and using that as the source of a question in the editor, but it’s not possible to filter on any values from that query, nor utilize the filters created in the native query.
Am I doing something wrong or is this simply how it works?
I've encountered both but only the first is related to my issue.
Steps to reproduce similar issue from sample dataset.
Create a custom question from Orders, join Product and review.
Add aggregation count by product ID.
Save and add to dashboard
Create dashboard filter to filter on the review rating.
Expected behaviour
Dashboard filter should allow filtering on fields in joined tables. (as it's possible to add such a filter when creating the filter itself)
EDIT: I'm aware that I can fix this particular query by starting from review table instead, but in my real world case I would like to add a filter on date for orders as well, which would not be possible in that case.
See image. The dashboard filter does not allow me to add any filter on the review table.
@mrmiffo
Okay, I can reproduce the problem and cannot find any issues about this.
Seems like it has something to do with “linked tables”, which is kinda joins before 0.33, and handled via Admin > Data Model. I’m saying this, since it shows filter columns from Orders and Products, but not Reviews - and Reviews is not linked to Orders in the Data Model.
Example, if you try to add a ID dashboard filter to this question, then you’re able to link to Orders, Products and Users (which hasn’t been joined).
on the first post of each: