Creating a report to analyze web service data

Using the Freeform SQL Editor you can create a report that integrates web service data into MicroStrategy for reporting and analysis purposes.

Prerequisites

• Freeform SQL reports are created in MicroStrategy Developer. However, these reports can be manipulated and executed from both MicroStrategy Developer and Web. Access to the Freeform SQL Editor is available only to Developer Designers with the Define Freeform SQL Report privilege and those with the Create Schema Objects Common privilege.

To create a report to analyze web service data

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

2. On the ODBC Sources tab, in the Source area, select the database instance created to access web services, as described in Allowing connections to web services in a project.

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

3. Select Use Freeform SQL Editor and click OK. The Freeform SQL Editor opens.

4. In the SQL Statement pane (the top pane on the right), type your XQuery statement. For information on how to develop an XQuery statement, along with a sample XQuery statement, see Using XQuery to retrieve data from a web service.

5. In the Mapping pane (the bottom pane on the right), map the columns in your XQuery statement to attributes and metrics to be used in the report. Follow the requirements listed below when mapping columns in your SQL statement to attributes and metrics:

   • When mapping the columns, it is important that you follow the same sequence of the columns as they appear in the <ColumnHeaders section of the XQuery statement. This ensures that the data is mapped to the correct attributes and metrics.

   • Make sure that the number of mappings is the same as the number of columns in the <ColumnHeaders section of the XQuery statement. For example, if your XQuery statement lists 10 columns from which to retrieve data, you should map these columns to exactly 10 attributes and metrics.

   • You must map a column to the ID form for each attribute that you use in your Freeform SQL report.

   • Freeform SQL reports can map data to metadata objects that are part of the relational project, or metadata objects that are known as managed objects. For information on mapping data in Freeform SQL report to metadata objects, see Mapping columns to metadata objects. The steps in the procedures linked below describe how to map attributes and metrics for Freeform SQL reports that use SQL statements. However, the same general steps apply for mapping attributes and metrics for Freeform SQL reports that use XQuery statements:

   • Mapping a new attribute form to a column.

   • Mapping a new metric to a column.

   • Mapping an existing object to a column.

   • For the example report displayed in Reporting on third-party web services, the four attributes created are City, State, Zip Code, and Area Code.

6. Click OK to close the Freeform SQL Editor. The Report Editor opens.

7. Format and define the Freeform SQL report in the same way as you would a standard report, using features such as sorting, view filters, thresholds, exporting, and so on.

8. From the File menu, click Save As. The Save Report As dialog box opens.

   You must save the report before you can run it, otherwise a message is displayed.

9. Enter a name for the report and click Save.

10. Run the Freeform SQL report.