Use Filter Widget with a SQL database

[saguiar]

Hello,

I am trying to use the Data Filter widget but I am a bit confused on how to set it up. I would like to filter data that comes from a SQL database, which I am getting by a SQL query, which is written as a service that I have predefined. I am displaying the data using the Grid Widget and I want to able to use the Data Filter to manipulate it (i.e filter by the options the widget itself provides). However, I don't know how to define the Query property in the Data Filter widget. Do I need to specify every single field that would be used by the Data Filter widget in a service that will be defined as the Query property or how do I tell the widget which database should be filtered?

Thanks

[saguiar]

Thanks for your answer. So lets say I have a table called colors, like this:

colorId || colorName

1          || red

2          || blue

3          || yellow

And I would like to filter by using all the possible outcomes the widget has. These are: "Starts With", "Contains", "Ends With", and "Is Exactly".

Therefore, to achieve this I would have to create an SQL query that does the same thing for every single possibility I might have in my database? If that is the case, then I don't see how the widget can be useful! Since you can create different SQL Query services and assigned them to Text Inputs or Radio Buttons and perform different filters to the same data contained , for instance, in a Grid Widget.

[supandey]

Hi Santiago, you can use that data, which you are pulling from the SQL DB, into an Infotable and then you use that in a query service something like this

var params = {
  t: undefined /* INFOTABLE */,
  query: undefined /* QUERY */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Query(params);

You don't have to manually enter each of those fields if you'd use Datashape for that. To build your query you can refer to the documentation on Query Parameter for Query Service

Once you have that query service you can bind that to Query Property for the Data Filter widget

[saguiar]

Thanks! This was helpful. I didn't know the infotable was required as an input parameter as well, but know it makes sense it does.

Thank you!

[qngo]

When you get the data from the DB and bind it to the Grid Widget, you can bind the same data to the Data Filter. The Query is generated by the widget when the user defines the filter in runtime. Then you can select the data available for filter in Configure Data Filter Fields.