MicroStrategy ONE

Freeform SQL Editor

The Freeform SQL Editor is where you create and define your SQL statement to generate a Freeform SQL report.

Accessing the Freeform SQL Editor

You can access the Freeform SQL Editor in one of the following ways, depending on whether you are creating a new Freeform SQL report or editing an existing Freeform SQL report.

Prerequisites

  • You must have the Define Freeform SQL report and Create schema objects privileges to access and use the Freeform SQL Editor.

  • If there is no database instance defined in the metadata, the Freeform SQL Editor cannot be loaded and a message is displayed.

  • Database instances are available for selection only if they have been set up correctly for use with Freeform SQL (select Project Configuration, then Database instances, and select the check box for the database instance from the Database Instance list).

To access the Freeform SQL Editor for a new report

  1. In MicroStrategy Developer, from the File menu, select New, and then choose Report. The New Grid dialog box opens.

  2. On the Freeform Sources tab, in the Source area, select a database instance for the data source to access using Freeform SQL. :

    • If the New Grid dialog box does not open when you select Report, you must modify a MicroStrategy Developer setting. In MicroStrategy Developer, from the Tools menu, select DeveloperPreferences. In the Developer Preferences dialog box that opens, select the Object Template category. The Object Template - General pane opens. Under Show templates for the following objects, select the Report check box. Click OK.

    • While database instances commonly point to a relational database, you can also create Freeform SQL reports from database instances connecting to text filesor Excel files.
       

  3. Select one of the following:

    •  Create Freeform SQL report: To create a report using custom SQL statements.

    • Create Freeform XQuery report: To create a report using custom XQuery statements. You can use XQuery statements to retrieve data from a web service.

    • Create Transaction report: To create a Transaction Services report, which can be used to update data in your various data sources. This option is not available for Intelligent Cubes. For steps to create a Transaction Services report, see the Advanced Reporting Help.

  4. Click OK. The Freeform SQL Editor opens.

To access the Freeform SQL Editor to edit a Freeform SQL report

  1. In MicroStrategy Developer, right-click a Freeform SQL report and select Edit. The Report Editor opens.

  2. If the report is not in Design view, switch the report to Design view.

  3. From the Data menu, select Freeform SQL Definition. The Freeform SQL Editor opens.

Freeform SQL Editor contents

The Freeform SQL Editor is composed of the following interface topics: