MSTR Report Pre and Post Statements

Report Post Statement

The Report Post Statement settings insert custom SQL statements after the final SELECT statement but before the DROP statements. The settings are numbered 1-5. Each text string entered in Report Post Statement 1 through Report Post Statement 4 is executed separately as a single statement. To execute more than five statements, insert multiple statements in Report Post Statement 5, separating each statement with a semicolon (;). The SQL Engine breaks them into individual statements at the semicolons and executes each separately. The custom SQL is applied to every intermediate table or view.

Report Pre Statement

The Report Pre Statement settings insert custom SQL statements at the beginning of the report SQL. The settings are numbered 1-5. Each text string entered in Report Pre Statement 1 through Report Pre Statement 4 is executed separately as a single statement. To execute more than five statements, insert multiple statements in Report Pre Statement 5, separating each statement with a semicolon (;). The SQL Engine breaks them into individual statements at the semicolons and executes each separately.

Table Post Statement

The Table Post Statement settings insert custom SQL statements after the CREATE TABLE and INSERT INTO statement. The settings are numbered 1-5. Each text string entered in Table Post Statement 1 through Table Post Statement 4 is executed separately as a single statement. To execute more than five statements, insert multiple statements in Table Post Statement 5, separating each statement with a semicolon (;). The SQL Engine breaks them into individual statements at the semicolons and executes each separately. The custom SQL is applied to every intermediate table or view

Comments

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name – is a generic name used for the predefined pass-through functions described above expression with placeholders – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are five pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select Custom as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the Project Design Guide . ...

Microstrategy "Error type: Odbc error. Odbc operation attempted

"Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’. Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Control the display of null and zero metric values

Show Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the Hide Nulls/Zeros icon in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the Tools menu, select Report Options . The Report Options...

Bursting file subscriptions Microstartegy

Bursting file subscriptions: Delivering parts of reports across multiple files: Large MicroStrategy reports and documents are often broken up into separate pages by attributes. In a similar way, with Distribution Services, you can split up, or burst, a report or document into multiple files. When the subscription is executed, a separate file is created for each element of each attribute selected for bursting. Each file has a portion of data according to the attributes used to group data in the report (page-by axis) or document (group-by axis). Ex:, you may have a report with information for all regions. You could place Region in the page-by axis and burst the file subscription into the separate regions. This creates one report file for each region. As a second ex:, if you choose to burst your report using the Region and Category attributes, a separate file is created for each combination of Region and Category, such as Central and Books as a report, Central and Ele...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

Events and event-triggered schedules in Microstrategy

Events and event-triggered schedules in Microstrategy Subscriptions and tasks that are based on event-triggered schedules execute when a MicroStrategy event is triggered. These triggers do not need to be defined in advance. A system external to Intelligence Server is responsible for determining whether the conditions for triggering an event are met. Once Intelligence Server has been notified that the event has taken place, Intelligence Server performs the tasks associated with the corresponding schedule. In a clustered environment, administrative tasks associated with event-triggered schedules are executed only by the node on which the event is triggered. MicroStrategy recommends that you use event-triggered schedules in situations where it is important to control which node performs certain tasks. If projects are distributed asymmetrically across the cluster, when you assign an event-triggered schedule to a project, make sure you trigger the event on all nodes on which tha...

Transaction Services - Configure Transactions

Configure Transactions in MSTR Web Transaction Services-enabled document displayed on an iPhone, iPad, or Android device can allow users to insert/update/delete data in to the database, using the options in the Configure Transactions Editor. To do so, you must link a Transaction Services report to a grid or to text fields in a panel stack. If the document is being displayed on an iOS device, you can link the report to the cells of a transaction table. Data from the input objects defined in the Transaction Services report is displayed in the grid, text fields, or cells for users to edit. Prerequisites: Ø You must have the Web Configure Transaction privilege assigned by MSTR user admin. Ø Create the Transaction Services report (usually a grid report) you want to link to the grid, text fields, or transaction table cells. Make sure that the Transaction Services report must contain the input object for each value you w...

Microstrategy Analytics

Search This Blog