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

[stribou]

Hi All - 

I am wondering if anyone can point me in the right direction on how to reference a business rule to generate the SQL statement within a data adapter for SQL / External type data adapter?

Any help would be greatly appreciated!

[Big_Rick_CPM · Accepted solution]

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. 

[MelindaLe]

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.