Forum

This content is now out of date.

Visit Our Community

Creating a Source Filter using Scheduled SQL Query


When using source filters, you can populate the filter values via an SQL Query.
Using this option, allows you to write a query and have it refresh automatically, so you won?t have to worry about updating any new records manually.


Forum image


When you create an Access Filter using the scheduled query option, you will need to enter a SQL query and schedule it for refresh.
The query will run against the source database, and must return four fields corresponding to the fields ;
-'Identifier Type'
-'Identifier'
-'Reference Type'
-'Reference Id'

(N.B. You will need to use EMAIL as the identifier_type, and not Email Address)

Click on the 'Test Query' link to run the query and report any errors. This will test the SQL syntax, but will not validate the data returned.

Example of valid result set:[code]
EMAIL � �admin@yellowfin.com.au � �Region � �Australia [/code]


'Refresh Type' controls how to treat new records , choosing overwrite all current entries, will simply overwrite all existing entries and insert values returned from the query.
Choosing append to current entries, will only add new records returned from the SQL Query.
'Refresh filter when new users are created' will simply re-run the query if a new Yellowfin user is created.

The Schedule section is where you specify if you would like this SQL query to run automatically.
Setting the frequency to 0 will mean the query needs to be manually opened and saved again in order to update.

Related posts:
- Source/Access Filters Guide
- Setting up a source/access filter on the data source
- Applying the source/access filter to a view
- Applying the source/access filter to a report
- Tutorial: Setting up a new source filter with the Ski-Team database
Hi David,

It would be great, if you could put the information about the different values for the identifier types in the Wiki manual, too.

http://wiki.yellowfin.com.au/display/USER61/Source+Access+Filters

it took me quite a while to figure out, that the SQL needs to return EMAIL and not Email Address as the identifier type, since that is not mentioned in the manual.

Thank you!

Regards,
Ingo
Thanks for the feedback Ingo, I have passed this onto our wiki Admin, and can hopefully get this in, in the very near future.

In order to keep this post short and easy to find, I will be removing your comment in the next few days.

Thanks again.
David

One thing i noticed was that you need to RTrim the 'Reference Type' field value incase you are not hard coding the Reference Type...