How To Create Freeform Sql Report In Microstrategy

Freeform SQL reports Freeform SQL reports can be created on Desktop only. However, these reports can be manipulated and executed from both Desktop and the Web. This section describes the process of creating a Freeform SQL report

from a database
from an Excel file
from a flat file
using a stored procedure

Creating a Freeform SQL report from a database

The process of creating a Freeform SQL report from a database involves the following general steps:

On Desktop, create a database instance for the data source that the Freeform SQL report will run against.
You must create a database instance before defining a Freeform SQL report.
Make the database instance available for Freeform SQL reports (in the Project Configuration Editor, select Project Definition, then Database Instances, and then the database instance name from the Database Instance list).
Create a Freeform SQL report by selecting New from the File menu and then Freeform SQL. The Freeform SQL Editor is displayed.
Access to the Freeform SQL Editor is available onlyto Desktop Designers with the "Use Freeform SQLEditor" privilege and those with the "Create schema objects" Common Privilege.
Below the toolbar, from the database instance drop-down list, select the database instance against which the Freeform SQL report is set to query.
If there is no database instance defined in the metadata, the Freeform SQL Editor cannot be loaded. Instead, the following message is displayed: "There is no database instance in the metadata. Please create the necessary database instance before using Freeform SQL."
Type your SQL statement in the SQL statement panel.
In the Mapping panel, map the columns in the SQL statement to MicroStrategy objects (attribute forms and metrics).
The number of mappings should match the number of columns in the SQL statement.
Insert prompts into the SQL statement, if needed.
Insert security filters, if needed.
Click OK to exit the Freeform SQL Report Editor. The Report Editor is displayed.
Define the Freeform SQL report in the same way as you define a standard report, using features such as formatting, sorting, view filters, thresholds, exporting, and so on.
Save the Freeform SQL report.
You must save the report first before you can run it.
Run the report.

Creating a Freeform SQL report from an Excel file

The Freeform SQL reporting feature also allows you to create reports using data from Excel files. The creation process involves the following steps.

Create a table with the Excel file

Prepare the Excel file.
- Make sure that all the columns with data have proper headers: no space in the header name (for example, Category_ID, not Category ID) and are alphanumeric, starting with alphabets (for example, M2004Q1, not 2004Q1M).
- Make sure that all cells for the ID column have value in them (not empty).
Create a table by doing the following:
- Highlight the rows and columns with the data that you want to create a report with, including the column headers, such as Category_ID and Category_DESC.
  Do not use the column headings (at the top of the Excel spreadsheet, marked as A, B, C...) to select the whole column because doing so may include numerous empty cells with NULL value.
- Type a name for the highlighted part (rows and columns) in the name box, and then press ENTER.
  You may create multiple tables in one Excel file by highlighting different parts of the file and naming them differently.
Save the Excel file with a name.
Make sure that the file is not password-protected.

Set up the data source (ODBC)

From the Control Panel, select Administrative Tools and then Data Sources (ODBC). The ODBC Data Source Administrator dialog box is displayed.
Select the System DSN tab, and then click Add. The Create New Data Source dialog box is displayed.
Select the Microsoft Excel Driver if you are using the Windows platform, and then click Finish. The ODBC Excel Setup dialog box is displayed.
Enter a Data Source Name (DSN) in the space provided.
Click Select Workbook. The Select Workbook dialog box is displayed.
Under Database Name, select the Excel file that you saved and named in Step 1, Prepare the Excel file.
Click OK to close the Select Workbook dialog box and return to the ODBC Excel Setup dialog box.
Click OK to return to the ODBC Data Source Administrator dialog box.
Click OK. The ODBC data source is set up.

You can then use the MicroStrategy ODBC Test Tool to test if data can be retrieved from the table(s) you created from the Excel file.
Make sure to select the correct DSN for the Excel file for the test.

Create a database instance for the Excel file

On MicroStrategy Desktop, create a new database instance that points to the DSN for the Excel file.
You could select any database as the Data Connection Type; however, it is recommended that you use Microsoft Access 7.0.
Make the new database instance available for your Freeform SQL report.

In the Project Configuration dialog box, select Project Definition, then Database Instances, and then choose the DSN for the Excel file from the available database instances list.

Create a Freeform SQL report from the Excel file

From the File menu on Desktop or right-click anywhere in the right panel on Desktop, select New and then Report.
In the New Grid dialog box, select Freeform SQL.
In the Freeform SQL Editor, from the Database Instance drop-down list, select the database instance you created previously (see the "Create a database instance for the Excel file" subsection).
In the SQL Statement panel, type in your SQL query.
Note the following:
- Column names in the SQL statement have to match the column headers in the Excel file.
- Case does not have to match, as long as the column names are correct.
- Do not use the Excel file name as the table name for the "From" clause. Use the table name instead. Remember that the Excel file is the data source that contains the "tables".
In the Mapping panel, map the columns in your SQL statement to attributes and metrics to be used in the MicroStrategy report.
Note the following:
- 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 these columns to exactly 10 objects (including attributes and metrics).
- For each attribute, you must map the ID form at least.
Click OK to close the Freeform SQL Editor. The Report Editor opens in Design view by default.
Format the Freeform SQL report as you do with a standard report.
From the File menu, click Save or Save As.
You must save the report before you can run it. Otherwise, you will see a Desktop message saying that the report "cannot be executed unless it is saved."
In the Save Report As dialog box, enter a name for the report.
Run the Freeform SQL report.

Creating a Freeform SQL report from a text file

The Freeform SQL reporting feature also allows you to create reports using data from text files. The creation process involves the following steps:

Prepare the text file for MicroStrategy use

Make sure that the file type is text file (.txt).
Select a correct delimiter, for example, comma.
Make sure that field (column) names appear in the first row of the file and are delimited.
Save the text file in a folder, which will be used as the data source for MicroStrategy reports.

Set up the data source (ODBC)

From the Control Panel, select Administrative Tools and then Data Sources (ODBC). The ODBC Data Source Administrator dialog box is displayed.
Select the System DSN tab and then click Add. The Create New Data Source dialog box is displayed.
Select DataDirect5.0 Text File (version 5.00.00.42) as your ODBC driver and then click Finish. The ODBC Text Driver Setup dialog box is displayed.
If you do not have this driver on your machine, you need to install it.
On the General tab, enter a Data Source Name (DSN).
In the Database Directory field, provide the path of the directory where you store the text file.
Select Comma as the Default Table Type.
Select the Column Names in First Line check box.
On the Advanced tab, click Define. The Define File dialog box is displayed.
Select the text file you want to define and click Open. The Define Table dialog box is displayed.
In the Table Information section, in the Table text box enter the name of the table for the text file, for example, LU_EMPLOYEE (for LU_EMPLOYEE.txt).
Select the Column Names in First Row check box. It is important that you select this option.
Click Guess to display all the columns contained in this table.
Click OK to return to the Define File dialog box.
Click Cancel to return to the ODBC Test Driver Setup dialog box.
Click Apply and then OK to return to the ODBC Data Source Administrator dialog box.
Click OK. Your data source for the text file is now set up.
(Optional) Use the MicroStrategy ODBC Test Tool to test retrieving data from the table (for the text file).
Make sure to select the correct DSN for the text file.

Create a Freeform SQL report from a text file

From the File menu on Desktop, right-click anywhere in the right panel on Desktop, select New and then Report.
In the New Grid dialog box, select Freeform SQL. The Freeform SQL Editor is displayed.
Below the toolbar, from the Database Instance drop-down list select the database instance you created in the previous procedure, "Create a database instance for the Excel file".
In the SQL Statement panel, type in your SQL query.
Note the following:
- Column names in the SQL statement have to match the field names in the text file.
- Case does not matter, as long as the column names are correct.
In the Mapping panel, map the columns in your SQL statement to attributes and metrics that will be used in the MicroStrategy report.
Note the following:
- 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 the ID form at least.
Click OK to close the Freeform SQL Editor. The Report Editor opens in Design view by default.
From the File menu, click Save or Save As.
You must save the report before you can run it.Otherwise, you will see a Desktop message saying that the report "cannot be executed unless it is saved".
In the Save Report As dialog box, enter a name for the report and click Save.
Run the Freeform SQL report.

Creating a Freeform SQL report using a stored procedure

Creating a Freeform SQL report using a successful stored procedure is similar to creating such a report from a regular database. The tricky part is the mapping of columns. Although the stored procedure itself does not display any column names, you need to know in advance what exact columns will be retrieved once the procedure is executed. Otherwise, it may be difficult for you to do the mapping for the columns.

For example, if you use the following stored procedure:

Execute sp_customer_profit

you may need to map the columns to the following MicroStrategy objects:

Customer ID
Customer DESC
Customer City ID
Customer City DESC
Profit

Below are the general steps you need to take when you use a stored procedure to create a Freeform SQL report.

To use a stored procedure to create a Freeform SQL report

From the File menu on Desktop or right-click anywhere in the right panel on Desktop, select New and then Report.
In the New Grid dialog box, select Freeform SQL. The Freeform SQL Editor is displayed.
Below the toolbar, from the Database Instance drop-down list, select the database instance you created previously. You can refer to the same procedure as in "Create a database instance for the Excel file".
In the SQL Statement panel, type in your stored procedure.
Different databases use different syntax. Make sure you use the correct one. Below is some information on stored procedure execution for some major databases.
In the Mapping panel, map the columns that are supposed to be retrieved by the stored procedure to attributes and metrics that will be used in the MicroStrategy report.
Click OK to close the Freeform SQL Editor. The Report Editor opens in Design view by default.
From the File menu, click Save or Save As.
You must save the report before you can run it. Otherwise, you will see a Desktop message indicating that the report "cannot be executed unless it is saved".
In the Save Report As dialog box, enter a name for the report and click Save.
Run the Freeform SQL report. For more information, please refer to the MicroStrategy online help.