0

SQL and dynamic data sources

Hi,

Is it possible to use dynamic data sources with SQL? I have two databases (PROD & BETA) which require identical dashboards. Instead of creating two separate dashboards with two separate data sources, would it be possible to use a variable and a drop down menu with a single dashboard to switch between PROD and BETA? 

I'd like to place the variable at the beginning of the SQL statement. So instead of USE DataBasePROD and USE DataBaseBETA, there would be USE {prop.DBName}. Is this possible?

 

Regards,

Carlos Kynäslahti

2 comments

  • 1
    Avatar
    Rahul Bura

    Hey Carlos,

    Thanks for reaching out! :) Yes you can absolutely use a variable in your SQL query - note that it would be {props.DBNAME} - props not prop. 

    1.Create a variable that you will use in the dynamic query: DBNAME. Give it a valid value so that the first instance created will be valid.

    2.Then create a NEW (dont use an existing query) SQL query inserting the dynamic property in the format of {props.DBNAME}.

    3.In your Klip you would need a user input control to toggele between the DBNAME values.

    I hope that helps - let us know you need further assistance!

  • 0
    Avatar
    Carlos Kynäslahti

    Thank you for your reply. I managed to get things to work now.

    (Read Edit from bottom!) - I'd like one additional clarification: do I also have to create new Klips from scratch? I tried replacing the data source id with the "Notepad" method described in this article: https://support.klipfolio.com/hc/en-us/articles/115001996268-How-do-I-switch-data-sources-in-a-Klip- but it didn't work. The data seems to come through initially, but the drop down data source selection doesn't update the Klip at all.

    When I created a new Klip switching data sources worked fine, the data was updated. 

    PS. As a clarification to anyone else hoping to use variables with SQL, remember that the {props.DBNAME} can only be used in the input field for the initial database, NOT inside the actual SQL script itself. 

    Edit - I don't know what happened, but my this is suddenly no longer an issue. The Klip somehow just started working, which is confusing to say the least. The only thing I did was go to "Edit" the Klip, save and exit. Great news none the less!

Please sign in to leave a comment.