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?