Now available in v2017.1 is the much-requested ability to generate SQL for new reports using the API.

After creating an API object, but before calling GetUrlParamString(), you can supply custom SQL in the ApiReportOptions class. This is only valid if the Api.Action is NewAdvancedReport or NewExpressView (v2017.1.2+).

wrApiReportOptions reportOptions = api.ApiReportOptions;
reportOptions.DataSourceId = 0;
reportOptions.EntityName = "SomeEntityName";
reportOptions.SqlStmt = "SELECT * FROM Product";
reportOptions.Keys = "OrderId,ProductId";

• DataSourceId must reference an existing data source from the config file.
• EntityName may be any name other than existing entities specified in the config file.
• Keys is a comma delimited list of columns which provide a unique key.

If this is set, the Categories page is suppressed in the New Report Wizard and Report Designer. In the ExpressView Designer, the Data Objects Pane displays only the custom category, and the Settings > Report Info menu is disabled.

Note

Only one custom SQL object is supported by this method.

To additionally suppress the Sorts or Filters pages:

api.SuppressFiltersInterface = {true|false}
api.SuppressSortsInterface = {true|false}

To set the report name in the API (this suppresses the Name page):

api.DefaultReportName = "ReportName"

Report Schema

The XML schema for reports has been extended to support this functionality. The <apireportoptions> node has been added to <report>:

<apireportoptions>
  <entityname>SomeEntityName</entityname>
  <sqlstmt>SELECT * FROM Product</sqlstmt>
  <keys>OrderId,ProductId</keys>
  <datasourceid>0</datasourceid>
</apireportoptions>

The following nodes have been added to <main>:

<suppressfiltersinterface>True|False</suppressfiltersinterface>
<suppresssortsinterface>True|False</suppresssortsinterface>