Use a BR to Generate a SQL Statement within a Data Adapter

You are going to need to create a Dashboard Data Set business rule and reference that in the data adapter. A dashboard det set rule allows you to dynamically generate a SQL string using whatever logic you need and excute against any external data source you have connections to. The rule will return a data table/data set that your data adapter will use. When setting up the adapter, you will want to setup your adapter to be a "Method" command type refencing the business rule created. You can even parametrize the rule and pass arguments via name value pairs when calling the method.

I recommend reviewing the Application Reports(RPTA) marketplace solution for examples. 

Here are the screenshots to support Rick's recommendation:

If you want to view the output in a Dashboard report, you can create a Dashboard Component and add a Data Adapter.