Yellowfin allows you to create views based on Stored Procedures. Any parameters required for the procedure are presented as filters in reports based on the view. The following process can be used to set up a Source Filter that will pass the current user's User ID as one of the parameters to the procedure.

Creating the Source Filter

This section describes setting up a basic Source Filter. See Source/Access Filter Guide

The first step will be to set up a source filter that contains a list of User IDs. For this to work, you must have the User IDs for your users in the source database. (It is also possible to load a list of User IDs manually if you are not likely to be adding or removing users often.)

1. Edit the Data Source connection, and make sure the Source Filters option is enabled:




2. Click the "Next" arrow to go to the Source Filters page. Add a new Filter, and select "Scheduled SQL Query".

3. Before you can create the source filter, you will need to create a filter type for user ids. Use the box on the right of this page to do this:




Create a filter type with the Code set to USERID and the Name set to User ID.
You can choose different values for the Code and Name if you wish.




4. Now back on the Filters page, enter a SQL query that will return all User IDs from your database. Yellowfin uses this query to keep track of how to filter data for each user. The result set must return four columns: Identifier Type, Identifier ID, Reference Type, Reference Value. The first two columns are used by Yellowfin to identify a Yellowfin user. The last two columns specify the value that Yellowfin will use to filter data for that user (the value returned in the third column must match the Code you gave to the filter type in step 3). The SQL query you enter here will depend on your data schema. In this example, we have a table called Users with a column called User ID:



The User IDs returned by this query must match Yellowfin User IDs for this to work.

Set a refresh schedule, and give the Source Filter a Description, and save it.

5. You will not be able to use the Source Filter until it has some values saved against it, which will not happen until the first time it has been refreshed. You can force it to refresh immediately by editing the filter, and clicking on the Refresh this filter now link at the bottom.




Applying the Source Filter to the Stored Procedure view

This section describes adding the Source Filter created above to a view based on a Stored Procedure. For more information about creating views using Stored Procedures, see the Administrators Guide.

1. Edit the view, and go to the View Fields page. Any parameters required for the Stored Procedure will be listed under the Stored Procedure Filters section.

2. Double-click the parameter you want to pass the User ID to. On the Access tab, set the Access Filter option to User ID.




3. Save the field, and save and activate the view.

Creating a report

See the User Guide for more information on creating reports.

1. Create a new report based on the Stored Procedure view.

2. Yellowfin will automatically create filters for any parameters in your Stored Procedure, except for those that are set up as Source Filters. In this example, we have a Stored Procedure that requires two parameters: UserID and Date. We have set up the UserID parameter to use a Source Filter, so the report only displays the Date parameter as a filter:




When we enter a date and run the report, the UserID parameter is automatically populated for the user running the report (User A in this example).