I am pretty new to Metabase and SQL but have been able to create the reports I need. I have realized though, that my various dashboards all use copied & pasted code… so if I need to change a calculation, I need to go edit all the dashboards that use it. That is inconvenient right now, and it will become impractical at scale.
Is there any way to write a function once and then refer to it in a different Question, passing in the needed variables?
You can add the same question to multiple dashboards, so when you edit the question, then it will be reflected on all dashboards showing it.
But if you have general questions, which just have slight adjustments, then you can make use of a Saved Question for the basis of the other questions:
There’s a request for SQL snippets, which sounds like what you’re after - go and upvote by clicking on the first post:
I do that in some places, but in many cases the question needs customization through a variable.
For example say I have SQL that prints out some metrics if configured with a statement like WHERE partner_id = 1. I give that partner a link to their dashboard.
If I want to give a different partner a link to their own dashboard, I have to make a copy of all of the code and use a new WHERE statement. Right? I cannot let any partner use the handy variable menus to change partner IDs, or they can see someone else’s data.
In this case, what seems right to me is defining the partner report somewhere, and invoking that code in some way that lets me specify that one key variable.
Okay, so you have made your own row-level-security (in the Enterprise Edition it’s called Sandboxing).
If you make a SQL query that is “SELECT * FROM table”, and then create a question based on that Saved Question, where you filter by
partner_id=1, then that should do the same trick.
But that would require that you disable data permissions and only allow view access to the collection, which means the customer wouldn’t be able to create their own questions.
Admin > Permissions > Data and Collections
OK, thanks, I will look at that!