Supply SQL for New Reports in the API

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

To use this feature:

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".

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.

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"

Hidden Article Information

Article Author
Exago Development
created 2017-05-15 20:43:55 UTC
updated 2017-05-16 13:16:47 UTC

default, report, sql,
Have more questions? Submit a request