MicroStrategy ONE
Specifying Custom SQL Statements
The SQL Statements tab of the Report Data Mart Setup dialog box allows you to enter custom SQL statements for a data mart table. You can add any pre-creation or post creation SQL statements to your data mart table definition.
The SQL Statements tab is only visible if you have the Use SQL Statements tab in Datamart/Bulk Export editors privilege in the Developer privileges.
To access the SQL Statements tab, open the data mart report in the Report Editor. From the Data menu, select Configure Data Mart. In the Report Data Mart Setup dialog box, select the SQL Statements tab.
Custom SQL statements should be separated by semicolons. Press Enter after each SQL statement so that they are displayed on separate lines in the boxes on the SQL statements tab.
These SQL statements are not validated by the system. Be sure to follow the syntax rules for your database.
Type the SQL statements to be executed as described below:
- SQL to be executed prior to data mart creation: To execute SQL prior to the CREATE TABLE statement, type the SQL that you want to be executed.
- SQL to be executed after data mart creation: To execute SQL after the CREATE TABLE statement, type the SQL that you want to be executed.
- SQL to be executed before inserting data: To execute SQL after the CREATE TABLE statement and before the INSERT statement, type the SQL that you want to be executed.
You can also use placeholders to dynamically modify your queries. The following is a list of valid placeholders for custom SQL statements:
|
Placeholder |
Replacement Options |
|
!u |
User name |
|
!d |
Date on which the table was created |
|
!o |
Report name |
|
??? |
Temporary table names |
|
!!! |
All column names |
|
!a |
Attribute column names |
|
!! |
Exclamation (!) |
|
!!= |
Does not equal (!=) |
|
!j |
Job ID |
|
!r |
Report GUID |
|
!t |
Timestamp |
|
!p |
Project Name |
|
!z |
Project GUID |
|
!s |
User session GUID |