MaxB Posted October 15 Share Posted October 15 (edited) Hello everyone, I’m looking to provide standardized information links to our users in Spotfire. My goal is to keep database load and RAM usage under control by loading only the columns required for specific use cases. To achieve this, I need a way to parameterize the information link, allowing flexibility in column selection. I have an idea of how to implement this, but I cant get it working... Here’s what I have in mind: Parameterized Information Links Steps: 1. Create a Parameter: In the Information Designer, create a parameter to represent the list of columns the user wants to query. For example, you can name this parameter SelectedColumns. 2. Use the Parameter in SQL: Modify your SQL query to dynamically include or exclude columns based on the value of the SelectedColumns parameter. A basic SQL template might look like this: SELECT ?SelectedColumns FROM MyTable WHERE <contitions> • If the user selects specific columns, those will be substituted in place of ?SelectedColumns, allowing only the chosen columns to be queried. • SelectedColumns has a default value in case the user doesn’t specify any columns. Using Spotfire Modifications: Dynamic Properties To make the selection of columns more user-friendly, I would use Property Controls to allow users to choose from a predefined list of columns. Steps: 1. Create a Document Property: Set up a multi-select list property in Spotfire (let’s call it ColumnSelection) that contains the list of available columns the user can choose from. 2. Bind the Document Property to the Information Link: Link this ColumnSelection document property to the SelectedColumns parameter you defined in the Information Designer. Any insights or suggestions on how to get this approach working would be greatly appreciated! Thanks in advance for your help! Edited October 15 by MaxB Correction of syntax Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted October 16 Share Posted October 16 Hi Max, Can you share your dxp file, with the failing set up? Also, can you tell us what isn't working for you? Kind regards, David Link to comment Share on other sites More sharing options...
MaxB Posted October 16 Author Share Posted October 16 Hi David, thanks for replying! I tried several different syntaxes of the column names but I always got this error: Could not load on-demand data for table 'dynamic_column_selection_test' Failed to create DataTable at Spotfire.Dxp.Data.ColumnFactory.CreateColumns(DataRowReader reader, String documentTitleForOrigin, IDataPropertyContainer defaultProperties, DataPropertyRegistry dataPropertyRegistry, GlobalMethodRegistry globalMethodRegistry, CxxSession session, Boolean addNewProperties, PartialDataLoadReport loadReport, ResultProperties resultProperties, PendingViewRequestsManager pendingViewRequestsManager, Boolean mangleColumnNames) at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.GetOutputResults(DataFunctionInvocation functionInvocation, Dictionary`2 pendingViewRequestsManagers) at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.<>c__DisplayClass60_0.<ExecuteFunction>b__1() at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation) at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.ExecuteFunction(DataFunctionInvocation functionInvocation, Dictionary`2 pendingViewRequestsManagers) dynamic_column_selection_test.dxp Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted October 16 Share Posted October 16 Hi Max, I have tried to make it work, but I'm afraid it's not possible. I have discussed this also with my colleagues. The dynamic generation of columns is not possible for 2 reasons: - the information links have their predefined list of columns; - the values send to the SQL statement are in string format. So whenever you select a number of columns that is similar to the list of columns in the information link (let's say you've selected city and state), you'll get a result. But this result means that all lines are filled with the name of the selected columns instead of their values (so value 'city' will be in the city column and value 'state' will be in the state column). This is due to the string format that is being injected into the SQL. Whenever you select a number of columns that isn't equal to the listed columns in the information link, Spotfire will throw an error since it expected the number of columns defined in the information link. As we feel this could be a useful enhancement, could you create a request for this on the Ideas portal? Kind regards, David 1 Link to comment Share on other sites More sharing options...
MaxB Posted October 16 Author Share Posted October 16 Hi David, thanks for looking into that topic! I just created a request in the ideas portal: https://ideas.spotfire.com/ideas/TS-I-9745 Everyone who wants that feature implemented please vote! :) Best regards, Max Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted October 16 Share Posted October 16 Thanks Max, I voted for the idea! 1 Link to comment Share on other sites More sharing options...
So Ma Posted October 16 Share Posted October 16 for the same reason I wanted to do so but for a data connection but this is not possible there either. There is an idea for that open https://ideas.spotfire.com/ideas/TS-I-8241 As a work around you can write a Python script used as a data function to connect to your DB with a dynamic select query but the issue is that credentials for connecting to the DB will be visible from the script! 1 Link to comment Share on other sites More sharing options...
Solution David Boot-Olazabal Posted October 18 Solution Share Posted October 18 Hi Max, So Ma, Another way of accomplishing a dynamic query, is to use a stored procedure in Spotfire. That stored procedure is being created on your database side, which holds a prompt. See below example of such a procedure (from SQL server): CREATE OR ALTER PROCEDURE GETDYNAMICCOLUMNS @ColumnFilter as Varchar(100) AS BEGIN DECLARE @Template AS Varchar(max) SET @Template = 'SELECT {ColumnList} FROM [sales].[stores]' SET @Template = REPLACE(@Template, '{ColumnList}', @ColumnFilter) EXEC (@Template) END GO In the example above, I use a table called sales.stores. Once you have your procedure, you can 'catch' it in the Information Designer (make sure you use Query as Procedure type and set the Prompt to 'Single Value': You can now create an Information Link, based on this procedure: When you then open the Information Link, you're prompted to enter values. These values are your column names, separated by comma to inject into your SQL statement properly: And then display the result: You still may need to figure out how to automate the prompt part from a document property (if possible at all), but this creates a dynamic query to your database as you get prompted each time you reload the data set (just check this box for the data table): Kind regards, David 1 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now