FILTERS

Overview

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]

Filter Variables

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.

For Example:

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

FORMAT

EXAMPLE

LIST

Comma delimited list

Juice, Milk, Fruit

Date Range

StartDate – EndDate

1/1/2018 – 1/1/2019

Numeric Range

MinNumber – MaxNumber

1 – 10

LIST Filter
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

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

Off-Screen Filters

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

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)
  • Calendar

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