Freeform SQL gives you full control over accessing data; you can run your query directly against a data warehouse, bypassing the MicroStrategy SQL engine. Using Freeform SQL, you can access data from several ODBC sources such as Excel flat files. After writing the query in the Freeform SQL editor, we create a managed object or map it to an existing attribute. It is good to use Freeform SQL while creating static reports, fetching data from other sources, or using OLTP tables that are not set up for OLAP analysis.
You can have element prompts or security filters on Freeform reports, but you cannot include custom groups, consolidations, transformations, and existing filters on the report.
The steps to create a Freeform SQL report are:
- Select File | New | Report. A new grid dialog box opens.
- Select the Free Sources tab; on this tab, select or create the database instance using the new ODBC source.
- Select create Freeform SQL report and click OK.
- This opens a Freeform SQL editor where we can type our SQL query, create or reuse existing attributes and metrics, and insert a prompt or security filters as needed.
- Once SQL creation completes, click OK and this will close the Freeform SQL editor and open the report editor.
- Select File and Save report.
An example:
Let us describe a few Freeform SQL features.
Prompt controls the data display. Data will only be displayed based on the user input at runtime. In freeform, we can only have the value prompt and element prompt.
There are two options to add a prompt on to the freeform SQL editor:
- Add New Prompt: This allows you to create a new prompt.
- Insert Prompt: This allows you to select an existing prompt.
The different prompts are:
- Value Prompt: For a text value prompt, we need to manually add a single quote to the prompt name, whereas the date and number prompt can be used without a quote
- Element Prompt: For element prompts, if we are using the keyword IN, then we need parentheses around the prompt, but in the case of any other operator, such as >=, =, and so on, we need not have parentheses
Defining an optional prompt:
- In the SQL pane, highlight the line containing the prompt
- Right-click the highlighted part and select Prompt-dependant SQL; this will turn the whole line pink
- At runtime, even if we leave this prompt empty, the report will be executed properly
Security filter provide us with control at the MicroStrategy level and restricts the result set that the user or group can view while executing the report. If the user belongs to a specific group, by default the security role assigned to that user is not applied to the report filter. To apply the security filter, we have to insert it within the SQL statement of the report. Security filters are created based on the project attributes.
The components of security filters are:
- Attribute mapping pane: Contains the column that replaces the attribute form contained in the security filter
- Replacement string: This will be inserted into the SQL pane and replaced by a security filter upon report execution
- Select level: Defines the level of detail the user or group can view upon executing the report
- Ignored attributes: Contains the attribute form that needs to be ignored while creating a security filter:
