SQL Queries: Filter Data Using Wildcards

This will require Builder privileges for setup and implementation.

This is a tip I recently learned with the help of a few colleagues here at iDashboards. These queries run against a Microsoft SQL Server data source and the syntax is especially crucial for successful results. The queries will begin simple, and get more complex. Some examples require the use of custom queries, which must be enabled by the administrator. The last example simply uses the traditional Filter dialog when creating charts.

The data contains a list of first names, last names, and phone numbers, for approximately 900 people.  

Custom query to filter the chart for people with the last name of Rose

SELECT first_name, last_name, phone

FROM dbo.K12_Directory

WHERE last_name = 'Rose'

Custom query to filter the chart for people with a last name containing the letters ros

SELECT first_name, last_name, phone

FROM dbo.K12_Directory

WHERE last_name LIKE '%Ros%'

Custom query to filter the chart for people with a last name beginning with the letters ros

SELECT first_name, last_name, phone

FROM dbo.K12_Directory

WHERE last_name LIKE 'Ros%'

Custom query to filter the chart for people with a last name containing the letters provided by an Input Parameter.  The input parameter has a name of last.  In this result set, the user entered ro into the input parameter.

SELECT first_name, last_name, phone

FROM dbo.K12_Directory

WHERE last_name LIKE '%'+${param:last}+'%'

(Without using the custom query) Using Filter to filter the chart for people with a last name containing the letters provided by an Input Parameter.  The input parameter has a name of last.  In this result set, the user entered ro into the input parameter.

• Select the last_name column from the dropdown
• Then from the conditions dropdown select not equal (<>) 
  • Enter this:
    • 'xxx' AND [last_name] LIKE '%'+${param:last}+'%'

For More Information:

• iDashboards Builder Manual 14 Analytics
• SQL Query Help

Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.