Creating Freeform SQL reports using stored procedures

In addition to SQL statements, the Freeform SQL feature also allows you to use existing stored procedures to retrieve data from the data source. It is recommended that you test these stored procedures before applying them for Freeform SQL reports.

While using a stored procedure, it is important that you know what exact data the stored procedure is supposed to retrieve, since this information is essential in building a Freeform SQL report. Specifically, you need to know the number of columns, names of the columns, and their data types, all of which are necessary for mapping the columns to MicroStrategy objects, as described in Mapping columns to metadata objects.

For example, if you execute a stored procedure named StoredProcedure_Customer_Profile, you may need to map these columns in the Freeform SQL Editor: CustomerRegion_ID, CustomerRegion_DESC, CustomerCity_ID, CustomerCity_DESC, Profit.

You could use the ODBC Test Tool to test the validity of the procedure before creating the Freeform SQL report.

Below is some information on stored procedure execution for some major databases:

• DB2

  call stored_procedure_name with DB2 ODBC driver

  The stored procedure must have been created indicating that it has a result set. The results are sent back to the client.

• Oracle

  • call stored_procedure_name() with MicroStrategy ODBC driver
  • {call stored_procedure_name} with Oracle ODBC driver
  • The stored procedure must return the results into a table that can subsequently be selected.

• SQL Server

  exec stored_procedure_name with SQL Server ODBC driver

  The stored procedure returns the data to the client. No particular precaution is needed.