How can we help?

New post
0

SQL and dynamic data sources

Avatar
Carlos Kynäslahti
None
Follow

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

Date Votes

5 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!

  • 0
    Avatar
    Matt McFedries

    There still appears to be an issue, maybe if you don't create the dynamic source first.

    My process was to create a PostgreSQL query and Klips with static variables first to make sure they work, then edit the datasources to add dynamic variables.

    However this didn't work as expected i.e. the Klips didn't refresh when the dynamic variable values changed.

    So I duplicated the datasources, hoping this would make them NEW (as mentioned by Rahul above). But this didn't fix the issue.

    So then I tried editing the Klips, added in the new duplicated datasources in new tables, which then worked once I'd saved the Klip.

    But the odd thing is, then my first 'edited' datasources started to work also.

    So it seems that editing the Klip (and maybe adding an additional datasource to it) forces the Klip to refresh and then the dynamic datasources work.

    Would be great to know if anyone else has experienced this and has a reliable solution. In the future I think I'll start with dynamic datasources from the beginning to save all the hassle!

  • 0
    Avatar
    Meggan King

    Hi Matt,

    You can't easily change a static data source to dynamic. You must first create the dynamic data source, add it to the Klip you wish to build, and then go from there. In your description, what happened is the static data source being switched to dynamic did not put the required variable values into the Klip schema. These values tell the Klip your data source is dynamic. So the Klip has no idea how to use the dynamic data source, or change the variable - or even assign it a default value.

    When you duplicated the dynamic data source, and then added it to the Klip, the data source added the missing dynamic variables to the Klip schema. Since both data sources used the same variable name, both now magically work. 

     

    In your scenario, there is a brute force way to fix a data source you've changed from static to dynamic - and that is to manually add in the dynamic variables to the Klip schema. This is generally fine if only one Klip has been built, but it is not a practical solution if you've already built many Klips and then change your data sources to dynamic. 

     

    Another option would have been to remove all references to the data source, and then add it back in. 

    Your last point is our best practice - always start with a dynamic data source from the start, or if you start with static and then switch to dynamic, do it before any Klips have been created. 

    You can read more here: https://support.klipfolio.com/hc/en-us/articles/216183237-BETA-Working-with-dynamic-data-sources

     

    Thanks

    Meggan

  • 0
    Avatar
    Arttu Piipponen

    Also confirming my experience: I set up the variable (text field + button) and the klip, then went on to modify the SQL datasource to have WHERE column = '{props.variable}'

    No updates through refresh of the page or the field.

    After reading this thread, I added another datasource to the klip => dynamic query started working

Please sign in to leave a comment.