Load Columns on demand

[Michele Scarpa]

Hello,

I am going to create a dashboard that uses a table with a huge amount of both columns and rows. Now I load data on demand filtering rows according to markers (like "select * from my_table where column_1 = my_value"). In order to manage all these columns i wold like to load also columns on demand according to some parameters (like "select column_1, column_2 from my_table where column_1 = my_value" where columns change according to what I whis to visualize).

Is there a way to do so

Thank you for your support.

Michele

[Khushboo Rabadia]

Loading columns on demandwould be an enhancement request which you can register or vote for in Ideas Portal:

https://ideas.tibco.com

It is not feasible directly in Spotfire, but somehow if you make it possible using stored procedure in sql as per suggestions in below reference, you can try referencing that procedure in dataconnection. Then apply on demand rows functionality on that data table having data connection

https://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-i...

This is not tested but you can give it a try.

[Michele Scarpa]

Hello, thanks Khushboo for your reply.

I found a work around and I am sharing it as it migh be helpfull for someone else.

In the database I used the "unnest" function to create a table with 3 columns containing:

 

an ID that is an attribute to understand from wich row of original table the value is from.

the value

the column name.

 

For example:

select

ID,

unnest(array["Column 1", "Column 2", "Column 3"...]) AS value,

unnest(array['Column 1', 'Column 2', 'Column 3'...]) AS original_column

FROM my_origina_table

Then I loaded this new table on demand using a filter on ID to filter original rows and on original_column to filter the columns.

Done this I used the pivot function(https://docs.tibco.com/pub/spotfire/7.0.1/doc/html/data/data_details_on_pivot_data.htm) to re-create the original shape of the table.

Of course this is not as fast as what I was looking for but it is helpfull anyway.

[Khushboo Rabadia]

Thanks michele for sharing the workaround. :)