I'm trying to use metabase to call a stored procedure and perform pivoting and other functions.
I have initialized 2 variables namely 'from date' and 'to date'. I pass these variables as parameters to the stored procedure. When I execute it, I successfully get the result. But when I try to pivot it , it shows the error: Pivot tables can only be used with aggregated queries.
I had an idea if I could convert the SQL query to a model, I would be able to pivot it. With static date ranges, it is possible. But when I introduced variables in the query editor and I try to convert it to a model it shows the error: Variables in models aren't supported yet
To solve this, just remove the variables in this question and try again. (It's okay to use SQL snippets or reference the results of another saved question in your query.)
Note: Specifying the date range is necessary as the database is huge and I would like to keep the runtime to a minimum.
Please advice me on how to proceed.
Thanks in advance.
I believe you could build a question (with the query builder, not native) on top of the native SQL model (here the model would retrieve the whole table), and add the date filters on the question.
You can check out our docs for more details on pivoting over native queries:
Hi Marcos, like I said, the database is huge and I don't want to import the entire data and then later filter it. Plus the method you suggested is a 2 step process. I would like it to be a single step. It would be much simpler if I could use sql variables in models.
Could you share what DB engine you're using here?
I believe, yes, it will run a potentially large query when you're editing the model, but it should just bring the first 2000 rows, which shouldn't really cause a lot of trouble (it's only going to run a SELECT, not going to import any data into Metabase itself, as long as Persistence isn't enabled).
Then when you add the filters on the question, it will add the WHERE clause (you can add a LIMIT to the question too just to be safe) and should be fine from that point onward. Let me know if I'm missing something here, though