Skip to main content

"System Prompt" and its uses in MicroStrategy

System Prompt and its uses in MicroStrategy


WHAT IS A "SYSTEM PROMPT"?
"System Prompt" is a system object that was introduced back in version 8.0.0. The object is named as "User Login" and is implemented as a prompt object. The object can be found under Public Objects > Prompts > System prompts, as shown below:

Unlike ordinary prompt objects, system prompts don't require any answers from the user. When a report containing a system prompt runs, the prompt is answered automatically with the login of the user who runs the report. On the other hand, like other prompt objects, answers to system prompts are used to match caches. Therefore, users don't share caches for reports that contain system prompts. For details on how caches are matched, refer to the following MicroStrategy Knowledge Base document:
  • KB5300-7X0-0147 - How are caches matched in MicroStrategy Intelligence Server 7.x?
WHEN ARE SYSTEM PROMPTS USED? 
 
System prompts provide users more flexibility in implementing the security mechanisms of MicroStrategy applications. The following three examples demonstrate how system prompts can be used:

  • The security filter definition process is more simple with system prompts

    A report displays employee information and each manager can only view the information of those employees which the manager supervises. In MicroStrategy 7.x.x, security filters are static; multiple security filters must be defined and assigned to each manager user accordingly. The security filters is defined as follows:

    Manager = "Jane Doe"
    ….
    Manager = "Tom White"

    In MicroStrategy 8.0.0, security filters can be defined in a more dynamic way. For the example described above, only one security filter is necessary and it is defined as:

    Manager = ?

    This security filter can be assigned to a Manager user group. When a user with login "Jane Doe" executes the report, the security filter will generate SQL for condition:

    Manager = 'Jane Doe'
     
  • Report level "security filter" can be implemented

    In MicroStrategy 10.x, security filter functionalities can be implemented at report level by defining report filters with a system prompt. For instance, the Manager = ? condition can be used to define a report filter and users can include that report filter in certain reports but not others. In this way, security is enforced at the report level, not the project level.
 
  • Database tables containing security information can be used

    To synchronize security constraints for all enterprise applications, some organizations maintain security information in database tables and build all enterprise applications based on security tables. With system prompts, it is possible to use database security tables to build MicroStrategy security mechanisms.
     
    Example:

    In the database warehouse, there is a table called SecurityRegion, with two columns, Region_ID and User_ID. SecurityRegion table defines from which region a user is allowed to view data. Using system prompts, users can use SecurityRegion table to create a report with a restriction on regions. MicroStrategy Tutorial project is used in the following example to illustrate this:
    1. Define an attribute qualification filter, Security_Filter_APPLY, as shown below:

      ApplyComparison ("#0 in (select Region_ID from SecurityRegion where User = #1)", Region@ID, ?)
      1. Create a report with the Region attribute on the template and Security_Filter_APPLY as the report filter.
      2. Login as 'Administrator' user. View SQL and notice that only the regions the administrator are allowed to view are returned:
        select a11.Region_ID Region_ID, a11.REGION_NAME REGION_NAME
        from LU_REGION a11
        where a11.Region_ID in
            (select Region_ID from SecurityRegion where User = 'Administrator')
        1. Login as 'Brian Kelt.' SQL for the same report changes to:
          select a11.Region_ID Region_ID, a11.REGION_NAME REGION_NAME
          from LU_REGION a11
          where a11.Region_ID in
              (select Region_ID from SecurityRegion where User = 'Brian Kelt')

      Comments

      Popular posts from this blog

      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 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:...

      Custom Tooltips in Microstrategy developer and Web

      Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...

      Relationship with Report Filter options for Levels metrics

      Relationship with Report Filter options for Levels metrics You can define how the report filter affects the metric calculation. From the  Relationship with Report Filter  drop-down list, select one of the following: • To include only data that meets the conditions in the report filter in the metric calculation, select  Standard filtering . • To raise the level of the report filter to the level of the target, if possible, then apply the report filter to the metric calculation, select  Absolute filtering . For example, the report filter contains the Washington, DC, Boston, and New York call centers, but the Revenue metric is calculated at the Region level. Because Call Center is a child attribute of Region, the report filter's level is raised to the Region level, and the report filter is treated as if it includes the regions that contain Washington, DC, Boston, and New York (in this case, Mid-Atlantic and Northeast). Data from all call centers in the Mid-At...

      Email Subscription in Microstrategy

      Create Email Subscription in Microstrategy Creates an email subscription. Do not include any leading or trailing spaces in the ANSWER parameters. This causes a SQL error and prevents the command from executing. Ex: CREATE EMAILSUBSCRIPTION "New Multi Users" FOR OWNER "administrator" SCHEDULE "Books Closed" CONTACTGROUP "TEST"  CONTENT "Electronics Revenue by Region" IN FOLDER "\Public Objects\REPORTS\SUBJECT Areas\Sales and Profitability Analysis" IN PROJECT "MicroStrategy Tutorial" DELIVERYFORMAT HTML  EXPIRATIONDATE NEVER EXPRIED FILENAME "file_name"   SUBJECT  "Test REPORT" MESSAGE "Please Test"; CREATE EMAILSUBSCRIPTION [ subscription_name ] [FOR OWNER login_name ] SCHEDULE schedule_name  (ADDRESS address_name | USER user_name  | CONTACT contact_name [ADDRESS contact_ address_name ] | CONTACTGROUP contact_group_name ) CONTENT ( report_or_document_name IN FOLDER   loc...

      Microstrategy removing rows with Zero metric values

      Microstrategy removing rows with Zero metric values If there are more than one metric and want to remove the rows with Zero metric values. There are several ways to do this but I guess the easiest one will be creating a Metric which is the sum of all the metrics to be used in the report. Such as Sum Metric = Metric 1 + Metric 2 + Metric 3 +........... The Sum Metric should be included in the report and it can be controlled by any of the ways below: 1) Adding a view filter to the report where  Sum Metric != 0 which will bring only non zero values 2) Creating a filter definition prompt with the  Sum Metric , so that when the rpeort is prompted user cans elect  Sum Metric value is >0 . Or we can jsut had code the filter  Sum Metric >0 if we want to pre-filter the report with non zero rows for all the metrics in the report. We can also use the report limits with metric values >0 with an and condition between each metric c...

      Retrieve a list of user groups and the associated users in MicroStrategy

          Retrieve a list of user groups and the associated users in MicroStrategy Developer 9.x Although MicroStrategy Command Manager 9.x can be used to generate lists of all user groups and all users within a particular group, there is currently no way for it to retrieve a list of all groups and all of the users in each group. If such a list is desired, it can be created using the Project Documentation Wizard in MicroStrategy Desktop Developer 9.x. Follow the steps below to create a list of all groups and the users in each group: In MicroStrategy Developer 9.x, select 'Project Documentation' from the Tools menu to start the wizard. Select any project that is in the project source that contains the users and groups and click Next. Select only Configuration Objects for documentation. Uncheck the 'Basic Properties' object category from the next screen, as shown below: Then select only 'User Group' under the Configuratio...

      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 "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...

      Tag Users in Microstrategy Dossier

      Tag Users in Microstrategy Dossier You can tag multiple users or user groups in a comment. You want to tag the team leader responsible for creating the dossier. Navigate to the first page of the Office Royale Sales dossier. On the  Overview  page, click the  Comments    icon. In  Comments , enter  @ . A list of suggested users appears as you type. The owner and/or certifier of the dossier appears at the top of the list with  Owner    and  Certifier    icons. Select a user from the list. If you want to include the filtered state of your dossier, enter  @  in  Comments  and click  filter . Enter your message. Click  Post .