Creating a Freeform SQL report

Unlike standard reports which can be created in the Report Editor by including attributes, metrics, and other objects on the report, Freeform SQL reports, require you to first define a valid SQL statement. You must also map objects to the data returned by your SQL statement. Once these tasks are complete, you can begin reporting and analyzing the data returned from your SQL statement with the Report Editor. Refer to the procedure below for steps to create a Freeform SQL report.

Prerequisites

• You must have the Use Freeform SQL Editor 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 create a Freeform SQL 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.

   While database instances commonly point to a relational database, you can also create Freeform SQL reports from database instances connecting to text files or 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.

5. In the top-right pane, type your SQL query. Alternatively, you may use an existing stored procedure.

6. In the bottom-right pane, map the columns of the SQL statement to the MicroStrategy objects (attributes and metrics) that will be used in the report.

   When mapping the columns, it is important that you follow the same sequence of the columns as they appear in the SQL statement. Doing otherwise will cause the report to fail.

   Make sure that the number of mappings is the same as the number of columns in the SQL statement. For example, if your SQL statement lists 10 columns from which to retrieve data, you should map them to exactly 10 objects (including attributes and metrics).

   For each attribute, you must map a column to the ID form.

7. Based on your needs, you may need to perform the following tasks:

   • Accessing attributes and metrics in Freeform SQL reports

   • Adding a new attribute form to the Mapping pane

   • Adding a new metric to the Mapping pane

   • Adding an existing object to the Mapping pane

   • Adding a new prompt to the SQL statement

   • Adding an existing prompt to the SQL statement

   • Setting prompt-dependent SQL for optional prompts

   • Creating a security filter

   • Inserting an object to the Mapping pane

   • Removing an object from the Mapping pane

   • Renaming an object in the Mapping pane

   • Replacing an object in the Mapping pane

   • Searching for objects in Freeform SQL reports

   • Showing/Hiding Object Browser

   • Sorting in Freeform SQL reports

8. After you have finished creating a Freeform SQL report in the Freeform SQL Editor, click OK to close the Freeform SQL Editor. The Report Editor opens in Design view by default (see Using Report Editor for Freeform SQL reports). This is where you can define the report as you normally do with a standard MicroStrategy report, using features such as formatting, sorting, attribute form display, and so on.

9. You must save the report before you run it. Otherwise, the report cannot be run. From the File menu, click Save As. The Save Report As dialog box opens.

   For information on saving a prompted Freeform SQL report, see Saving a prompted report.

10. Type a name for the report, and click Save. You are returned to the Report Editor.

11. If you need to modify the data that is returned by a Freeform SQL report, refer to the following procedure to edit the Freeform SQL report.

12. Run the Freeform SQL report.

    As for a standard report, you can switch the view to Grid, Graph, Grid/Graph, or SQL by selecting the options from the View menu or by using the icons on the toolbar.