I believe I have found a bug in the way parameters are handled in custom SQL requests.
Here is the context :
I have 2 tables stored into 2 different datasources.
This is a 1 to many relationship, lets call the first table “Person” and the second table “Car”.
I get both these tables with custom SQL
SELECT id, name
from Person
SELECT ownerId, brand, ...
from Car
I tried to join both tables but it became very much aparent that this join would create millions of rows in the new Spice, as the request couldn’t be completed due to a timeout.
The objective is that the user would be able to select a person and see all the cars he posseses. So, to takle the problem of Data ingestion, I wanted to filter the users by their name using a dataset parameter that the users of the dashboard would be able to edit.
So I change the Custom SQL request for Person to include the parameter that I just created :
SELECT id, name
from Person
where name = <<$personName>>
But then I get this error: “Missing parameters used as part of SQL Query”, even though I definitely created the parameter.
I then creates a new dataset with only the table Person and the parameter “personName” and it was fine. But as soon as I added the new datasource, I got the same error message.
Did any of you get the same error at some point ?
Did you find workarounds ?
Hi @Dams - Welcome to AWS QuickSight community and thanks for posting the question. As per my understanding when you have a parameter in a table and join it with another table you are getting error but the parameter with one table is working fine. You are not able use SPICE due to high volume of data. To replicate this symptom, is it possible to submit a ticket to AWS Customer support team so that they can analyze it and get back to you. To raise the request please follow the link - Creating support cases and case management - AWS Support
Hi
Thank you for your answer.
I am not the owner of the AWS account, I only have access to QuickSight with my account, so I do not believe that I can report the issue by myself.
Am I correct ?
I am also experiencing this issue. I have two custom direct queries I am joining together and when I try to use dataset parameters I get the same issue saying “CUSTOMSQL_QUERY_WITH_MISSING_PARAMETERS sourceErrorMessage: Parameter in custom sql missing in parameter set”
When I have just the one custom direct query I don’t get an error.
I have found a workaround for my problem.
The original objective was to select select a person and then being able to see all the cars he owns.
I order to do that, I created 2 different datasets in Direct query : 1 for the Person and the other for Car.
Then, in the visuals, I created a table to display the Person’s name and id (hidden).
I then added a filter action on this table that filters my cars visuals with the field “id” from Person.
I made sure that the field mapping was correct and voila.
I am now trying to see if I can select several persons at a time on a table, and I have found this very interesting workaround :
Hi, I’m having the same problem: CUSTOMSQL_QUERY_WITH_MISSING_PARAMETERS When creating a data set:
First I create a parameter pIdClient.
Then I add data from a custom query:
select id_client, name_client from tb_client where id_client = <<$pIdClient>>
When I add more data to join it with the previous data and thus have my new set of data parameterized, I get the indicated error.
Sadly it doesn’t seem like SPICE supports this feature right now.
It kinda makes sense when you think about how SPICES work and how they are supposed to be used. In order to fix the problem there are several workarounds.
Have you tried my solution ?
Instead of creating 1 very big SPICE, create 2 different datasets with client being in direct query(+ custom SQL if you want). Then you don’t even need to filter with the parameter, as the join will not cause any data ingestion problems.
Then, on your visualisation, create a table with all the “name_client” and “id_client” hidden.
Once this is done, you can create an action that will filter any other graphs with the right id_client.
