In this blog, you will learn an advanced filtering technique with the ‘Data Source Column’ method.
Image 1 below shows a sales dashboard with 5 input parameters as follows:
• Start Date
• End Date
• Product Line 1
• Product Line 2
• Product Line 3
The three controls for Product Lines allow a user to either select ‘ALL’ in the first, or up to 3 distinct values to filter the dashboard. If you are a well experienced iDashboards user, you may notice a typical roadblock here, “how can I have an ‘and’ as well as an ‘or’ operator in the ‘WHERE’ clause using the data source column method?”
The standard method when creating input parameters for filtering purposes is that each input parameter results in one filter referencing that input parameter. So, assuming this to be true and knowing that each of the charts on this dashboard have 5 parameters, we can conclude that there are at least 5 filters.
Image 2 shows our list of input parameters on the charts, but if we take a look at the filters, we would expect to see at least 5 filters including the 5 for the input parameters.
In looking at Image 3, this is not the case. Rather, there is only one filter which includes reference to each on the 5 input parameters. The ‘Filter’ interface is simply used to create the ‘WHERE’ clause in the SQL Query that iDashboards is configuring for the chart. What this means is that you can simply use one filter to configure more complex filter criteria than simply creating a unique filter for each input parameter and also can combine ‘And’ as well as ‘Or’ operators without needing to use custom SQL.