Creating a Query Builder report

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

Query Builder allows you to build your own SQL queries against relational databases, text files, and Excel files to report on and analyze the data retrieved.

Prerequisites

• Before you can create a Query Builder report, you must make a database instance available for Query Builder reports. This procedure assumes the database instance is connected to a normal data warehouse. You can also create Query Builder reports from text files or Excel files.

• You must have the Define Query Builder report and Create schema objects privileges to access to the Query Builder Editor.

• If a database instance is not defined in the metadata, the Query Builder Editor cannot be loaded and a message is displayed.

To create a Query Builder report

1. From the File menu select New, and then 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 Query Builder. You can select a database instance only after it has been made available for Query Builder reports. See Database instance selection for Query Builder for instructions.

3. Select Create Query Builder report and click OK. The Query Builder Editor opens.

4. In the Tables and Joins pane (the top pane on the right), add tables or import tables to define your Query Builder reports.

5. Define the joins for your tables in the Tables and Joins pane.

6. Select the columns from the tables for your Query Builder report.

7. Map the columns to the MicroStrategy objects (attributes and metrics) that will be used in the report. Columns that are mapped appear in the Selections pane (the bottom pane on the right).

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

8. Based on your needs, you can perform any of the following tasks:

   • Accessing attributes and metrics in Query Builder reports

   • Mapping a new attribute form in the Selections pane

   • Mapping a new metric in the Selections pane

   • Mapping an existing object in the Selections pane

   • Removing a column selection

   • Renaming an object in the Selections pane

   • Replacing an object in the Selections pane

   • Searching for objects in Freeform SQL reports and Query Builder reports

9. In the Conditions pane (the middle pane on the right), create qualifications that are inserted in the WHERE or HAVING clauses of the SQL query. These qualifications act as the filters of your Query Builder report.

10. You can also create Query Builder security filters in the Conditions pane as needed.

11. After you have finished creating your query, click OK to close the Query Builder Editor. The Report Editor opens in Design view by default. This enables you to define the report as you normally do with a standard MicroStrategy report, using features such as formatting, sorting, attribute form display, and so on.

12. You must save the report before you can run it. From the File menu, click Save. The Save Report As dialog box opens.

    For information on saving a prompted Query Builder report, see Saving a prompted report.

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

14. If you need to modify the data that is returned by a Query Builder report, refer to the following procedure to edit the Query Builder report.

15. Click the Run Report icon to run the Query Builder report.