Change Data Table Automatically

[Kenneth Richardson]

We want to create a dashboard that references a user-defined query to build a data table that is created by a stored procedure in the SQL Server database. The purpose is to pull only the data needed as a subset for the analysis without having to read the entire original table that has millions of rows and filtering on them. If it was only a single parameter value, something like a promt would work, but we are having to a hierarchy of values as an array of systems, sub systems, components and sub components. The string of values is too long to use a previous method using a configuration block, which I believe can only be used to filter and not limit the data.

Since multiple users may run the query for their own projects, we want to ensure that the user only analyzes the subset of data they just querried and not someone else's. We thought we could just name the table with the user ID as a possible solution (Data Table=UserID_MyAnalysis), but how do we get the dashboard to know which table needs to be used when the user opens the dashboard Is there a better solution

[Fabian Duerr]

You could add a script that reads the UserID when the analysis is being opened and store this ID to a document property.  Then you could define what to do in your data function/query with an IF statement by setting a data table name (...) or by using the ID directly.

 

Maybe something like:

 

if (UserID = ABC){dt.name <- ...) 

 

if (UserID = ABD){dt.name <- ...) 

 

Or like this

 

dt.name <- paste0(UserID, "...", collapse = "")

 

I hope this was helpful

[Jerald Rodbell]

I need clarification on how to explicitly implement.

 

Where is this implemented, using what script language,

 

to have a shared dashboard that can change datasource based on a param. 

 

the sql table name syntax would be TempTable_9999

 

I can run a sql proc to get data from an existing table by that name based on a sql param

 

I dont understand how to pass param in url tablename=TempTable_9999

 

and run sql with a param passed returning data for Spotfie ingestion/use

 

 

 

 

[Fabian Duerr]

You can use a IronPython script to get the user and store it to document property (here "user"). Create this doc prop first and then populate it with the script:

 

from System.Threading import Thread
Document.Properties["user"] = Thread.CurrentPrincipal.Identity.Name;

 

There is some information on how to trigger this script when the document is opened:

 

https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-44163

 

How do you extract your data I'm not an expert here. I can only help further if you are using an R data function to send the query.