Filters function by ‘slicing down’ the data returned to a given report view. Any combination of filters is usable within your dataset query. In general, filter options are generated via the queries you write.
To add a Filter , Choose ADD -> Filter -> [Choose the desired filter type]
Before adding Filters, you may want to create your dataset query/stored procedure containing the necessary parameter(s). This ensures that the [Filter Variable] dropdown field populates.
LIST Filter sample screenshot:
[Name] – The filter’s name (what the end user sees)
[Filter Variable] – The @parameter as defined in the dataset query/stored procedure. When the user selects one or more items in a filter, those items are passed to the Filter Variable as a string (separated by commas in a multi-select filter).
For example: If the filter contains a list of Positions, and the user selects positions: ‘Executive’ and ‘Manager’, then the @Position Filter Variable (as in above screenshot) will be passed the string value of [Executive, Manager]
[Dataset] – Choose the requisite dataset query/stored procedure which contains the list of items that should appear in the filter list.
For example: If you want to filter by Customers, the dataset query/stored procedure should return a unique list of Customers the end user can choose from. The user selection(s) will be mapped to the variable listed in the [Filter Variable] field
** Note – not all filters require a dataset query/stored procedure to populate. For example, Calendar and Numeric Range filters
After creating your filter and filter variables you can use the filter variable to filter report visuals
Example: Bar Chart using @Position variable
You are responsible to parse the filter variable in your report query/stored procedure.
create procedure sp_GetSalary @Position varchar(max) AS Select Name, Salary, Position FROM HR where Position in (select item from dbo.fnsplit(@Position,','))
** The sql dbo.fnsplit is very useful for parsing delimited strings
Adding a List Filter to filter data on user dashboards
Open ‘Setup Portal’, choose [+ADD -> Filter] then select the List Filter. After adding the List filter dataset don’t forget the last step in the [Assign to Dashboards] tab
** Be aware regarding List Filter – if you want the selection to be a single-select instead of the default multi-select, go to Edit-> App Dashboards – Choose your App Dashboard -> Filters tab – > Delete filter from re-add (deselect Multi-Select)
Filter Control Types
MOST COMMON FILTER TYPES
Comma delimited list
Juice, Milk, Fruit
StartDate – EndDate
1/1/2018 – 1/1/2019
MinNumber – MaxNumber
1 – 10
Passes a comma delimited string to your stored procedure/query.
MSFT SQL example: if a parameter named @Group is passed to your query/stored procedure (from a List Filter), you can use the fnSplit function to parse the string.
select Sum(Quantity) FROM Products WHERE ProductName IN (select item from dbo.fnsplit(@Group,','))
How To Parse Filter Strings in your Query:
The fnSplit() function is included as part of the RockDaisy install. For more information on fnSplit() click here.
Date Range Filter (Calendar)
Passes two dates as a string to your stored procedure/query. For example: 10/01/2019 – 10/31/2019
MS SQL example: if a parameter named @DateRange is passed to your query/stored procedure, you can use the fnSplit() function to parse the string.
SELECT @startDate = min(cast(item as date)), @endDate = max(cast(item as date)) FROM dbo.fnSplit(@DateRange,' - ')
Numeric Range Filter
Passes two numbers as a string to your stored procedure/query. For example: 1 – 10
MS SQL example: if a parameter named @NumericRange is passed to your query/stored procedure, you can use the fnSplit() function to parse the string
SELECT @MinNumber = min(cast(item as int)), @MaxNumber = max(cast(item as int)) FROM dbo.fnSplit(@NumericRange,' - ')
Filter Groups are used to group your user’s filters by category
Creating a new Filter Group:
Choose [GROUP OPTION] -> FILTER GROUP
Filtering Data by User/Group
This feature provides a way of limiting data access to certain user(s)/group(s).
For example, this is useful if you have confidential data points within a report which should only display to certain employees, like the CEO, CFO and/or IT administrator.
RockDaisy assigns an internal unique identifier to each user. This identifier is found in the [Custom Field] in the [Edit User] screen. When executing any SQL query/stored procedure from within RockDaisy you have access to this variable and within your query you can perform the necessary logic to filter the data based on the UserID. (For example – ‘do not return salary information if the user variable passed is not in the HR group’)
You can find this variable in the Edit Users screen.
You can use this variable as a parameter to your query
By default, every filter you create is an off-screen filter. Off-Screen Filters and are visible to users within a dashboard’s [REFINE] button.
On-Screen Filters are the same as Off-Screen Filters except that On-Screen Filters are visible in the main user dashboard area. Off-Screen Filters are only visible within the left side [REFINE] button
Off-Screen Filter On-Screen Filter
Supported On-Screen Filter controls
- Table (single select)
To create an On-Screen Filter, choose [Add New Report Visual] -> Select desired filter type
On-Screen Table Filter Example
Choose Table Report -> Choose your dataset
Set the Interaction Type in the [Design Panel] and assign the Filter Variable