Microstrategy Advanced Reporting

MICROSTRATEGY DEVELOPER: ADVANCED REPORTING
Course Guide
Version: RPTADV-941-Mar14-CG
20002014 MicroStrategy Incorporated. All rights reserved.
This Course (course and course materials) and any Software are provided as is and without express or limited
warranty of any kind by either MicroStrategy Incorporated (MicroStrategy) or anyone who has been involved in the
creation, production, or distribution of the Course or Software, including, but not limited to, the implied warranties of
merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the Course
and Software is with you. Should the Course or Software prove defective, you (and not MicroStrategy or anyone else
who has been involved with the creation, production, or distribution of the Course or Software) assume the entire cost
of all necessary servicing, repair, or correction.
In no event will MicroStrategy or any other person involved with the creation, production, or distribution of the Course
or Software be liable to you on account of any claim for damage, including any lost profits, lost savings, or other
special, incidental, consequential, or exemplary damages, including but not limited to any damages assessed against or
paid by you to any third party, arising from the use, inability to use, quality, or performance of such Course and
Software, even if MicroStrategy or any such other person or entity has been advised of the possibility of such damages,
or for the claim by any other party. In addition, MicroStrategy or any other person involved in the creation, production,
or distribution of the Course and Software shall not be liable for any claim by you or any other party for damages
arising from the use, inability to use, quality, or performance of such Course and Software, based upon principles of
contract warranty, negligence, strict liability for the negligence of indemnity or contribution, the failure of any remedy
to achieve its essential purpose, or otherwise.
The Course and the Software are copyrighted and all rights are reserved by MicroStrategy. MicroStrategy reserves the
right to make periodic modifications to the Course or the Software without obligation to notify any person or entity of
such revision. Copying, duplicating, selling, or otherwise distributing any part of the Course or Software without prior
written consent of an authorized representative of MicroStrategy are prohibited.
U.S. Government Restricted Rights. It is acknowledged that the Course and Software were developed at private
expense, that no part is public domain, and that the Course and Software are Commercial Computer Software and/or
Commercial Computer Software Documentation provided with RESTRICTED RIGHTS under Federal Acquisition
Regulations and agency supplements to them. Use, duplication, or disclosure by the U.S. Government is subject to
restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at
DFAR 252.227-7013 et. seq. or subparagraphs (c)(1) and (2) of the Commercial Computer SoftwareRestricted Rights
at FAR 52.227-19, as applicable. The Contractor is MicroStrategy, 1850 Towers Crescent Plaza, Tysons Corner, Virginia
22182. Rights are reserved under copyright laws of the United States with respect to unpublished portions of the
Software.
Copyright Information
All Contents Copyright 2014 MicroStrategy Incorporated. All Rights Reserved.
Trademark Information
MicroStrategy, MicroStrategy 6, MicroStrategy 7, MicroStrategy 7i, MicroStrategy 7i Evaluation Edition,

MicroStrategy 7i Olap Services, MicroStrategy 8, MicroStrategy 9, MicroStrategy Distribution Services, MicroStrategy
MultiSource Option, MicroStrategy Command Manager, MicroStrategy Enterprise Manager, MicroStrategy Object
Manager, MicroStrategy Reporting Suite, MicroStrategy Power User, MicroStrategy Analyst, MicroStrategy Consumer,
MicroStrategy Email Delivery, MicroStrategy BI Author, MicroStrategy BI Modeler, MicroStrategy Evaluation Edition,
MicroStrategy Administrator, MicroStrategy Agent, MicroStrategy Architect, MicroStrategy BI Developer Kit,
MicroStrategy Broadcast Server, MicroStrategy Broadcaster, MicroStrategy Broadcaster Server, MicroStrategy
Business Intelligence Platform, MicroStrategy Consulting, MicroStrategy CRM Applications, MicroStrategy Customer
Analyzer, MicroStrategy Desktop, MicroStrategy Desktop Analyst, MicroStrategy Desktop Designer, MicroStrategy
eCRM 7, MicroStrategy Education, MicroStrategy eTrainer, MicroStrategy Executive, MicroStrategy Infocenter,
MicroStrategy Intelligence Server, MicroStrategy Intelligence Server Universal Edition, MicroStrategy MDX Adapter,
MicroStrategy Narrowcast Server, MicroStrategy Objects, MicroStrategy OLAP Provider, MicroStrategy SDK,
MicroStrategy Support, MicroStrategy Telecaster, MicroStrategy Transactor, MicroStrategy Web, MicroStrategy Web
Business Analyzer, MicroStrategy World, Application Development and Sophisticated Analysis, Best In Business
Intelligence, Centralized Application Management, Information Like Water, Intelligence Through Every Phone,
Intelligence To Every Decision Maker, Intelligent E-Business, Personalized Intelligence Portal, Query Tone, Rapid
Application Development, MicroStrategy Intelligent Cubes, The Foundation For Intelligent E-Business, The Integrated
Business Intelligence Platform Built For The Enterprise, The Platform For Intelligent E-Business, The Scalable
Business Intelligence Platform Built For The Internet, Office Intelligence, MicroStrategy Office, MicroStrategy Report
Services, MicroStrategy Web MMT, MicroStrategy Web Services, Pixel Perfect, Pixel-Perfect, MicroStrategy Mobile,
MicroStrategy Integrity Manager and MicroStrategy Data Mining Services are all registered trademarks or trademarks
of MicroStrategy Incorporated.
All other company and product names may be trademarks of the respective companies with which they are associated.
Specifications subject to change without notice. MicroStrategy is not responsible for errors or omissions.
MicroStrategy makes no warranties or commitments concerning the availability of future products or versions that
may be planned or under development.
Patent Information
This product is patented. One or more of the following patents may apply to the product sold herein: U.S. Patent Nos.
6,154,766, 6,173,310, 6,260,050, 6,263,051, 6,269,393, 6,279,033, 6,567,796, 6,587,547, 6,606,596, 6,658,093,
6,658,432, 6,662,195, 6,671,715, 6,691,100, 6,694,316, 6,697,808, 6,704,723, 6,741,980, 6,765,997, 6,768,788,
6,772,137, 6,788,768, 6,798,867, 6,801,910, 6,820,073, 6,829,334, 6,836,537, 6,850,603, 6,859,798, 6,873,693,
6,885,734, 6,940,953, 6,964,012, 6,977,992, 6,996,568, 6,996,569, 7,003,512, 7,010,518, 7,016,480, 7,020,251,
7,039,165, 7,082,422, 7,113,993, 7,127,403, 7,174,349, 7,181,417, 7,194,457, 7,197,461, 7,228,303, 7,260,577, 7,266,181,
7,272,212, 7,302,639, 7,324,942, 7,330,847, 7,340,040, 7,356,758, 7,356,840, 7,415,438, 7,428,302, 7,430,562,
7,440,898, 7,486,780, 7,509,671, 7,516,181, 7,559,048, 7,574,376, 7,617,201, 7,725,811, 7,801,967, 7,836,178, 7,861,161,
7,861,253, 7,881,443, 7,925,616, 7,945,584, 7,970,782, 8,005,870, 8,051,168, 8,051,369, 8,094,788, 8,130,918,
8,296,287, 8,321,411 and 8,452,755. Other patent applications are pending.
How to Contact Us
MicroStrategy University MicroStrategy Incorporated

1850 Towers Crescent Plaza 1850 Towers Crescent Plaza
Tysons Corner, VA 22182 Tysons Corner, VA 22182
Phone: 877.232.7168 Phone: 703.848.8600
Fax: 703.848.8602 Fax: 703.848.8610
E-mail: [email protected] E-mail: [email protected]
http://www.microstrategy.com/training-events/ http://www.microstrategy.com
training
TABLE OF CONTENTS
Preface Course Description.................................................................... 13

Who Should Take This Course .............................................. 14
Course Objectives ................................................................. 15
About the Course Materials ......................................................... 16
Content Descriptions ............................................................. 16
Learning Objectives ............................................................... 16
Lessons ................................................................................. 16
Opportunities for Practice ...................................................... 17
Typographical Standards ....................................................... 17
Core Courses......................................................................... 19
Advanced Courses ................................................................ 20
1. Introduction to Lesson Description ................................................................... 23

Advanced Reporting Lesson Objectives ................................................................. 24
Business Intelligence Architecture............................................... 25
Source Systems..................................................................... 26
Data Warehouse .................................................................... 26
MicroStrategy Business Intelligence Platform........................ 26
Metadata Database ............................................................... 27
Basics of Reporting...................................................................... 29
Review: What is a Report? .................................................... 29
Three-tier Versus Four-Tier Reporting................................... 30
Report Execution Query Flow ................................................ 32
Reporting and Caches ........................................................... 33
Security Considerations in Report Development ................... 35
Lesson Summary......................................................................... 40
2014 MicroStrategy Inc. 5

Table of Contents MicroStrategy Developer: Advanced Reporting
2. Advanced Metrics Lesson Description ................................................................... 41

Lesson Objectives ................................................................. 42
Level Metrics................................................................................ 43
What Are Level Metrics?........................................................ 43
The Metric Editor and Level Metrics ...................................... 43
Using Level Metrics................................................................ 45
Grouping Set to Standard ...................................................... 46
Grouping Set to None ............................................................ 50
Defining Level Metrics............................................................ 55
Removing the Report Level Target ........................................ 56
Review: Level Metrics.................................................................. 59
Non-aggregatable Metrics ........................................................... 66
What Is a Non-aggregatable Metric? ..................................... 66
Defining Non-aggregatable Metrics ....................................... 67
Transformation Metrics ................................................................ 70
What Are Transformations? ................................................... 70
Types of Transformations ...................................................... 71
Transformation Components ................................................. 73
Creating a Transformation Metric .......................................... 74
Base Formulas............................................................................. 76
Reusing Formulas.................................................................. 76
Conditional Metrics ...................................................................... 77
Removing Related Report Filter Elements............................. 78
Nested Metrics............................................................................. 80
Advanced Functions .................................................................... 83
Overview of Built-in Advanced Functions .............................. 83
Count Metrics......................................................................... 86
Advanced FunctionsSelect Examples ................................ 88
Advanced Subtotals..................................................................... 94
Custom Subtotals .................................................................. 94
User-Defined Subtotals.......................................................... 97
Lesson Summary....................................................................... 101
Exercises: Advanced Metrics..................................................... 103
Level Metrics: Percent Contribution to Subcategory and
Category .............................................................................. 104
Level Metrics: All Item Sales................................................ 106
Level Metrics: Metrics with Multiple Target Attributes.......... 107
Non-aggregatable Metrics.................................................... 109
Transformation Metrics ........................................................ 111
Conditional Metrics: Geographic Comparisons.................... 112
Count Metrics....................................................................... 114
6 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Table of Contents
Use an Advanced FunctionRunning Revenue ................. 116

Optional Advanced Exercises.................................................... 119
Monthly Sales for Top 3 Customer Regions ........................ 119
Rank of Units Sold by Subcategory ..................................... 120
Advanced Metrics Exercise Solutions........................................ 122
3. Advanced Filters Lesson Description ................................................................. 123

Lesson Objectives ............................................................... 124
Attribute-to-Attribute Filtering..................................................... 125
Joint Element List ...................................................................... 127
Dynamic Dates .......................................................................... 130
Creating Dynamic Dates ...................................................... 130
Order of Evaluation .............................................................. 132
Import Filter Elements................................................................ 133
Pass-through Functions............................................................. 135
Pass-through Functions ....................................................... 135
Creating Custom Expressions With Apply Functions........... 136
Set Qualification: Qualifying on Metrics ..................................... 139
Output Level ........................................................................ 139
Break By .............................................................................. 141
Metric-to-Metric Comparison................................................ 142
Set Qualification: Relationship Filters ........................................ 144
Direct and Indirect Attribute Relationships........................... 144
Relationship Filter ................................................................ 145
Relationship Filter Editor...................................................... 148
Relationship Filters vs. Shortcut-to-a-report Qualification.... 151
Lesson Summary....................................................................... 153
Exercises: Advanced Filters ...................................................... 155
Attribute-to-attribute Filtering ............................................... 155
Joint Element List ................................................................ 157
Import Filter Elements.......................................................... 158
Dynamic Dates .................................................................... 159
Pass-through Functions ....................................................... 161
Set QualificationOutput Level........................................... 163
Set QualificationBreak by ................................................. 164
Relationship Filter ................................................................ 165
Demographic Analysis ......................................................... 167
Unit Sales for Bottom 5% of Items ....................................... 168

Market Basket Analysis........................................................ 169

Advanced Filter Exercise Solutions ........................................... 171
4. Consolidations and Lesson Description ................................................................. 173

Custom Groups Lesson Objectives ............................................................... 174
Consolidations ........................................................................... 175
Business Scenario: Seasonal Analysis................................ 175
What Is a Consolidation? ..................................................... 176
Consolidation Elements ....................................................... 178
Consolidation Editor............................................................. 179
Custom Groups.......................................................................... 182
Business Scenario: Special Promotions .............................. 182
What Is a Custom Group? ................................................... 183
Custom Groups and SQL Query.......................................... 183
Custom Group Editor ........................................................... 184
Custom Group Banding ....................................................... 189
Consolidations vs. Custom Groups...................................... 194
Lesson Summary....................................................................... 195
Exercises: Consolidations and Custom Groups ........................ 197
ConsolidationAnalysis by Customer Geography .............. 197
ConsolidationMore Analysis by Customer Geography..... 198
Custom GroupsTop 5 ....................................................... 199
Customer Banding ............................................................... 200
Brand Comparisons ............................................................. 202
Employee Experience Analysis............................................ 203
5. Advanced OLAP Lesson Description ................................................................. 205

Services Lesson Objectives ............................................................... 206
Intelligent Cubes ........................................................................ 207
Sharing Intelligent Cubes........................................................... 208
Creating Intelligent Cubes ................................................... 209
Enabling ROLAP Drilling for Reports Accessing Intelligent
Cubes .................................................................................. 214
Publishing Intelligent Cubes ................................................ 218
Reporting and Analyzing Data with Intelligent Cubes................ 221
Run-time Reporting with Prompts ........................................ 224
Relational Analysis with Drilling ........................................... 225
Unavailability of Intelligent Cubes ........................................ 228
Reporting on Intelligent Cubes with Dynamic Sourcing ....... 229

View Filters and Dynamic Aggregation...................................... 230

View Filter: Metric Qualification Level .................................. 230
Lesson Summary....................................................................... 233
Exercises: Intelligent Cubes ...................................................... 235
Creating and Reporting on Intelligent Cubes ....................... 235
Utilizing Prompts in Intelligent Cube Reports....................... 238
View Filters in Intelligent Cube Report................................. 243
6. Derived Elements Lesson Description ................................................................. 249

Lesson Objective ................................................................. 250
Introduction to Derived Elements............................................... 251
Types of Derived Elements........................................................ 253
Group Derived Element ....................................................... 253
Filter Derived Element ......................................................... 255
Calculation Derived Element................................................ 257
All Other ............................................................................... 259
Creating Derived Elements........................................................ 260
Create Quick Groups and Calculations................................ 261
Using the Derived Elements Editor ...................................... 265
Create and Share Stand-Alone Derived Elements .............. 270
Derived Element Formatting ...................................................... 273
Interaction with Other Reporting Features................................. 275
View filters ........................................................................... 275
Derived Metrics .................................................................... 277
Page-by ............................................................................... 277
Thresholds ........................................................................... 279
Drilling .................................................................................. 280
Prompts ............................................................................... 282
Lesson Summary....................................................................... 283
Exercise: Derived Elements....................................................... 285
Create Quick Calculations ................................................... 285
Creating Derived Elements Using The Derived Elements Editor
289
7. Report Data Options Lesson Description ................................................................. 301

Overview of Report Data Options .............................................. 303
Calculations ............................................................................... 305
Report Limit ......................................................................... 305
Metric Join Type .................................................................. 307

Attribute Join Type ............................................................... 311

Evaluation Order .................................................................. 313
Subtotals .............................................................................. 320
Display ....................................................................................... 322
Alias ..................................................................................... 322
Object Display...................................................................... 324
Null Values........................................................................... 326
Grid Graph Mode ................................................................. 330
General Options......................................................................... 331
Drilling .................................................................................. 331
Advanced ............................................................................. 335
MicroStrategy Mobile ........................................................... 336
Lesson Summary....................................................................... 337
Exercises: Report Data Options ................................................ 339
Report Limit ......................................................................... 339
Joins .................................................................................... 340
Evaluation Order Compound Metric and Report Limit ...... 342
Subtotals .............................................................................. 344
Alias ..................................................................................... 346
Drilling .................................................................................. 346
Report Data Options Exercise Solutions ................................... 348
8. Drill Maps Lesson Description ................................................................. 351

What is Drilling?......................................................................... 353
Drill Maps and Drill Paths..................................................... 353
Creating and Customizing Drill Maps ........................................ 356
Create a Drill Map ................................................................ 356
The Drill Map Editor ............................................................. 357
Lesson Summary....................................................................... 362
Exercises: Drill Maps ................................................................. 363
Drill to Template: Customer Details ..................................... 363
9. Report Developer Lesson Description ................................................................. 367

Tools Lesson Objectives ............................................................... 368
Find and Replace....................................................................... 369
Project Documentation .............................................................. 373
Lesson Summary....................................................................... 377
Exercises: Report Developer Tools ........................................... 379

Find and Replace................................................................. 379

Project Documentation ........................................................ 380
Report Developer Tools Exercise Solutions .............................. 383
A. MicroStrategy Tutorial The MicroStrategy Tutorial Data Model ..................................... 386

Geography Hierarchy........................................................... 387
Customers Hierarchy ........................................................... 388
Time Hierarchy..................................................................... 389
Products Hierarchy .............................................................. 390
The MicroStrategy Tutorial Schema .......................................... 390
Geography Schema ............................................................. 394
Customers Schema ............................................................. 395
Time Schema....................................................................... 396
Products Schema ................................................................ 397
Fact Tables Schema ............................................................ 398
Partition Mapping Table ....................................................... 398
B. Additional Topics Appendix Description.............................................................. 399

Advanced Settings..................................................................... 400
Conditional Metrics: Embedding Method ............................. 400
Filter Editor: Set Qualification Advanced Button .................. 403
Dynamic Aggregation .......................................................... 405
Prompt Editor: Level Prompt................................................ 410
Prompted Custom Groups ................................................... 412
Prompt-in-prompt Functionality............................................ 413
Value-Prompted Metrics ...................................................... 415
Index ......................................................................................... 419


PREFACE
Course Description
This three day course explores advanced topics in MicroStrategy Developer.

The course assumes and builds on an understanding of basic report creation
from the two day MicroStrategy Developer: Reporting Essentials class.
Developer has a wealth of sophisticated reporting features designed to enable
you to create almost any type of report. This course explores some of these
options with the goal of equipping you to take advantage of them in your own
projects.
You will learn about creating advanced metrics, such as level, transformation,
and conditional metrics. You will also learn about creating and using advanced
filters, such as relationship, metric-to-metric, and joint element list filters. You
will also learn about custom groups and consolidations. You will learn about
advanced MicroStrategy OLAP Services features and custom drill maps.
Finally, you will learn about using Report Data Options features to customize
reports and work with other useful tools such as the Project Documentation
Wizard.
At the end of this course, you will have an understanding of the important
concepts required to build sophisticated reports using Developer.

Preface MicroStrategy Developer: Advanced Reporting
Who Should Take This Course

This course is designed for:
Report developers
Course Prerequisites
Before starting this course, you should be familiar with:
MicroStrategy Developer: Reporting Essentials
Basic knowledge of SQL
Follow-Up Courses
After taking this course, you might consider taking the following courses:
MicroStrategy Visual Insight Essentials
MicroStrategy Report Services: Documents and Dashboards
Related Certifications
To validate your proficiency in the content of this course, you might consider
taking the following certification:
Certified Report Developer
14 Who Should Take This Course 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Preface
Course Objectives
After completing this course, you will be able to:
Describe the MicroStrategy business intelligence architecture that serves as

the foundation for creating reports. Explain report execution
concepts. (Page 24)
Create level, non-aggregatable, transformation, conditional, and nested

metrics. Describe the settings associated with these types of metrics.
Execute reports using advanced metrics. (Page 42)
Create several types of advanced filters. (Page 124)
Create consolidations and custom groups and determine the appropriate use
of both objects. (Page 174)
Explain the concept of Intelligent Cubes. Create Intelligent Cube reports

that share Intelligent Cube caches. Understand how view filters and
dynamic aggregation work while adding and moving objects from the grid to
the Report Objects pane. (Page 206)
Explain the derived elements feature, the different types of derived

elements, and how they can be created in Developer. (Page 250)
Define all of the Report Data Options and understand how to enable
them. (Page 302)
Describe the purpose of drill maps in reporting. Create and edit drill
maps. (Page 352)
Use Find and Replace to change multiple objects simultaneously. Create

project documentation HTML files. (Page 368)
2014 MicroStrategy Inc. Course Objectives 15

About the Course Materials

This course is organized into lessons and reference appendices. Each lesson
focuses on major concepts and skills that help you to better understand
MicroStrategy products and use them to implement MicroStrategy projects.
The appendices provide you with supplemental information to enhance your
knowledge of MicroStrategy products.
Content Descriptions
Each major section of this course begins with a Description heading. The
Description introduces you to the content contained in that section.
Learning Objectives
Learning objectives enable you to focus on the key knowledge and skills you
should obtain by successfully completing this course. Objectives are provided
for you at the following three levels:
CourseYou achieve these overall objectives by successfully completing all

the lessons in this course. The Course Objectives heading in this Preface
contains the list of course objectives.
LessonYou achieve these main objectives by successfully completing all
the topics in the lesson. You can find the primary lesson objective directly
under the Lesson Objectives heading at the beginning of each lesson.
Main TopicYou achieve this secondary objective by successfully

completing the content in the main topic. The topic objective is stated at the
beginning of the topic text. You can find a list of all the topic objectives in
each lesson under the Lesson Objectives heading at the beginning of each
lesson.
Lessons
Each lesson sequentially presents concepts and guides you with step-by-step
procedures. Illustrations, screen examples, bulleted text, notes, and definition
tables help you to achieve the learning objectives.
16 About the Course Materials 2014 MicroStrategy Inc.

Opportunities for Practice

A Workshop is a reinforcement and assessment activity that follows two or
more lessons. Because a Workshop covers content and applied skills presented
in several lessons, it is a separate section on the level of a lesson.
The following sections within lessons provide you with opportunities to

reinforce important concepts, practice new product and project skills, and
monitor your own progress in achieving the lesson and course objectives:
Review
Case Study
Business Scenario
Exercises
Typographical Standards
The following sections explain the font style changes, icons, and different types
of notes that you see in this course.
Actions
References to screen elements and keys that are the focus of actions are in bold
Arial font style. The following example shows this style:
Click Select Warehouse.
Code
References to code, formulas, or calculations within paragraphs are formatted

in regular Courier.New font style. The following example shows this style:
Sum(Sales)/Number of Months
2014 MicroStrategy Inc. About the Course Materials 17

Data Entry
References to literal data you must type in an exercise or procedure are in bold
Arial font style. References to data you type that could vary from user to user or
system to system are in bold italic Arial font style. The following example
shows this style:
Type copy c:\filename d:\foldername\filename.
Keyboard Keys
References to a keyboard key or shortcut keys are in uppercase letters in bold

Arial font style. The following example shows this style:
Press CTRL+B.
New Terms
New terms to note are in regular italic font style. These terms are defined when
they are first encountered in the course. The following example shows this
style:
The aggregation level is the level of calculation for the metric.
Notes and Warnings
A note icon indicates helpful information.

Ayouwarning icon calls your attention to very important information that
should read before continuing the course.
Heading Icons
The following heading icons are used to indicate specific practice and review
sections:
Precedes a Review section

Precedes a Case Study
Precedes a Business Scenario
Precedes Exercises
MicroStrategy Courses
Core Courses
Implementing MicroStrategy: Development and Deployment
MicroStrategy Web Essentials
MicroStrategy Web for Reporters and Analysts
MicroStrategy Web for Professionals
MicroStrategy Visual Insight Essentials
MicroStrategy Report Services: Documents and Dashboards
MicroStrategy Mobile for App Developers
MicroStrategy Architect: Project Design Essentials
MicroStrategy Developer: Reporting Essentials
MicroStrategy Developer: Advanced Reporting
MicroStrategy Office Essentials

Advanced Courses
MicroStrategy Administration: Configuration and Security
MicroStrategy Administration: Application Management
MicroStrategy Engine Essentials
MicroStrategy Architect: Advanced Project Design
MicroStrategy Advanced Data Warehousing
MicroStrategy Data Mining and Advanced Analytics
MicroStrategy Developer: Advanced Reporting Case Studies
MicroStrategy Freeform SQL Essentials
MicroStrategy Transaction Services for Mobile App and Dashboard

Developers
MicroStrategy Web SDK: Customization Essentials
MicroStrategy Web SDK: Customizing Security
MicroStrategy Web SDK: Portal Integration
All courses are subject to change. Please visit the MicroStrategy website
for the latest education offerings.



1
INTRODUCTION TO ADVANCED
REPORTING
Lesson Description
This lesson covers two major topics related to creating and deploying
MicroStrategy reports in a business intelligence system.
First, you will learn about the components of a business intelligence system so
that you have a better understanding of the entire architecture that makes
business intelligence reporting possible.
Second, you will learn about the basic concepts of report execution of which
you should be aware, especially when creating reports for a large end-user
audience. This lesson covers three main concepts related to reporting:
Report execution query flow
Reporting and caches
Reporting and security

1 Introduction to Advanced Reporting MicroStrategy Developer: Advanced Reporting
Lesson Objectives
After completing this lesson, you will be able to:

Describe the MicroStrategy business intelligence architecture that serves as
the foundation for creating reports. Explain report execution concepts.
After completing the topics in this lesson, you will be able to:
Define the components of a MicroStrategy business intelligence

architecture. (Page 25)
Describe the basic concepts related to report execution that are useful to a
report designer when devising a strategy to deploy advanced reports to end
users. (Page 29)
24 Lesson Objectives 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Introduction to Advanced Reporting 1
Business Intelligence Architecture
After completing this topic, you will be able to:

Define the components of a MicroStrategy business intelligence architecture.
Business intelligence systems facilitate the analysis of volumes of complex

data by giving you the ability to view this data from multiple perspectives. A
quality business intelligence system gives users access to data at various
levels, enables them to ask a variety of questions, and rapidly provides them
with accurate and meaningful answers. Modern-day business intelligence
systems must also provide a foundation for the proactive delivery of
information to system subscribers. Todays businesses need to be capable of
rapid, self-service analysis of new datasets while also supporting reporting on
a high volume of disparate data sources (big data). The market also requires
the delivery of relevant and timely information to the right people at the right
place and time. Finally, analytics technology must support businesses in
maintaining close and individualized contact with their customers.
The following illustration shows a business intelligence architecture using

MicroStrategy:
MicroStrategy Business Intelligence Architecture
2014 MicroStrategy Inc. Business Intelligence Architecture 25

Source Systems
A source system refers to any system or file that captures or records
transactions, such as sales, inventory, withdrawals, or deposits. Source
systems are often described as online transaction processing (OLTP)
systems.
Data Warehouse
A well-designed and robust data warehouse lies at the heart of the business
intelligence system. The data warehouse provides the foundation for a robust
online analytical processing (OLAP) system, which involves manipulating
transaction records to calculate sales trends, growth patterns,
percent-to-total contributions, trend reporting, profit analysis, and so forth.
While source systems are generally designed and optimized for transactional
processing, data warehouses are generally designed and optimized for
analytical processing. You populate a data warehouse with data from the
existing operational systems using an extraction, transformation, and
loading (ETL) process.
MicroStrategy Business Intelligence Platform

The MicroStrategy business intelligence platform is the heart of your
business intelligence environment. It encompasses the Engine and server
components, which are designed to effectively communicate with the data
warehouse and retrieve the exact results that a user requests at a given time.
The following components comprise the core of the platform:
SQL Engine and Query EngineWork together to generate and send

optimized SQL to the Relational Database Management System
Analytical EngineAdds analytical power to greatly extend the capability

of the system beyond what the Relational Database Management System
provides
Server componentsHandle all aspects of report generation, element

retrieval, object retrieval, and so forth
26 Business Intelligence Architecture 2014 MicroStrategy Inc.

The MicroStrategy business intelligence platform also provides a number of

applications that are built specifically to harness the power of its engine and
server components:
MicroStrategy Desktop
MicroStrategy Developer
MicroStrategy Architect
MicroStrategy Web
MicroStrategy Intelligence Server
MicroStrategy Report Services
MicroStrategy OLAP Services
MicroStrategy Distribution Services
MicroStrategy SAP Services
MicroStrategy Data Mining Services
MicroStrategy Office
MicroStrategy SDK
MicroStrategy Mobile
MicroStrategy Transaction Services
MicroStrategy Visual Insight
MicroStrategy Integrity Manager
MicroStrategy Object Manager
MicroStrategy Command Manager
MicroStrategy System Manager
MicroStrategy Enterprise Manager
MicroStrategy Health Center
MicroStrategy Cloud
2014 MicroStrategy Inc. Business Intelligence Architecture 27

Metadata Database
The metadata database stores MicroStrategy object definitions and
information about the data warehouse and maps MicroStrategy objects to the
data warehouse structures and content.
MicroStrategy applications use the metadata database to translate user

requests into SQL queries and translate the results of those queries back into
MicroStrategy objects like reports and documents.
The MicroStrategy metadata stores a variety of data. The following object

types in the MicroStrategy environment describe these various types of data:
Application objectsReports and report-related objects built in

Developer
Configuration objectsAdministrative and connectivity-related objects

built in Architect or in the Developer administration console
Schema objectsLogical objects built in Architect that relate application

objects to physical data warehouse structures
28 Business Intelligence Architecture 2014 MicroStrategy Inc.

Basics of Reporting

Describe the basic concepts related to report execution that are useful to a
report designer when devising a strategy to deploy advanced reports to end
users.
Review: What is a Report?

Reports are the focus of business intelligence investigations. A report is a
request for specific, formatted data from the data warehouse. A report
consists of a template, filter, and view. Although you may not specifically
define and use a separate template, filter, or view, these are the objects that
logically comprise the definition of a report.
A template specifies which information is to be retrieved from the data

warehouse. You can create templates separately using the Template Editor,
or you can create them on the fly in the Report Editor.
A filter specifies the conditions that the data must meet to be included in the
report results. You can create filters separately using the Filter Editor, or you
can create them on the fly in the Report Editor.
A view specifies the portion of the full report result set that is initially
displayed to the user. A report may contain two attributes and two metrics,
but the view or template that the end-user sees may contain only one of the
original attributes and one metrics. Additionally, a report filter may contain
data for the entire East region, but you can use a view filter to further restrict
the data that is displayed.
The view of a report is a feature of MicroStrategy OLAP Services.
2014 MicroStrategy Inc. Basics of Reporting 29

Three-tier Versus Four-Tier Reporting

As an advanced report designer, you use Developer to create your reports in a
three-tier environment. However, it is likely that most of the end-users who
run these reports do so in a four-tier environment, using MicroStrategy Web
or some other customized Web interface.
A three-tier business intelligence architecture consists of a data warehouse,

metadata, Intelligence Server, and Developer. The following illustration
shows a three-tier architecture:
Three-Tier Architecture
30 Basics of Reporting 2014 MicroStrategy Inc.

A four-tier business intelligence architecture consists of a data warehouse,

metadata, Intelligence Server, Developer, and MicroStrategy Web. The
following illustration shows a four-tier architecture:
Four-Tier Architecture
There is no difference between running a report in three tier and running the
same report in four tier. In both instances, almost all of the tasks involved in
report generation are completed by the Intelligence Server. The Intelligence
Server generates and executes the SQL, performs advanced analytics on the
data, manages report caches, and enforces security.
The only part of the report generation process that the client performs is to
format the data for display purposes. Developer gets the result set from
Intelligence Server and then formats it for display in the Developer interface.
Similarly, MicroStrategy Web retrieves the result set from Intelligence Server
and formats it for display in a Web browser.

Report Execution Query Flow

When you execute a report in Developer, there are a number of things that
Developer and Intelligence Server need to do before the final result set is
formatted and ready for display.
The following illustration and table describe, at a high level, the major steps
involved in executing a report in a three-tier environment:
Step Process
1 A request is made from the Client interface (Developer,

MicroStrategy Office, Web, etc.).
2 The request is passed to the Intelligence Server Bus which

coordinates all the tasks necessary to execute the report.
3 The Intelligence Server Bus first checks the cache to see whether
the report results are already there. The report results will already
be in cache if another user or a schedule previously ran the
report. If a valid cache exists, Intelligence Server skips directly to
Step 9.

Step Process
4 If no valid cache exists for the report, the Intelligence Server Bus
obtains the report definition and application objects from the
metadata using the Metadata Server.
5 The Intelligence Server Bus sends this information to the SQL

Engine. The SQL Engine generates the SQL optimized for the
specific database being used. The SQL passes are then returned
to the Intelligence Server Bus.
6 The Intelligence Server Bus sends the SQL to the Query Engine.
7 The Query Engine sends the SQL to run against the data
warehouse and the report results are returned to the Intelligence
Server Bus.
8 The Intelligence Server Bus invokes the Analytical Engine which

performs additional calculations, as necessary, and formats the
results. The results are returned to the Intelligence Server Bus.
9 Report results are returned to the Client interface.
For reports with prompts, before step 3, Intelligence Server sends the
request back to the user to obtain prompt answers. After the user
provides the prompt answers, Intelligence Server continues with the
remaining steps.
For reports that access an Intelligent Cube, the report result is

returned from a published Intelligent Cube cache. For more
information on Intelligent Cubes, see the Advanced OLAP Services
lesson starting on page 205.
Reporting and Caches

Intelligence Server can retain the definition and results for every report that
it has run to improve query response time if those same reports are run
again. These stored result sets are called report caches.
Caches are created only when you save and close a report and execute it. It is
not sufficient to execute the report from Design View.
Caches are managed by the administrator of Intelligence Server.
For more information on cache management, see the MicroStrategy

Administration: Configuration and Security course.

As a report designer, it is useful to understand at a high level how Intelligence

Server identifies valid caches. Understanding how caching works helps you
determine whether a cache will be used to satisfy a report request.
Intelligence Server identifies a valid report cache by checking the following
keys:
Report ID and Report Version ID
Template ID and Template Version ID
Filter ID and Filter Version ID
Security Filter ID (if any)
Prompt answers to the report, including system prompt answers (if any)
You can also associate the following keys with a report cache, depending on
how cache properties are configured:
User ID
Database Connection ID
Database Login ID
The administrator can use these options to ensure that caches are not
shared between users.
Each object in the MicroStrategy project is identified with a unique ID.

Examples of these unique object IDs are the Report ID, Template ID, and so
forth. These IDs remain the same as long as the objects exist and are not
deleted. However, every time you edit an object, it is given a new version ID
so that the tool can determine whether it has been modified.
If any of these keys do not match between a report that you run and an
existing cache, then the cache is not used, and the report is executed against
the data warehouse.
In addition, if you modify any of the other user objects that comprise the
template or filter of a report, such as metrics, custom groups, and so forth,
the version ID of the object changes. Since the object is part of the report
definition, when you execute the report again, a new cache is created.
As mentioned earlier, the administrator of your Intelligence Server typically

manages caches for your MicroStrategy environment. Still, as a report
developer, you should be aware that the overall caching configuration can be
devised at the MicroStrategy project-level or at the individual report level. In
other words, caching can be enabled or disabled for the entire project or on a
report-by-report basis.

Re-executing a Report
You can re-execute every report you run in Developer by using the
Re-execute button. In general, re-executing a report means running the
report against the data warehouse, thus bypassing any valid cache that might
exist for that report. The following reporting scenarios explain the use of the
Re-execute button:
Scenario 1: Nonprompted reportYou are viewing a report in Grid view.

You click the Re-execute button. If a valid cache exists for that report, the
Re-execute button overrides the cache by sending the same SQL query for
the original report against the data warehouse. In this scenario,
re-executing the report should only return a different result set if the data
in the data warehouse has changed.
Scenario 2: Prompted reportsYou run a report that contains prompts.

You answer the prompts at run time and view the results in Grid View.
You click the Re-execute button, expecting to see the prompt options
presented to you again. However, the Re-execute button does not work
like the Re-prompt button. In other words, Intelligence Server does not
try to resolve the prompt again if you re-execute a prompted report.
Instead, the Re-execute button simply sends the same SQL query with the
same prompt answers to the data warehouse. To see the prompt options
again you use the Re-prompt button.
Security Considerations in Report Development

The MicroStrategy platform consists of a comprehensive security model with
multiple levels of security interacting with one another. As a report designer,
you need to be aware of how different security settings affect the end-users
experience in the MicroStrategy environment.
An in-depth explanation of how security works in the MicroStrategy

project environment is beyond the scope of this class. This topic is
primarily applicable to administrators. For more information on
security, see the MicroStrategy Administration: Configuration and
Security course.
Object-Level Security
As a report developer, the object-level is the only level at which you can set
security properties.

When you create any MicroStrategy object, you are given ownership (in other
words, Full Control permissions) of the object. As the owner of the object,
you can set its access control list. These access control list settings enable you
to set object permissions on a user-by-user ,or user group-by-user group,
basis.
You can set the following permissions on an object:
BrowseSee the object in the Object Browser
ReadOpen the object in its editor to view its definition
WriteModify the objects definition in the appropriate editor and create

new objects in the parent object. For example, add a new metirc in a
report or add a new report to a document.
DeleteDelete the object
ControlModify the objects access control list
UseUse the object to define another object
ExecuteExecute the object as part of a report

To modify the permissions of an object:
1 Right-click the object and select Properties.
2 In the Properties window, in the Categories list, select Security.
3 If you want to add a user or user group, click Add.
4 In the Select Users and Groups window, select the user or user group.
You may need to select the Show users check box to see the
members of the group in the list.
5 Click OK.
6 In the Properties window, under Permissions, in the Object drop-down

list, select the permissions you want to assign to the user or user group.

IfInyou want to set permissions individually you can select Custom.

the Access Control Entry for window, you can grant or deny
individual permissions:
Access Control Entry for Window
While users may have permissions to write or edit an object, they are
not able to do so if they do not also have the privilege to use the editor
for that object. Only the administrator can assign privileges to use the
different object editors. For more information about assigning
privileges, see the MicroStrategy Administration: Configuration and
Security course.
7 In the Properties window, click OK.
Security Filters
Like report filters, security filters narrow down a result set according to
specified conditions. However, their purpose is to ensure that users see only
the data to which they have the appropriate access. For example, if a sales
manager for the UK is allowed to see data only for his own country, then the
administrator for the MicroStrategy environment can create a filter that
qualifies on the Country attribute to return only UK data. This filter can serve
as the UK Sales Manager's security filter, so the condition "Country = UK" is
automatically included in the WHERE clause of the SQL for all reports that
the UK sales manager executes.

As you can see, security filters are assigned on a per-user basis. Every report
the user runs uses both its security filter and the standard report filter.
Only an administrator has the ability to assign security filters.

As a report developer, it is important for you to realize that your end-users
might receive different result sets for the same report because of their
security filters.
In addition to object-level security and security filters, there are other

security-related concepts that can affect report results. As a report developer,
you should work with the administrator of your MicroStrategy environment
to ensure that the target users for your reports see only the information that
is relevant to them.


Lesson Summary
In this lesson, you learned the following:
The MicroStrategy metadata stores application objects, configuration

objects, and schema objects.
A report consists of a template, filter, and view. Views are a feature of

MicroStrategy OLAP Services.
A three-tier business intelligence architecture consists of the data

warehouse, metadata, Intelligence Server, and Developer.
A four-tier business intelligence architecture consists of the data

warehouse, metadata, Intelligence Server, Developer, and MicroStrategy
Web.
The report execution query flow is the process by which Developer and
Intelligence Server generate reports in a three-tier architecture.
Intelligence Server can retain the definition and results of every report
that it runs to improve query response time the next time those same
reports are run. These stored result sets are called report caches.
Intelligence Server identifies valid report caches by matching various

keys.
Re-executing a report means running the report against the data

warehouse, thus bypassing any valid cache that might exist for that
report.
As a report developer, you should be aware of object-level security,

security filters, and other security features that might affect the results
end-users see when they run reports.
40 Lesson Summary 2014 MicroStrategy Inc.

2
ADVANCED METRICS
Lesson Description
In the MicroStrategy Developer: Reporting Essentials course, you learned how

to create basic metrics. This lesson takes that knowledge to a deeper level.
In this lesson, you will create various types of advanced metrics, such as level,
non-aggregatable, conditional, and transformation metrics. These metrics
empower you to answer questions like, What percentage of total company
revenue did my business unit generate?, Are inventory levels being
consistently replenished at the beginning of each week?, or What was the
profit margin last year at this time?.

2 Advanced Metrics MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Create level, non-aggregatable, transformation, conditional, and nested
metrics. Describe the settings associated with these types of metrics. Execute
reports using advanced metrics.
Create level metrics and explain their use in percent-to-total

calculations. (Page 43)
Describe and create non-aggregatable metrics. (Page 66)
Describe and create transformation metrics. (Page 70)
Save base formulas for reuse in creating other metrics. (Page 76)
Create conditional metrics. (Page 77)
Create nested metrics that involve multiple formulas calculated at different

levels. (Page 80)
Use advanced functions to create metrics. (Page 83)
Define custom subtotals to use different naming conventions for totals and
create user-defined subtotals that use nonstandard subtotal
functions. (Page 94)

MicroStrategy Developer: Advanced Reporting Advanced Metrics 2
Level Metrics

Create level metrics and explain their use in percent-to-total calculations.
What Are Level Metrics?

Level, or dimensionality, enables you to determine the attribute level at which
a metric is calculated. However, you can specify the grouping and filtering
involved in a metric calculation.
By default, all metrics calculate at the report level, which means that the
attributes on the report template dictate the level at which the metric
calculates. However, you may specify any attributes as the calculation level of a
metric. The metric-specified attribute levels override the default report level.
The Metric Editor and Level Metrics

When you define the level for a metric, you need to consider the following
settings:
Target
Grouping
Filtering
2014 MicroStrategy Inc. Level Metrics 43

The following image shows the Level (Dimensionality) pane in the Metric
Editor:
Metric EditorLevel (Dimensionality Pane)
Target
The target is the attribute level at which the metric calculates. Any attribute or
hierarchy can be the target.
Grouping
Grouping determines how the metric aggregates. The option you choose for
the grouping of a metric affects the GROUP BY clause of the SQL pass that
calculates the metric. The following grouping options are available in the
Metric Editor for level metrics:
StandardGroups by the attribute level specified as the target
NoneExcludes the target (and its children) for the report grouping. This
setting calculates one total for the target attribute and any of its child
attributes that are included on the template.
44 Level Metrics 2014 MicroStrategy Inc.

Filtering
The filtering setting governs the relationship between the report filter and the
calculation of the metric. The following filtering options are available in the
Metric Editor:
The filtering setting only applies to filtering criteria that are related to
the attribute that is specified as the target. A report filter is related to the
target if it qualifies on an attribute that belongs to the same hierarchy
and there is a direct relationship between the target attribute and the
report filter.
StandardEnables the report filter to interact as usual in the metric

calculation. The metric calculates only for the elements included in the
report filter definition. The filter criteria for the report is included in the
WHERE clause of the SQL pass that calculates the metric.
AbsoluteRaises the level of the report filter to that of the target
IgnoreCompletely ignores any related report filtering criteria. Any related

report filtering conditions are not included anywhere in SQL pass that
calculates the metric.
NoneDirects the MicroStrategy Engine to use a particular fact table to

calculate a metric. You typically use this filtering option in combination
with the None grouping option.
Using Level Metrics

You are the manager of the Northeast and Mid-Atlantic regions. You want to
assess how each of the call centers in your region contributed to the overall
revenue for the region. How can you accomplish this analysis using level
metrics?
Solution
To answer this question:
1 Create a simple metric that calculates revenue at the report level.
2 Create a level metric using the Revenue fact and set the target to Region.
3 Create a third metric that calculates percent contribution by dividing the

first metric by the second metric.

4 Create a report with Region, Call Center, and the first and the third metrics.
Grouping Set to Standard
Business Scenario
Your company has recently kicked off a new advertising campaign targeted at
certain high-growth opportunity areas. In your regions, the high-growth areas
are the Boston, New York, and Washington, DC, call centers, so you need to
focus your analysis on these areas. One of your requirements is to see how the
revenue for each of these areas compares to the other areas in its region.
Filtering Set to Standard
When you select Standard filtering, all of the report filter elements are applied
to the calculation of the metric. In the report SQL, the report filter is evaluated
in the WHERE clause of the SQL pass that calculates the metric.
In the business scenario, the value for the Regional Revenue level metric for
the Mid-Atlantic region includes revenue only for the Washington, DC call
center. The metric calculates this way because the Washington DC call center is
the only call center in the Mid-Atlantic region that is included in the report
filter. Also, because you are using Standard grouping, the Regional Revenue
metric calculates at the target attribute level of Region. The following image
shows the report with values for the level metric with Standard filtering:
Regional RevenueFiltering Set to Standard
Ifdimensionality
you include the totals for the Regional Revenue level metric, they are
aware and display the correct figures.

The SQL for this report looks like the following:

select a12.[REGION_ID] AS REGION_ID,
sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [CITY_CTR_SLS] a11, [LU_CALL_CTR] a12
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID]
and a11.[CALL_CTR_ID] in (5, 11, 12)
group by a12.[REGION_ID]
All SQL examples in this lesson use Microsoft Access 2007 SQL
syntax.
Filtering Set to Absolute
Using the same business scenario, if you select Absolute filtering, the Regional
Revenue level metric calculates only for those regions that have at least one call
center present within the report filter. Unlike Standard filtering, the Regional
Revenue is the total of all call centers within that region, not just the call
centers included in the report filter. Grouping continues to occur at the target
attribute level of Region since the grouping option is set to Standard. The
following image shows the report with values for the level metric with Absolute
filtering:
Regional RevenueFiltering Set to Absolute

The Regional Revenue level metric gets resolved in multiple passes of SQL. The
report filter is present in the WHERE clause of the first SQL pass. The
MicroStrategy Engine brings back only the target attribute elements (Region)
where the report filter elements (Call Center) exist within the target attribute
(Region). The SQL for this metric looks like the following:
insert into ZZTSX01009ESQ001
select distinct s21.[REGION_ID] AS REGION_ID
from [LU_CALL_CTR] s21
where s21.[CALL_CTR_ID] in (5, 11, 12)
insert into ZZTSX01009EMD002

from [CITY_CTR_SLS] a11, [LU_CALL_CTR] a12,
[ZZTSX01009ESQ001] pa13
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and
a12.[REGION_ID] = pa13.[REGION_ID]
Filtering Set to Ignore
When you select Ignore filtering, the report filter elements that are directly
related to the target attribute are not included in the WHERE clause of the SQL
pass that calculates the metric.

Using the same business scenario, since the call centers are directly related to
the target attribute of Region, the entire report filter is ignored. However, if the
report filter contained other attribute elements, like years, those conditions
would not be ignored because they are not directly related to the target
attribute of Region. The following image shows the report with values for the
level metric with Ignore filtering:
Regional RevenueFiltering Set to Ignore
The SQL for this metric looks like the following:

from [CITY_CTR_SLS] a11,[LU_CALL_CTR] a12

Grouping Set to None
Business Scenario
You now want to perform an analysis that is similar to the previous example
but with a slightly different perspective. For example, you may want to analyze
the following questions:
How did the revenue for each of the three call centers (Boston, New York,
and Washington, D.C.) compare to the revenue of the entire company?
How did the revenue of each of the three call centers compare to the total of
only the three call centers?
The answers to these questions give you an insight into how the new
advertising campaign is being received in the targeted areas of your region.
They also give you a broader perspective on its effects on the company.
Filtering Set to Standard
With grouping set to None, the metric does not group by the target attribute or
any child attribute directly related to the target attribute specified within the
level metric. Therefore, in this particular example, there is no GROUP BY
clause in the SQL pass that calculates the metric. With filtering set to Standard,
the report filter elements are included in the WHERE clause.
The None grouping option applies only to the target attribute specified in the
level metric or attributes that are related as children of the target. If the
following report also contained the Year attribute, the report SQL would still
include a GROUP BY clause. The GROUP BY clause would contain Year since
the Year attribute is not directly related to the metric target of Region. The
GROUP BY clause would not contain Call Center or Region since Call Center is
a child of Region and Region is the target of the metric.

Using the same business scenario, this metric calculates the total revenue for
all call centers present in the report filter. The following image shows the
report with values for level metric with Standard filtering:
Regional RevenueFiltering Set to Standard

select sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [CITY_CTR_SLS] a11
where a11.[CALL_CTR_ID] in (5, 11, 12)
Filtering Set to Absolute
With Grouping set to None, the metric does not group by anything directly
related to the target attribute specified for the level metric. Since the only
attributes on the report are related to the target, there is no GROUP BY clause
in the SQL pass that calculates the metric. When filtering is set to Absolute, the
level metric is resolved in multiple passes of SQL. The report filter is present in
the WHERE clause of the first SQL pass.

Using the same business scenario, this report lists only the regions that have
call centers present in the report filter. The Regional Revenue metric
calculation takes into account only these listed regions, but it includes the total
for all call centers in these regions, not just the call centers specified in the
report filter. The following image shows the report with values for the level
metric with Absolute filtering:
Regional RevenueFiltering Set to Absolute

insert into ZZTSX0100CKSQ001
select distinct s21.[REGION_ID] AS REGION_ID
from [LU_CALL_CTR] s21
where s21.[CALL_CTR_ID] in (5, 11, 12)
insert into ZZTSX0100CKMD002

from [CITY_CTR_SLS] a11, [LU_CALL_CTR] a12,
[ZZTSX0100CKSQ001] pa13
where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and
a12.[REGION_ID] = pa13.[REGION_ID]

Filtering Set to Ignore
With filtering set to Ignore, all the report filtering criteria directly related to the
target attribute are ignored, and the MicroStrategy Engine automatically
removes the related report filtering criteria from the WHERE clause of the SQL
pass that calculates the metric. With grouping set to None, the level metric also
does not display any grouping on the report since the only attributes on the
report are directly related to the target of Region.
Using the same business scenario, the Regional Revenue metric calculates the
total company revenue for all call centers in all regions, not just the three call
centers that are included in the report filter. The following image shows the
report with values for the level metric with Ignore filtering:
Regional RevenueFiltering Set to Ignore

from [YR_CATEGORY_SLS] a11

Filtering Set to None
You typically use the None filtering option in combination with the None
grouping option. The None filtering option enables you to specify the fact table
that you want the MicroStrategy Engine to use to calculate a metric. You add as
many target attributes as necessary to the metric to force it to calculate against
a specific fact table. Any target attribute set to the None filtering option
borrows its filtering criteria from the other target attributes specified in the
dimensionality of the metric, enabling you to choose the fact table without
altering the original intent of the report.
For example, using the same business scenario, if you want the MicroStrategy
Engine to use the ITEM_EMP_SLS fact table instead of the CITY_CTR_SLS
fact table (an aggregate fact table) to calculate the metric, you include the Item
attribute as a target of the level metric. Since the Item attribute is found in the
ITEM_EMP_SLS table and not the CITY_CTR_SLS table, including Item as a
target forces the MicroStrategy Engine to use the ITEM_EMP_SLS fact table.
If data is stored differently in the ITEM_EMP_SLS table, results could be
different. The following image shows the report with values for the level metric
with None filtering:
Regional RevenueFiltering Set to None
The SQL statements for these metrics look like the following:
Regional Revenue (Target=Region, Filtering=Standard, Grouping=Standard)

from [CITY_CTR_SLS] a11,[LU_CALL_CTR] a12


Regional Revenue (Target1=Region, Filtering=Standard,

Grouping=Standard, Target2=Item, Filtering=None, Grouping=None)
from [ITEM_EMP_SLS] a11, [LU_EMPLOYEE] a12,
[LU_CALL_CTR] a13
where a11.[EMP_ID] = a12.[EMP_ID] and
a12.[CALL_CTR_ID] = a13.[CALL_CTR_ID]
Defining Level Metrics

Creating a level metric is similar to creating a simple metric. You must use a
Metric Editor to create it. Once you define a simple metric formula, you have to
enter a level definition for your metric.
To create a level metric:
1 In the Metric Editor, in the Definition pane, define a simple metric.
2 In the upper pane, select Level (Dimensionality).
3 In the Object Browser, drag the desired target attributes to the Level
(Dimensionality) pane.
Incalculates
general, you should retain the Report Level target, so the metric
at the report level for any attributes on the template that
are not related to your other target attributes.
4 In the Level (Dimensionality) pane, in the Filtering and Grouping

drop-down lists for each target attribute, select the appropriate filtering
and grouping settings.
5 Click Save and Close.

Removing the Report Level Target
Business Scenario
You want to compare the revenue performance of certain targeted areas to the
revenue of the entire company for all time.
Solution
Earlier in this lesson, you learned how to use Ignore filtering and None
grouping to create an All Revenue" level metric. If you have multiple
hierarchies represented on the report template, this method requires you to
include multiple attribute targets in your level metric. However, another
option is to remove the default Report Level target and add any attribute as the
target with its grouping set to None.
The presence of the default Report Level target on a metric tells the
MicroStrategy Engine to group by all the attribute IDs found on the template.
By removing the Report Level target from the metric and selecting None for the
grouping of any other available target attribute, the MicroStrategy Engine
understands that there should not be a GROUP BY clause in the SQL pass that
calculates the metric. You can use any attribute for this purpose. You do not
need to add more than one attribute unless specific filtering behavior is
required for the metric.
Ifattributes,
specific filtering behavior is required, you need to add other target
but you should always select None grouping.

Removing the Report Level target is a quick and easy way to do something that
would otherwise involve multiple steps. It is helpful if you have many
hierarchies represented on the report template that need to be included in the
metric calculation to obtain the desired outcome. The following image shows
the report with values for a metric with removed Report Level:
All RevenueRemove Report Level
In this report, the All Revenue level metric has the following dimensionality:
All RevenueWithout Report Level

The Report Level target is noticeably absent in the Level (Dimensionality)

pane. The metric returns all revenue from the data warehouse because it is not
grouping by any attribute on the template (and because there is no criteria
specified in the report filter). For this example, the Year attribute is the target,
but any attribute would produce the same results. You could even choose an
unrelated attribute like Subcategory and the result set would be the same for
the All Revenue level metric.
If you want to achieve the same result set by defining your level metric with the
Report Level target still present, you need to define the level metric as follows:
Level MetricOne Attribute from All Dimensions
When you keep the Report Level target, you also need to include every
attribute that is on the template as a target 0f the level metric. You select None
grouping for each of these attributes. This level metric produces the same
result (a value of $35,023,708) as the previous example, but its definition has
more components due to the multiple target attributes.


Review: Level Metrics
Identify the target, filtering, and grouping for the level metrics in the following
reports.
_______________________________________
_______________________________________
_______________________________________
2014 MicroStrategy Inc. Review: Level Metrics 59

_______________________________________
_______________________________________
_______________________________________
_______________________________________
_______________________________________
_______________________________________
60 Review: Level Metrics 2014 MicroStrategy Inc.

The following report displays nearly every type of level metric you learned
about in this lesson. If you assume that the target is Region, can you determine
the filtering and grouping for each level metric?
_______________________________________
_______________________________________
_______________________________________

Review Solutions:
1 Target: Region
Filtering: Standard
Grouping: Standard
2 Target: Region
Filtering: Absolute or Ignore
Grouping: Standard
3 Target: Region
Filtering: Standard
Grouping: None
Solutions for Image with Various Level Metrics
Metric Filtering Grouping
A Absolute None
B Absolute or Ignore Standard
C Ignore None
D Absolute or Ignore Standard
E Standard None
F Standard Standard

The following report shows the filtering and grouping settings for each level
metric in the previous report. For display purposes, the names of the metrics
are abbreviated as follows:
ABSAbsolute filtering
IGNIgnore filtering
NONENone grouping
STDStandard filtering or standard grouping
Read the following level metric description and identify the target, filtering,
and grouping for the metric.
1 You would like to create a metric that calculates the sum of sales across all
regions, considering only the regions represented by the call centers in the
report filter. On your template, you have the Region and Call Center
attributes and the metric.
_______________________________________
_______________________________________
_______________________________________
2 You would like to create a metric to calculate the sum of units sold for each
region in Q4 2010, considering only the call centers that are included in the
report filter. The template contains the Region and Call Center attributes
and the metric.
_______________________________________
_______________________________________
_______________________________________

3 You would like to create a metric to calculate the sum of profit for 2009
across all customer states regardless of the customer cities that are included
in the report filter. The template contains the Customer State and Customer
City attributes and the metric.
_______________________________________
_______________________________________
_______________________________________

Review Solutions:
1 Target: Region
Filtering: Absolute
Grouping: None
2 Target: Region
Filtering: Standard
Grouping: Standard
3 Target: Customer State

Filtering: Ignore
Grouping: None

Non-aggregatable Metrics

Describe and create non-aggregatable metrics.
What Is a Non-aggregatable Metric?

A non-aggregatable metric is a metric that should not be summed across a
particular attribute or hierarchy. Inventory is an example of a
non-aggregatable metric. The data warehouse may keep inventory records for
every quarter. However, you do not sum these inventory records to calculate
yearly inventory figures. It makes more sense to obtain the end-on-hand or
beginning-on-hand inventory numbers for each year to analyze how inventory
changed over the course of the year.
Correct Vs. Incorrect Non-aggregatable Metrics
In this example, the End on Hand metric is defined as non-aggregatable, while

the End on Hand (Incorrect) metric is not. Notice the difference in the values
that they produce for each year. The End on Hand metric does not sum
inventory over time. Rather, it returns the inventory values for the last month
in each year. In this case, it lists the values for the last month because inventory
is recorded every month. However, the End on Hand (Incorrect) metric sums
inventory values over time, which returns invalid figures. Inventory levels were
never that high at any point during the year.
66 Non-aggregatable Metrics 2014 MicroStrategy Inc.

In this example, the End on Hand inventory metric is defined as

non-aggregatable for the Month attribute, which tells the MicroStrategy
Engine not to sum the metric values at the Month level or any attribute level
higher than Month.
You can specify a metric as non-aggregatable for an entire hierarchy

rather than a specific attribute. For example, using the Time hierarchy
indicates that a metric should be non-aggregatable across all attributes
in that hierarchy.
Defining Non-aggregatable Metrics

You define a metric as non-aggregatable in the Level (Dimensionality) pane in
the Metric Editor. The following non-aggregatable options are listed under the
grouping options for a metric.
The options are as follows:

Beginning (fact)Uses the first available value in the fact table
Ending (fact)Uses the last available value in the fact table
Beginning (lookup)Uses the first available value in the lookup table
Ending (lookup)Uses the last available value in the lookup table
2014 MicroStrategy Inc. Non-aggregatable Metrics 67

The following image shows metric defined as non-aggregatable in the level

(Dimensionality) pane in the Metric Editor:
Metric EditorGrouping Set to Ending (Fact)
The following image shows the lookup table and the fact table that can be used
when specifying a metric:
Beginning (Lookup) Versus Beginning (Fact)
68 Non-aggregatable Metrics 2014 MicroStrategy Inc.

In the illustration above, the, the fact table does not store the stock amount for
the first week of the month (Week 1). The first stock value it stores is for the
second week of the month (Week 2). In this type of scenario, if you define your
non-aggregatable metric as beginning (lookup), the MicroStrategy Engine uses
the lookup table to identify the beginning week of the month. Since the
beginning week according to the lookup table is Week 1 and there is no stock
value in the fact table for Week 1, the report result returns a null value for the
Beginning Stock (LU) metric for Month 1.
Alternatively, if you define your non-aggregatable metric with beginning

(fact), the MicroStrategy Engine looks to the Fact table to identify the
beginning week of the month. Since the beginning week is week 2 according to
the Fact table, the report result returns the value of 10 for Beginning Stock in
Month 1.
To create a non-aggregatable metric:
1 In the Metric Editor, in the Definition pane, define a simple metric.
2 In the upper pane, select Level (Dimensionality).
3 In the Level (Dimensionality) pane, in the Grouping drop-down lists select

the appropriate grouping setting: beginning (fact), ending (fact), beginning
(lookup) or ending (lookup).
2014 MicroStrategy Inc. Non-aggregatable Metrics 69

Transformation Metrics

Describe and create transformation metrics.
What Are Transformations?

Transformations are schema objects used to compare like values at different
timesfor example, this year versus last year or date versus month to date.
Transformations are useful for discovering and analyzing time-based trends in
your data.
Because transformations are schema objects, you must have the

appropriate Architect privileges to create or modify them.
You use transformations to define transformation metrics. For example,

suppose you create a metric that calculates month-to-date revenue. If you add
a Month to Date transformation to the metric, the metric would calculate
month to date revenue. The following image shows this example:
Month to Date Revenue
You can apply any transformation to the definition of a metric, and you can
apply multiple transformations to a single metric.
70 Transformation Metrics 2014 MicroStrategy Inc.

Types of Transformations
There are two types of transformations:
Expression-based transformationsThese transformations use a

mathematical formula in their definition.
Table-based transformationsThese transformations base their definitions

on a relationship table, or transformation table, in the data warehouse.
Expression-Based Transformations
You define expression-based transformations using mathematical expressions.

A transformation expression typically includes an attribute ID column, a
mathematical operator, and a constant.
For example, you might create a Last Quarter or Last Month transformation
using QUARTER_ID-1 or MONTH_ID-1 respectively.
This method works only if you store your data in a format conducive to
the transformation expression. For example, if you store your month
IDs in the format YYYYMM, the MONTH_ID1 expression does not
always work. When you apply the expression to the month ID of 201101
(January 2011) with the intention of retrieving data for the previous
month of 201012 (December 2010),it returns the month ID of 201000.
So, instead of retrieving the data for the previous month, you retrieve no
data because the month ID of 201000 does not exist.
You can also create expression-based transformations using the date and time
functions available in MicroStrategy. For example, you can define an
expression-based transformation as follows:
AddMonths(Date_ID, -1)
This formula calculates a Last Month transformation using the AddMonths

function instead of a mathematical expression. The AddMonths function
returns a date or timestamp offset (+ or -) by the specified number of months
(in this example, - 1 month).
2014 MicroStrategy Inc. Transformation Metrics 71

Table-Based Transformations
Table-based transformations reference a physical table in the data warehouse

that defines the transformation from one time period to another. The following
illustration shows examples of transformation tables:
Transformation Tables
The following illustration shows how transformation tables act as

intermediaries in the metric join path when you use transformation metrics on
reports:
Table-Based Transformation

Transformation Components
All transformations have the following components:
Member attributesThese attributes are the ones to which the

transformation applies
For example, if the transformation is Previous Year, then the member

attributes might be Year, Quarter, Month, and Day.
Member expressionsEach member attribute has a corresponding

expression.
For expression-based transformations, the member expression is a

mathematical expression.
For table-based transformations, the member expression is a column in

a transformation table in the data warehouse.
Aexpression-based
single transformation can use a combination of table-based and
transformations. For example, you might create a
Last Year transformation based on the Year, Month, and Day
attributes. Year might be based on the expression Year_ID 1,
but Month and Day might be table-based transformations because
their IDs are not conducive to expression-based transformations.
Member tablesThese tables store the data for the member attributes.
For expression-based transformations, the member tables are generally

lookup tables that correspond to the attribute being transformed.
For table-based transformations, the member tables are the

transformation tables that store the relationship.
The mapping type determines the way the transformation is created based
on the nature of the data. You can select one of the following mapping
types:
One-to-oneA typical one-to-one relationship is comparing this year to

last year. One day or month this year maps to exactly one day or month
last year.
Many-to-manyA typical many-to-many relationship is comparing a

given date to the year-to-date total. For a given date, there are many
other dates included in its year-to-date calculation.

Creating a Transformation Metric

Creating a transformation metric involves two steps. First, you must define the
transformation object in the Transformation Editor.
This task is typically performed by the project architect or the person in

charge of schema object creation.
Second, you must create a transformation metric. You can create a

transformation metric using the Metric Editor, or you can create it as a derived
metric.
To create a transformation metric using the Metric Editor:
1 In the Metric Editor, in the Definition pane, define the formula for the
metric.
2 In the upper pane, select Transformation.
3 In the Object Browser, drag the predefined transformation object you want
use to the Transformations pane.
The following image shows the transformation metric in the Metric Editor:
Metric EditorTransformation Pane

To create a transformation metric as a derived metric:
1 Execute the report in which you want to create a transformation metric.
2 In the report window, select the header of the metric on which you want to
base your transformation metric.
3 On the menu, point to Insert, point to Transformations, point to the

transformation object you want to use, and select the type of
transformation values you want to display.
For each transformation metric, you can select what you want to
calculate:
Normal displays unit figures for both the current values and the
corresponding values for the interval selected.
Variance displays the difference between the current values and the
corresponding values for the interval selected.
Variance percentage calculates the difference, expressed as a

percentage, between the current values and the corresponding ones for
the interval selected.
4 In the message window, click Yes.
The transformation metric is added to the report results. Remember

that a derived transformation metric is saved locally to the report on
which you define it, so you cannot reuse it on other reports. Also, this
functionality is only available with OLAP Services.

Base Formulas
Save base formulas for reuse in creating other metrics.
Reusing Formulas
You can save the formulas of simple metrics as separate objects that you can
then reuse to create other metrics. These formula objects are called base
formulas. Instead of redefining a formula you frequently use, you can save it as
a base formula. You can drag base formulas from the Object Browser to the
Definition pane when you create a new metric.
The expression to create a base formula cannot have more than one
dimensionality {~}. For instance SUM(FACT1){~}+SUM(FACT2){~})
is invalid, but (SUM(FACT1)+SUM(FACT2)){~} is valid.
Base formulas are useful for object maintenance. If the formula for a simple
metric changes, you only need to edit the definition once in the base formula
object. All metrics that use that base formula are automatically updated with
the new definition. Base formulas also help ensure that report designers define
their metrics using a consistent definition, which is helpful if the formula is
complex and likely to be entered incorrectly. Finally, base formulas help you
categorize and identify the metrics that use the same formula since you can
search for metrics that contain a particular base formula object.
To save a metric formula as a base formula:
1 In the Metric Editor, in the Definition pane, define the metric formula.
2 On the File menu, select Save As.
3 In the Save As window, browse to the location in which you want to save the
base formula.
4 In the Object name box, type a name for the base formula, and select Base
Formula in the Save as type drop-down list.
5 Click Save.
7 Close the Metric Editor.
76 Base Formulas 2014 MicroStrategy Inc.

Conditional Metrics

Create conditional metrics.
A conditional metric contains its own filter. The filter of a conditional metric is
completely separate and independent of any filtering criteria specified in a
report filter. For example, you may want to create a report that contains
multiple metrics on its template and apply a time filter to most of these
metrics. However, you also want one metric to calculate all 2011 revenue
regardless of the filter on the report. You can create a conditional metric that
ignores the report filter and calculates all 2011 revenue.
While you can only apply one filter to the definition of a metric, that single
filter can contain multiple filtering conditions.
You can apply conditionality only to simple metrics.

Creating a Conditional Metric
To add a filter to a metric definition:
1 In the Metric Editor, in the upper pane, select Condition.
2 In the Object Browser, drag the filter you want to use to the Condition pane.
2014 MicroStrategy Inc. Conditional Metrics 77

Removing Related Report Filter Elements

By default, a conditional metric ignores any report filter elements that are
related to its conditionality filter. For example, suppose you create a
conditional metric to calculate revenue for the month of September. If you use
this conditional metric on a report that has another time-related filter (for
example, Year=2011), the conditional metric ignores the report filter and
calculates revenue for all instances of September in your data warehouse
regardless of the year.
You can change this behavior so that the related report filter elements are not
ignored. In the previous example, changing the default behavior causes the
September Revenue metric to calculate revenue only for September 2011. To
change the default behavior, you need to clear the Remove related report filter
elements check box, which is shown in the following image:
Conditional MetricsAdvanced Options
The Embedding method options control how a report filter and metric
filter interact when both filters qualify on metrics. For more information
on this setting, see Conditional Metrics: Embedding Method starting
on page 400.
Ifmerged
you use a view filter on a report, the view filter conditions are
with the conditional metric.
To change how a metric condition interacts with related report filter elements:
1 In the Metric Editor, in the upper pane, select Condition.
2 In the Condition pane, click Advanced.
78 Conditional Metrics 2014 MicroStrategy Inc.

3 By default, related report filtering criteria is ignored. If you do not want to

ignore the report filter, in the Conditions advanced options window, clear
the Remove related report filter elements check box.
When you enable the Remember option setting, the current state
of the Remove related report filter elements check box (enabled or
disabled) is saved as the default setting for all future conditional
metrics. However, you can still manually change the Remove related
report filter elements setting at any time.
4 Click OK.
5 In the Metric Editor, click Save and Close.
The following example demonstrates the difference in conditional metric

values when you remove related report filter elements and when you do not.
The Winter Revenue metric is defined with the Revenue fact and a filter on the
following months of the year: January, February, and March. The Summer
Revenue metric is similar except its filter includes the months of July, August,
and September. With these conditional metrics, you can create either of the
following reports:
Report with Related Report Filter Elements
Clearly, the metrics on the report on the left ignore the related report filter
element, Year = 2011. This behavior is the default for conditional metrics when
you select the Remove related report filter elements check box.
2014 MicroStrategy Inc. Conditional Metrics 79

Nested Metrics

Create nested metrics that involve multiple formulas calculated at different
levels.
An example is the best way to describe the purpose and use of a nested metric
(sometimes also referred to as a nested aggregation metric).
Imagine a report that requires a metric to calculate the average employee

revenue for each region. The report resembles the following:
Nested MetricsAverage Employee Revenue
The Average Employee Revenue metrics formula is defined as:
AVG(Sum(Revenue){~, Employee}){~, Region}
In this example, note how the metric has an inner formula, Sum(Revenue) at
the Employee level, and an outer formula that takes the Average of the inner
formula for each Region.
80 Nested Metrics 2014 MicroStrategy Inc.

If you were to take the formulas apart and calculate each formula separately,
you can see how nested metrics really work. Notice the report below, where the
Revenue metric is being summed for each employee in the Central region. Also,
notice the Average subtotal value across all employees in the Central region.
Average Revenue by Employee for the Central Region
This report is a representation of the inner formula in the nested metric

described earlier. Notice how the average subtotal matches the value displayed
for the Central region in the first report. These values match because the nested
metric is able to first calculate Sum(Revenue)at the Employee level and then
use this result set to calculate the average revenue across all employees at the
Region level.
By using nested aggregation, you can calculate an inner metric at one

particular attribute level, store this as an intermediate result set, and perform
another calculation on this result set at an entirely different attribute level.
This functionality and flexibility is very useful when fact tables in the data
warehouse do not store data at the level desired for a specific type of analysis.
Applying {~, Employee} to the Sum(Revenue) metric means that

the metric calculates at the Employee level regardless of what attributes
display on the final report.
In their structure, nested metrics have the following characteristics:
They are based on either facts or attributes.
They place one aggregation function inside another.
They include one or more level definitions.
They may apply conditions to its calculations.
They may include transformations.
2014 MicroStrategy Inc. Nested Metrics 81

Nested metrics are basically a special type of simple metric in which one simple
metric calculation is performed to enable the calculation of another simple
metric. You only use them when the level at which data is stored in the data
warehouse precludes being able to calculate and analyze data at the desired
level. Nested metrics provide an alternative to modifying physical data
warehouse tables.
To create a nested metric:
1 In the Metric Editor, in the Definition pane, define the formula.
For example, the nested metric from the earlier example has the
following syntax: Avg(Sum (Revenue) {~, Employee}) {~,
Region}. In this formula, Revenue is a fact, not a metric.
2 Click Validate.
Iflevel,
you want the outer formula of the metric to calculate at the report
you can create the nested metric as a compound metric. For
example, you define a metric as Sum(Revenue) with a target of
Employee. You use this metric as the inner formula of a second metric
that uses another aggregate operator (AVG) and the default Report
Level target. The compound metric would be defined as follows:
Avg(Employee Revenue) {~, Report Level}.
82 Nested Metrics 2014 MicroStrategy Inc.

Advanced Functions

Use advanced functions to create metrics.
Overview of Built-in Advanced Functions

MicroStrategy offers over 200 built-in functions, including a wide range of
sophisticated OLAP, statistical, and financial functions.
Resolving Advanced Functions
In the MicroStrategy environment, you can resolve advanced functions in the

following ways:
Through the relational database
Through the Analytical Engine
Many relational database servers natively support advanced functions (such as

RANK, MOVINGSUM, and STDEV). When a report includes one of these
advanced functions, Intelligence Server first determines if the database
supports that particular function. If the database supports the function, the
MicroStrategy Engine generates SQL to have the database server perform the
calculation. If the database does not support the function, the Analytical
Engine performs the calculation.
2014 MicroStrategy Inc. Advanced Functions 83

Accessing Analytical Functions in Developer
All of the built-in functions that MicroStrategy provides are stored in the
Schema Objects\Functions and Operators folder.
Metric EditorFunctions and Operators Folder
To use the analytical functions and operators:
1 In the Metric Editor, in the Object Browser, browse to the Functions and
Operators folder.
2 In the Functions and Operators folder, drag the desired function or

operator to the Definition pane.
The syntax for a particular function or operator is displayed at the

bottom of the Metric Editor.
3 In the Object Browser, drag the desired metric that you want to use between
the parentheses of the function or operator in the Definition pane.
4 In the Definition pane, select the entire function or operator.
84 Advanced Functions 2014 MicroStrategy Inc.

5 Right-click the selected function or operator and select <Function or

Operator Name> parameters.
6 In the <Function or Operator Name> Parameters window, set the

necessary parameters for the function or operator.
7 Click OK.
Another way to access the functions and operators (as well as their parameters)
is through the Insert Function Wizard. This wizard takes you through the
process of defining a metric formula. You access the wizard by clicking the f(x)
button on the toolbar of the Definition pane:
Metric Editor Insert Function Wizard

Count Metrics
Count metrics are typically based on attributes and count the number of
attribute elements that meet your specified criteria.
In a retail business environment, you might want to count the number of items
available at any time in any store. On the other hand, you might want to count
only the number of items available in a certain catalog. You may also want to
count only the number of items that sold in a particular year.
To create a count metric:
1 In the Metric Editor, in the Object Browser, browse to the Basic Functions
folder.
2 In the Basic Functions folder, drag Count to the Definition pane.
3 In the Object Browser, drag the attribute between the parentheses of the
Count function in the Definition pane.
Asattribute,
an alternative to selecting the Count function and then the
you can just drag the desired attribute to the Definition
pane. The Count function is the default for attributes.
4 Define additional metric properties as necessary.
By default, when you place a count metric on a report, it is calculated using the
lookup table of the attribute. For example, if you place a Count (Item) metric
on a report, the items are counted using the Lookup_Item table. This behavior
is the default because the MicroStrategy Engine always looks to the smallest
(and thus most efficient) table that contains enough data to resolve the report.

However, you may want to change the default behavior and force the
MicroStrategy Engine to use a fact table instead of the lookup table. For
example, suppose you create a Count Item metric defined as Count(Item).
You then place the Month attribute and the Count Item metric on a report.
Based on the tables in the following illustration, the MicroStrategy Engine
retrieves the item count from the lookup table, which is the smaller of the two
tables.
Calculate Count Against Lookup Table Versus Fact Table
However, retrieving a count of items from the lookup table for this report
results in a count of every item for every month. To answer the question, How
many of my available items did I sell each month?, you need the
MicroStrategy Engine to count items using the fact table, not the lookup table.
Any time you are counting an attribute (Item) in relation to an unrelated
attribute (Month), you must use a fact table since fact tables establish the
relationships between unrelated attributes.
You can adjust the behavior of the MicroStrategy Engine by changing the
parameters that govern the behavior of the Count function. In this example,
you could specify that the count should be performed using a specific fact. This
configuration forces the MicroStrategy Engine to count only those items having
a value for that fact, thereby forcing it to count from the fact table.

When using count metrics, the final count number you see in the result set
depends on the table against which the count is performed, especially when
your data warehouse includes aggregate fact tables. A Count Item metric
processed against a detail fact table, which tracks every item sold in every order
on every day and can list the same item multiple times, will likely return a
different number of items than the same metric processed against an aggregate
fact table, which tracks the items sold over all time by each employee and only
lists each item once. Also, the table against which a report is processed depends
on the attributes and filters in a report.
To change the table against which a count metric is processed:
1 In the Metric Editor, validate the syntax for the count metric formula.
2 In the Definition pane, select Count.
3 Right-click Count and click Count parameters.
4 In the Count parameters window, on the Parameters tab, in the Fact ID

drop-down list, select a fact that forces the MicroStrategy Engine to count
from a table that establishes a relationship between the attribute you are
counting and the selected fact.
5 Set additional parameters as necessary.
6 Click OK.
Advanced FunctionsSelect Examples

In this topic, you will learn about select examples of some commonly used
advanced functions.
For a complete list of functions and their definitions, see the

MicroStrategy Functions Reference product manual.

Rank
When using the Rank function (located in the Functions and

Operators/Functions/Rank and NTile Functions folder), you specify the metric
to be ranked and whether you want to rank it in ascending or descending order.
Another parameter enables you to specify a break-by attribute.
The level of a rank metric depends on the level of the simple metric that is
being ranked. A rank metric is always a compound metric that inherits the level
of the simple metric in its definition.
For example, Rank(Revenue)<Ascending=False> combined with the

Customer attribute on a report assigns the customer with the highest revenue a
ranking of 1. Adding a parameter to break by the Year attribute assigns the
customer with the highest revenue with a ranking of 1 for each year on the
report.
You can also create a rank metric by inserting a derived metric. As with all
derived metrics, a derived rank metric is local to the report. It is not a separate
metric object that can be reused on other reports. As such, if you think that you
might need to use the rank metric multiple times on several different reports,
your best choice is to create it as a metric object in the Metric Editor.
RunningSum, RunningAvg, MovingSum and MovingAvg
The RunningSum function (located in the Functions and

Operators/Functions/OLAP Functions folder) requires a metric as input and
gives a cumulative total of values based on a sort order specified in the metric
definition. The sort order is not based on the report level.
For example, suppose you want a report that shows dates, sales, and
month-to-date sales. You can define the Month-to-Date Sales metric as
RunningSum(Sales)<Sort Ascending by Date>. There are many
similar OLAP functions, such as RunningAvg, MovingSum, and MovingAvg.
Round
The Round function (located in the Functions and Operators/Plug-In

Packages/Math Functions folder) is used to round real numbers up or down
based on their decimal values into integers.

For example, suppose you need to round your revenue values up (or down) to
the nearest thousand dollars. You can use the Round function in the following
metric definition:
(Round((Revenue {~+} / 1000)) * 1000)
Dividing revenue by 1,000 converts revenue values into real numbers with only
the value in thousands to the left of the decimal point. Therefore, a number like
13,985 becomes 13.985. When rounded, a number like 13.985 becomes 14. By
multiplying the rounded value of 14 by 1,000, it becomes 14,000. This metric
definition also rounds down. For example, 13,121 rounds down to 13,000.
NTile
The NTile function (located in the Functions and Operators/Functions/Rank

and NTile Functions folder) categorizes the values for a metric into a certain
number of groups, or tiles. You can specify NTile parameters, such as the
number of groups and how these groups are assigned (ascending or
descending).
For example, you may want to see which customer cities are in the top 25%, the
next 25%, and so forth based on revenue. You can perform this analysis using
the NTile function in conjunction with the Revenue metric. Because you want
the results in quartiles, you define the number of tiles as four. If you want the
top 25% of your customer cities to be in the first quartile, you set the Ascending
parameter to False.

You can also specify a break-by attribute for the NTile function. For example,
selecting Customer State as a break-by attribute is useful for categorizing the
customer cities in each customer state into quartiles.
NTile Function Example
Date and Time Functions
MicroStrategy offers advanced date and time functions (located in the

Functions and Operators/Functions/Date and Time Functions folder) as part
of its built-in function library. These functions are always processed by the
relational database server and not the Analytical Engine. Therefore, to use the
date and time functions, you must run your reports against a relational
database server that supports them.
The following table lists some of the available date and time functions:
Function Description
AddDays Returns a date or timestamp plus an offset (+ or -) of

n days
AddMonths Returns a date or timestamp plus an offset (+ or -) of

n months

Function Description
DayOfYear Returns the number of the day in the year of the

input date
DaysBetween Returns the difference in days between two dates as

an integer
MonthEndDate Returns the date of the last day of the month in

which the date or timestamp occurs
MonthsBetween Returns the difference in months between two dates

as an integer
MonthStartDate Returns the date of the first day of the month in

which the date or timestamp occurs
YearEndDate Returns the date of the last day of the year in which
the date or timestamp occurs
YearStartDate Returns the date of the first day of the year in which
the date or timestamp occurs
For a complete list of date and time functions, see the MicroStrategy
Functions Reference product manual.
For example, to determine the number of months between two specific dates,
you can create the following metric:
MonthsBetween(Max([Hire Date]), CurrentDate())

This metric uses the CurrentDate function to identify the current date provided
by the database timer. It also uses an existing metric defined as MAX(Hire
Date) to identify the hire date for any employee. When you place this metric
on a report with Employee and Hire Date, the result looks like the following:
Date and Time Function Example
The report was executed on June 17, 2013, so the MonthsBetween function
calculates the number of months between this date (the current date) and the
hire date for each employee.

Advanced Subtotals

Define custom subtotals to use different naming conventions for totals and
create user-defined subtotals that use nonstandard subtotal functions.
Custom Subtotals
By default, when you add subtotals to a report, the same subtotal function is
used for all metrics on the report. The name of the subtotal also displays in the
subtotal line items on the report. For example, if you enable the Total subtotal
to show grand totals on a report, you see the following result:
Standard Subtotal Example
94 Advanced Subtotals 2014 MicroStrategy Inc.

Notice how the Total subtotal applies to all three metrics on the report and the
subtotal name Total displays in the subtotal line item. If you want more
control over the characteristics of a subtotal, you can define custom subtotals.
Custom subtotals enable you to define custom subtotal line items that display
on your reports. You create custom subtotals at the report level, and you
primarily use them for display purposes. Custom subtotals enable you to do the
following:
Customize the subtotal name that displays in the subtotal line item
Specify different subtotal functions for different metrics on a report
Specify the level of each subtotal
Disable subtotaling for specific metrics on a report
You can make the subtotal name dynamic by typing special characters in the
subtotal name field. The following table lists wildcard characters you can use
when naming custom subtotals:
Custom Subtotal Wildcards
Character Description
#A The name of the attribute under which the subtotal

displays
#P The name of the attribute to the left of or above the attribute under which
the subtotal displays
#0 All the forms of the parent element
#1 The first form of the parent element reading from left to

right or from top to bottom
#2 The second form of the parent element reading from left

to right or from top to bottom
#3 The third form of the parent element reading from left to

#4 The fourth form of the parent element reading from left to

An attribute form provides details that identify and describe an

attribute. Examples of attribute forms include an ID, name, or
description.
2014 MicroStrategy Inc. Advanced Subtotals 95

Custom Subtotal Example
The following report displays two custom subtotals:

Custom Subtotal Example
The subtotals for the Movies and Music categories and the Northwest and
Southwest Regions use a special definition, Total for the #0 #P, for their
names. The custom subtotal is enabled across the Subcategory and Category
attribute levels. The Grand Total is enabled across the Region attribute level,
and it displays in the Total line item.
To create a custom subtotal:
1 In the Report Editor, on the Data menu, select Subtotals.
2 In the Subtotals window, on the Definition tab, click Advanced.
3 In the Advanced Subtotals Options window, click New.

4 In the Custom Subtotal Properties window, in the Provide a name for the
custom subtotal box, type a name for the subtotal. (In the above example,
the first custom subtotals name is Total for the #0 #P).
5 In the list, select the appropriate subtotals you would like to use for each
metric section, select the appropriate subtotal for each metric on the report.
6 If you want the subtotals of the metrics on the report to use one subtotal,
select the subtotal you want to apply to all listed metrics under Reset all
metric subtotals to, and click Reset.
7 Click OK.
8 In the Advanced Subtotals Options window, select the custom subtotal.
9 Under Applied levels, set the levels at which you want to display the custom
subtotal and click OK.
10 In the Subtotals window, on the Definition tab, in the Available subtotals

list, the check box for your new custom subtotal is selected.
The icon for a custom subtotal is different from the icon used for
standard subtotals.
11 In the Subtotals window, click OK.
User-Defined Subtotals
As you already know, MicroStrategy provides standard, predefined subtotals
that are available for use with any metric or report. These predefined subtotals
use simple aggregation functions, such as Count, Average, Max, and so forth, to
address the most common subtotaling requirements. If these predefined
subtotals do not satisfy your subtotaling needs, you can create user-defined
subtotals. With user-defined subtotals, you control the definition of the
subtotal. For example, you may need a subtotal that always calculates at the
Year level regardless of the level of the report.
You can create your own subtotal using any combination of the following:
An aggregation function, such as AVGDEV, IRR, MIRR, or NPV, that is not

one of the standard, predefined subtotal functions
Multiple functions
Constants in conjunction with aggregation functions

Nested functions
Dimensional subtotals
Other metrics
User-defined subtotals are different from custom subtotals. You create

user-defined subtotals as independent subtotal objects using the Subtotal
Editor and apply them to metrics. Custom subtotals enable you to define
custom subtotal line items at the report level, and you primarily use them for
display purposes.
User-Defined Subtotal Example
The following report includes a user-defined subtotal called Last Quarter that
shows the number of units received for each product category in the last
quarter of 2012:
Example - User-defined Subtotal

In this example, the user-defined subtotal uses the Last function. Its Sort By
parameters list the Category and Quarter attributes, sorted in ascending order
by ID. The subtotal is enabled at the Quarter level.
To create a user-defined subtotal:
1 In Developer, on the File menu, point to New and select Subtotal.
2 In the Subtotal Editor, in the Object Browser, drag the desired function for
the subtotal to the Definition pane.
3 In the Definition pane, select the entire function name.
4 Right-click the selected function and select <Function Name>

parameters.
5 In the <Function Name> Parameters window, set the necessary parameters

for the function.
6 Click OK.
7 In the Subtotal Editor, click Save and Close.
8 Open any metric to which you want to apply the new subtotal with the
Metric Editor.
9 In the Metric Editor, click the Subtotals\Aggregation tab.
10 On the Subtotals\Aggregation tab, in the Project subtotals list, select the

user-defined subtotal.
11 Click the > button to move the subtotal to the Available subtotals for metric
list.
13 Create or edit the report on which you want to display the user-defined
subtotal in the Report Editor.
14 In the Report Editor, on the Data menu, select Subtotals.
15 In the Subtotals window, on the Definition tab, select the check box for the
user-defined subtotal.

16 Click Advanced.
17 In the Advanced Subtotals Options window, set the levels at which you want
to display the user-defined subtotal.
18 Click OK.
19 In the Subtotals window, click OK.
20 In the Report Editor, click Save and Close.
For additional information on custom subtotals and user-defined

subtotals, see the Advanced Reporting Guide product manual.


Lesson Summary
Defining the level (or dimensionality) of a metric enables you to control the
attribute level at which the metric calculates.
You use level metrics to achieve compound metrics that calculate

percent-to-total values.
By default, all metrics calculate at the lowest attribute level of the report
template. This is called report level.
When you define a level metric, you specify a target, Grouping of Standard
or None, and Filtering of Standard, Absolute, Ignore, or None.
The Grouping options dictate how the level metric aggregates. (That is,
whether or not the metric aggregates at the attribute level specified as the
target.)
The Filtering options dictate how the reports filter interacts with the level
metric calculation.
Non-aggregatable metrics are defined to not aggregate across a particular

attribute, or an entire hierarchy.
Non-aggregatable metrics use the Beginning and Ending Grouping options.
Transformation metrics use transformation schema objects in their

definitions and enable you to perform time-based comparisons.
Transformation metrics can be defined using expression-based

transformations or table-based transformations.
Base formulas are simple metric formulas that are saved as separate objects
that you can reuse to create new metrics.
Conditional metrics are metrics that contain their own filters.
You can control how conditional metrics interact with report filters with the
Remove related report filter elements option.
A nested metric contains an inner formula that calculates at one attribute

level and an outer formula that calculates at a different attribute level.
2014 MicroStrategy Inc. Lesson Summary 101

MicroStrategy offers over 200 built-in functions that you can use to create
metrics.
When you create a count metric, you should carefully consider if the count
should take place against a lookup table or a fact table. You can specify this
in the Count parameters.
Rank, RunningSum, Round, Ntile, and date/time functions are some of the
built-in functions available in MicroStrategy.
You can create custom subtotals to control the display of subtotals on your
reports. You define custom subtotals at the report level.
User-defined subtotals enable you to control the functions used to calculate

subtotals. You create user-defined subtotals with the Subtotal Editor and
apply them to metrics as needed.

MicroStrategy Developer: Advanced Reporting Exercises: Advanced Metrics 2

Exercises: Advanced Metrics
For this and all subsequent exercises in this course, you should connect to the
MicroStrategy Analytics Modules project source that points to the latest
metadata and save your work to the My Reports folder under the My Personal
Objects folder. You may want to create additional subfolders for each of the
exercise sections throughout this course.
Given that this course targets more advanced users, the exercises
typically provide only high level steps. See your instructor for further
assistance.
Dynamic Sourcing is enabled by default in the MicroStrategy Tutorial

project, which may interfere with the results of the exercises. To ensure
the success of each of the exercises, disable Dynamic Sourcing.
To disable dynamic sourcing for the MicroStrategy Tutorial project:
1 In Developer, log into MicroStrategy Analytics Modules as the

administrator with blank password.
2 Right-click the MicroStrategy Tutorial project and select Project

Configuration.
3 Expand the Intelligent Cubes category.
4 In the right pane, under Dynamic Sourcing, clear the check box next to
Enable Dynamic Sourcing.
5 Click OK.
6 Restart the Intelligence Server for changes to take effect.
2014 MicroStrategy Inc. Exercises: Advanced Metrics 103

2 Exercises: Advanced Metrics MicroStrategy Developer: Advanced Reporting

Category
The goal of this report is to view the contribution of the following list of items
to their subcategory and category:
From the Action subcategory: Apollo 13, Lethal Weapon 4
From the Comedy subcategory: Ferris Buellers Day Off, The Wedding
Singer
From the Drama Subcategory: Titanic
However, you only want the items on this report to be included in the metric
calculations.
104 Exercises: Advanced Metrics 2014 MicroStrategy Inc.

Solution:
1 Create two level metrics:
Category Sales
Definition: Sum(Revenue)
For this metric, and all subsequent level metrics, be sure to use the
Revenue fact (not the Revenue metric) in the formula.
Level: Category
Filtering: Standard
Grouping: Standard
Subcategory Sales
Level: Subcategory
Filtering: Standard
Grouping: Standard
2 Create two percent contribution compound metrics:
Percent Contribution to Category
Definition: Revenue/Category Sales
For this percent contribution metric and all others in this set of
exercises, use the Revenue metric, not the Revenue fact, as the
numerator. Also, be sure to format the metric as a percentage with 2
decimal places.
Percent Contribution to Subcategory
Definition: Revenue/ Subcategory Sales
3 Create a report filter named Various Movies composed of the following

items:
Apollo 13, Lethal Weapon 4, Ferris Buellers Day Off, The Wedding
Singer, Titanic
4 Create a report with Category, Subcategory, Item, Revenue, the Percent

Contribution metrics, and the Various Movies report filter.

5 After viewing the results of your report, note what each metric is
calculating.
______________________________________
______________________________________
Level Metrics: All Item Sales

In this report, you want to continue analyzing the same list of items. However,
you now want to compare the sales of each item to the total sales of all the
items included on the report and the sales for all items across all categories.
Solution:
1 Create two level metrics:
All Selected Items Sales
Level: Item (lowest level in hierarchy)
Filtering: Standard
Grouping: None
All Items Sales
Level: Item
Filtering: Ignore

Grouping: None
2 Create two percent contribution compound metrics:
Percent Contribution to All Selected Items Sales
Definition: Revenue/All Selected Items Sales
Percent Contribution to All Item Sales
Definition: Revenue/All Item Sales
You will need to format the Percent Contribution metrics as a

percentage with 2 decimal places.
3 Create a report containing Category, Subcategory, Item, Revenue, Percent

Contribution to All Selected Items Sales, Percent Contribution to All Item
Sales, and the Various Movies report filter.
4 After viewing the results of your report, what if you want to analyze the
same items, but now you want to see how each item contributed to the total
sales of the three subcategories to which they belong. What will be the
Target, Filtering and Grouping of Subcategory Sales for Selected Items
metric?
______________________________________
______________________________________
Level Metrics: Metrics with Multiple Target Attributes

Create the report below. With this report, you are trying to determine how each
item in Quarter 1 of 2010 contributed to its entire subcategory for all time
periods, not just the one listed on the report (Quarter 1 in 2010).

Solution:
1 Create a Subcategory & All Time Sales level metric:
Level: Subcategory, Day
Filtering:
Subcategory: Absolute
Day: Ignore
Grouping:
Subcategory: Standard
Day: None
2 Create a Percent Contribution to Subcategory & All Time Sales

compound metric:
Definition: Revenue/Subcategory & All Time Sales
You will need to format this compound metric as a percent with two
decimal places.
3 Create a report containing Category, Subcategory, Item, Revenue,

Subcategory & All Time Sales, Percent Contribution to Subcategory & All
Time Sales, the Various Movies report filter, and an additional Q1 2010
report filter condition.

Non-aggregatable Metrics
The report below shows the ending inventory for the last month of the most
recent year captured in the data warehouse. When you have achieved this
report, verify that the last month is being displayed by drilling to Month.
Solution:
1 Create an Inventory EOH non-aggregatable metric:
Definition: Sum(End on Hand)
Level: Month
Filtering: Standard
Grouping: Ending(Fact)
You have instructed the Engine to treat this as a non-aggregatable

metric and to not sum the End on Hand inventory values across any
levels of Time. By specifying Ending(Fact) for the Grouping
setting, you have instructed the MicroStrategy Engine to look to the
fact table to identify the ending time period (rather than looking to
the lookup table).
2 Create a report with Item, Inventory EOH, and the Various Movies report
filter.
InItemtheandTutorial data warehouse, the End on Hand fact is stored at the

Month levels. Because the metric is non-aggregatable, it
will show the End on Hand for the last month of the most recent year
captured in the data warehouse, rather than the sum of End on Hand
values across the entire year.

3 To verify that the last month of the most recent year is the one shown on the
report and that the values for each month were not summed to get the year
inventory figure, select the first item on the report and drill across to
Month. The drilled result set displays all months and only the last month of
the year shows the same value as the year inventory in the original report.

Transformation Metrics
You want to compare this years revenue to last years revenue for various
items. Create the report shown below using a transformation metric:
Solution:
1 Create a Last Years Revenue transformation metric:
Transformation: Last Years
The Last Years transformation object already exists in the

MicroStrategy Tutorial project.
You will need to format this compound metric as Currency with zero
decimal places.
2 Create a report with Item, Revenue, Last Years Revenue, the Various
Movies report filter, and an additional Year 2012 report filter condition.

Conditional Metrics: Geographic Comparisons

The following report compares the Eastern US customer profit to the Western
US customer profit and calculates the profit variance between the two
geographical groups. You achieve this report with two conditional metrics and
a compound metric that is based on the conditional metrics.
Solution:
1 Create a profit metric with conditionality based on the Western United

States Customers filter. This filter is located in the Public
Objects/Filters/Customer Analysis Filters folder. Be sure to use the Profit
fact in the metrics formula. Name this metric West US Customer Profit.
2 Create a second profit metric with conditionality based on the Eastern

United States Customers filter. This filter is also located in the Public
Objects/Filters/Customer Analysis Filters folder. Again, use the Profit fact
in the metrics formula. Name this metric East US Customer Profit.
3 Create a third metric called Ratio of West to East, defined as West US

Customer Profit / East US Customer Profit. Format the values for this
metric as percentages (displaying 2 decimals as shown above).
4 Create a report with Year, the three metrics you just created, and an
Electronics category report filter condition.
This report demonstrates the flexibility and usefulness of conditional metrics.

The East US Customer Profit metric and the West US Customer Profit metric
each apply their own specific filtering conditions, enabling you to display
figures for different geographical groups on the same report.

Test Your Understanding:
1 Does the Electronics category report filter apply to the East US Customer
Profit and West US Customer Profit conditional metrics? Why or why not?
_______________________________________
_______________________________________
Remove Related Filter Elements
To observe the effects of the Remove related report filter elements setting, you
need to edit the definition of the report filter.
1 Open the report you created above with the Report Editor. Modify the
report filter by adding a second condition so that the report qualifies on two
specific customer states: California and New York. Remember to keep the
Electronics condition.
2 Re-execute the report and observe the results. Did the report return
different data?
3 Next, edit the East US Customer Profit metric with the Metric Editor. In the
Condition definition window, click Advanced.
4 In the Condition Advanced Options window, clear the Remove related

report filter elements check box and click OK.
5 Save and close the East US Customer Profit metric.
6 Re-execute the report and observe the results:

Did the East US Customer Profit values change? Why or why not?
_______________________________________
_______________________________________
7 Use the Save As option to save the report as a new report to the My
Reports folder.
Count Metrics
Overview
Create a report that returns the number of items that were sold by Region and
Category for the year 2012. The first few rows of your report should resemble
the following:
Solution:
1 Create a Count Item metric:
Definition: Count(Item)
For this metric, use the Item attribute (not the Item metric) in the
formula.

Count parameters:
Distinct = False
FactID = Item-level Units Sold
Null = False
UseLookupForAttributes = True
Tometric
access the count parameters, highlight the Count function in the
definition, right-click, and select Count parameters.
Alternately, you can access the parameters through the Insert
Function Wizard.
2 Create a report with Category, Region, Count Item, and a report filter for
the year 2012.
3 Run the report. Compare your results to the expected report in the
Overview section at the beginning of this exercise.
4 Now, change the FactID count parameter to Nothing. How does your report
change?

Use an Advanced FunctionRunning Revenue
Overview
Create a new metric using the OLAP function, RunningSum. This metric shows
the running total of Revenue by monthly sales. The report should look like the
following:

Solution:
1 Create a RunningSum of Revenue metric, defined as

RunningSum(Revenue).
Use the Revenue metric, not the Revenue fact.

2 Define the SortBy parameter of the RunningSum function so that it sorts by
Month.
3 Format the metric as Currency with 0 decimals.
4 Create a report with Year, Month, Revenue, and RunningSum of Revenue.
5 Run the report. Compare your results to the expected report in the
Overview section at the beginning of this exercise.
6 Save the report to the My Reports folder as Running Totals For Monthly
Sales.
Now, modify the RunningSum of Revenue metric, so the running totals

restart for each year. The report results should look like the following:

Solution:
1 Open the RunningSum of Revenue metric with the Metric Editor and for
the of the RunningSum function, add Year as the Break By attribute.
2 Re-execute the report to see the change in the metric behavior.

MicroStrategy Developer: Advanced Reporting Optional Advanced Exercises 2

Optional Advanced Exercises
The following exercises are optional and are meant to challenge your
knowledge of the concepts covered in this lesson. They do not include
step-by-step instructions. Instead, they outline a list of report requirements
and suggest features or functionality that you should include in the report to
satisfy the requirements.
Monthly Sales for Top 3 Customer Regions

As the MicroStrategy report developer for your company, you need to create a
report for the business analysts. They would like the report to display the sales
by year and month for purchases made by the top 3 customer regions. They
also want to see the percentage each month contributed to the total sales for all
years (all time) in the data warehouse.
Report Requirements:
Revenue by month
Percentage of revenue for each month to the total for all years (all time) in
the data warehouse
A metric qualification filter on Revenue to rank the top 3 customer regions.
You need to set the Output Level of the metric qualification to the
Customer Region attribute.
2014 MicroStrategy Inc. Optional Advanced Exercises 119

2 Optional Advanced Exercises MicroStrategy Developer: Advanced Reporting
The first few rows of your final report should look as follows:
Rank of Units Sold by Subcategory

You now need to create a report that ranks the number of units sold for the
Books, Movies, and Music categories. You want to restart the rank for each
Category. In other words, all subcategories under Books are ranked starting
with 1, and the rank starts again at 1 for the subcategories under Movies and
Music (see the image on the next page). You also need to qualify your result set,
so it returns only subcategories with Units Sold greater than 25,000.
Use the Rank function
You need to change the default sort order and set the Break By
parameter to achieve the appropriate results.
Advanced sorting
120 Optional Advanced Exercises 2014 MicroStrategy Inc.

Report filter of Units Sold > 25,000 and Category=Books, Movies, or Music
Your final report should look as follows:

2 Advanced Metrics Exercise Solutions MicroStrategy Developer: Advanced Reporting

Advanced Metrics Exercise Solutions

Category
Percent Contribution to Category is calculating what percentage the movies
specified in the report filter contributes to the total category sales.
Percent Contribution to Subcategory is calculating what percentage the

movies specified in the report filter contributes to the total subcategory
sales.
Level Metrics: All Item Sales

In step 4, to analyze the same items and see how each item contributed to
the total sales, set the Target to Subcategory, Filtering to Ignore, and
Grouping to Standard.
Conditional Metrics: Geographic Comparisons

In step 1, under Test Your Understanding, the Electronics category report
filter applies to the East US Customer Profit and West US Customer Profit
conditional metrics because the Profit metric counts the profit made from
all categories, including electronics.
In step 5, the East US Customer Profit value changes because the filter is
ignored when you remove the related filter elements.
Count Metrics
In step 4, when you change the FactID count parameter to nothing, the
Count Item metric displays a value of 90 for all regions
122 Advanced Metrics Exercise Solutions 2014 MicroStrategy Inc.

3
ADVANCED FILTERS
Lesson Description
This lesson builds on your knowledge of basic filters and introduces you to
several types of advanced filters that you can use to answer more complex
business questions. You will learn about attribute-to-attribute comparison
filters, joint element lists, pass-through functions in filters, metric-to-metric
comparison filters, relationship filters, and more. The exercises give you
hands-on practice in creating and employing advanced filters in reports.

3 Advanced Filters MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Create several types of advanced filters.
Describe attribute-to-attribute filtering and create attribute-to-attribute

comparison filters. (Page 125)
Understand and create joint element lists. (Page 127)
Use dynamic dates in filters. (Page 130)
Import and export elements of a filter. (Page 133)
Understand how pass-through functions can be used to take advantage of

the capabilities of the database server. (Page 135)
List and define the methods for metric qualification. Describe the concepts
of Output Level and Break By. Create metric comparison
filters. (Page 139)
Define the purpose of a relationship filter. Create relationship

filters. (Page 144)

MicroStrategy Developer: Advanced Reporting Advanced Filters 3
Attribute-to-Attribute Filtering

Describe attribute-to-attribute filtering and create attribute-to-attribute
comparison filters.
Attribute-to-attribute comparison filters enable you to create reports that

compare values of two attributes using their respective attribute forms. For
example, you can create a report that retrieves only those orders that were
shipped within 2 days of their order date by comparing Ship Date with Order
Date + 2. The two attributes being compared are Ship Date and Order Date,
as shown below:
Example: Attribute-to-Attribute Filtering
To create an attribute-to-attribute comparison filter:
1 In the Filter Editor, double-click in the Filter definition pane.
2014 MicroStrategy Inc. Attribute-to-Attribute Filtering 125

2 In Filtering Options, select Add an Attribute qualification and click OK.
3 Beside the Attribute box, click Browse.
4 Select an attribute and click OK.
5 In the Qualify On drop-down list, select the attribute form on which you
want to qualify.
6 In the Operator drop-down list, select the desired operator.
7 In the Value drop-down list, select Custom.
8 Drag the comparison attribute into the text box to the right of the Value
drop-down list.
The attribute is displayed with an @ symbol separating the

attribute name from the attribute form name (for example,
Date@ID).
9 To change the attribute form, simply replace the ID string with the name
of the desired form. Complete the expression as necessary (for example,
Date@ID + 2).
10 Click OK.
126 Attribute-to-Attribute Filtering 2014 MicroStrategy Inc.

Joint Element List

Understand and create joint element lists.
A joint element list enables you to choose combinations of attribute elements

from different attributes to filter a report. Unlike a simple attribute
qualification, the joint element list enables you to define a filter with attribute
element pairs of different attributes.
For example, if you choose the attributes Year and Region, you can create a
filter using a joint element list that filters on element pairs of those
attributes:
(Central, 2011) OR (Northeast, 2011) OR (Central, 2010)

OR (South, 2010)
Example - Joint Element List
A filter containing these pairs of attribute elements is different from a filter

defined as Year in (2010,2011) AND Region in (Northeast,
Central, South). If all of the attribute elements were simply placed in the
same filter, you would return additional, undesired attribute element pairs,
such as (2011 AND South) and (2010 AND Northeast).
A joint element list enables you to create filters with attribute pairs as well as
triplets, quadruplets, and so on.
2014 MicroStrategy Inc. Joint Element List 127

To create a joint element list:
2 In Filtering Options, select Add an Advanced qualification and click

OK.
3 In Advanced Qualification, in the Option drop-down list, select Joint

element list.
Filter Editor - Advanced Qualification
4 Under Joint element list, select the desired attributes from the list of
Available attributes and click > to move them to the list of Selected
attributes.
5 Under Element list, click Add to add element lists of the selected
attributes.
6 To modify element lists, click Modify.
128 Joint Element List 2014 MicroStrategy Inc.

7 In the Select Element List window, under Available Elements, select the
attribute whose element you want to modify from the drop-down list. Add
the desired element to the Selected Elements list.
8 Click OK.
9 To add another element list, repeat steps 5 to 8.
10 To delete an attribute element from the Element list, highlight the

element list and click Remove.
11 Click OK.
12 In the Filter Editor, click Save and Close.
2014 MicroStrategy Inc. Joint Element List 129

Dynamic Dates

Use dynamic dates in filters.
Dynamic dates are a fixed set of dates or ranges of dates that change over
time. These dates are fixed offsets of the current date according to the system
clock of the Intelligence Server machine.
Ifonyouthearesystem
working in a 2-tier environment, dynamic dates are based
clock of the client machine that runs Developer.
For example, a dynamic date enables you to run a report that always
examines sales from two months ago. This is represented as today minus 2
months. Dynamic date qualifications can be as specific as any of the following
examples:
Monday of this week
Today minus an offset of 2 months and 1 day
The 4th of this month
The 3rd Wednesday of last month
May 5th of next year
Creating Dynamic Dates

You can access the editor for dynamic dates through the Filter Editor. When
you qualify on an attribute form that has a date datatype, a calendar icon is
displayed next to the Value text box. Use the calendar button to open the
Date Editor.
130 Dynamic Dates 2014 MicroStrategy Inc.

The Date Editor offers the choice of a static date or various types of dynamic
dates:
Today
This Week
This Month
This Year
Each of these choices have several options for the starting point and enable
you to add or subtract days, weeks, months, or years.
At the bottom of the editor is a preview section that shows to which date a
dynamic date resolves, based on the current system clock.
To create a filter with dynamic dates:
3 In Attribute Qualification, click Browse.
4 In the Open window, select an attribute with a date datatype and click
OK.
5 In Attribute Qualification, in the Qualify On drop-down list, select ID.
6 In the Operator drop-down list, select the appropriate operators and click
Date Editor.
2014 MicroStrategy Inc. Dynamic Dates 131

7 In the Date and Time Editor window, select Dynamic date.

Example - Dynamic Date
8 Using the dynamic date options, specify the date criteria appropriate for
your report.
The Preview box displays the results of the dynamic date. Use this to
confirm that the dynamic date resolves correctly.
Order of Evaluation
When evaluating a dynamic date such as first of this month minus 7 days,
the order in which these two parts of the filter are evaluated is important. The
addition or subtraction of days, weeks, months, or years is always done first,
before first of this month, this week, this year, and so on is calculated.
For example:
If today is February 13th, then Today minus 7 days is February 6th, and
the first of the month of today minus 7 days is February 1st.
However, if today is February 2nd, then today minus 7 days is January

26th, and the first of the month of today minus 7 days is January 1st.
132 Dynamic Dates 2014 MicroStrategy Inc.

Import Filter Elements

Import and export elements of a filter.
You can import filter elements into the Filter Editor from sources other than
MicroStrategy. In the MicroStrategy Developer: Reporting Essentials course,
you learned that filters can be based on other existing filters and reports. The
import filter elements option adds even more flexibility to the Filter Editor by
enabling you to import lists of data from existing files into the filter
definition. Existing filter definitions can also be exported to an external file.
You can use a prompt to enable users to select the file from which to
import at run-time.
At times, importing elements into a filter may be a quicker alternative to

selecting each individual element. Imagine a report where you need to filter
on hundreds of customer last names. Rather than having to add each last
name one-by-one, you can import a list of last names from a file in one, quick
and easy step.
To import elements into a filter, the elements need to be stored in a Microsoft

Excel or text file. The following rules apply when importing elements from
source files:
1 Microsoft Excel: Data can be stored in rows, columns, or both.
If it has double quotes in the first and last position, it is imported as is

(with the quotes).
If it has single quotes in the first and last position, it is imported as is

(with the quotes).
If it does not fall under either of the first two bullets, it is checked to
determine if it is a number. If so, it is imported as is.
If it does not fall under either of the first two bullets, it is checked to
determine if it is a date. If so, it is imported by adding single quotes at
the beginning and end to comply with the date format.
If it does not fall in any of the previous cases, it is assumed to be a text

value and is imported by adding double quotes at the beginning and
end to comply with the text format.
2014 MicroStrategy Inc. Import Filter Elements 133

2 Text
Tab-delimited
List-delimited as specified in the regional settings
Return-delimited
To import filter elements:
3 For Attribute, add an attribute and click OK.
4 For Qualify On, select the attribute form on which you want to qualify.
5 For Operator, select either In List or Not In List.
6 Click Import.
7 In the Import Elements from File window, browse to the file to be

imported and click Open.
8 The elements that exist in the file are imported into the List box. Click
OK.
134 Import Filter Elements 2014 MicroStrategy Inc.

Pass-through Functions

Understand how pass-through functions can be used to take advantage of the
capabilities of the database server.
Pass-through functions are intended to provide access to the special
functions or syntactic constructs that are not standard in MicroStrategy but
are provided by various RDBMS platforms.
A specialized Apply function represents each type of custom

subexpression/function call. There are five predefined Apply functions used
to replace regular, predefined functions of the same type:
ApplySimpleUses simple arithmetic operators. This pass-through

function is used for defining metrics, filters, and attribute form
expressions.
ApplyAggUses aggregate functions (Sum, Max, and so forth). This

pass-through function is used primarily for defining metrics.
ApplyOLAPUses OLAP functions (Rank, RunningSum, and so forth).

This pass-through function is used primarily for defining metrics.
ApplyComparisonUses comparison operators (>=, <, Like, and so

forth). This function is used primarily for defining filters.
ApplyLogicUses logical operators (not, and, or, and so forth). This

function is used primarily for defining filters.
The purpose of all of the pass-through functions in Developer is to give you

the flexibility to bypass the MicroStrategy parser and send database-specific
syntax to the database server. They can be used in many different capacities,
including to create or modify both schema and application objects. That said,
remember that MicroStrategy also provides many functions and operators
that serve the same purposes as pass-through functions. In a reporting
scenario where you can choose between using a pass-through function or
MicroStrategy function to achieve the same result, you might want to test
both methods to see which one returns the result set more efficiently.
2014 MicroStrategy Inc. Pass-through Functions 135

The purpose of this topic is not to make you an expert on how to use
pass-through functions. Rather, this topic introduces you to the
concept and gives you some examples where pass-through functions
can be used to create filters. Please refer to the Analytical Functions
Reference manual for additional information.
Generally, the syntax for Apply functions is as follows:
ApplyFunctionName (Pattern, Arg, Arg,..Arg)
where:
ApplyFunctionNameGeneric name used for one of the predefined

pass-through functions (described above)
PatternString describing the actual expression (syntax) used by the

MicroStrategy Engine when generating the SQL to be sent to the RDBMS
ArgArgument that replaces the parameter markers in the pattern
For example:
ApplyComparison(ComparisonFunction (#0, #1),

attribute1@ID, attribute2@ID)
The placeholders in this example are represented by #0 and #1. The

placeholders reference the attributes when the expression is
processed. # is a reserved character in MicroStrategy, and n is the
number of the parameter outside the quotes, starting with 0 and
increasing in increments of 1.
Creating Custom Expressions With Apply Functions
To create a custom expression with Apply Functions:

OK.
3 For Option, keep Custom expression selected.
4 Under Custom expression, type the custom expression. For example:
ApplyComparison("#0=trunc(sysdate-1)",Day@ID)
136 Pass-through Functions 2014 MicroStrategy Inc.

OR
ApplySimple("Mid(#0,5,2)",Month@ID) = 12
The example above assumes that the date is stored in a

YYYYMMDD format.
Filter Examples
You can use pass-through functions to define several types of MicroStrategy

objects, including filters, metrics, and attribute forms. Here are some
examples of how pass-through functions can aid in the creation of specialized
filters.
For examples of pass-through functions in metrics and other objects,

refer to the MicroStrategy Functions Reference product manual.
Example 1: Dynamic Date
As discussed earlier in this lesson, MicroStrategy enables you to create

dynamic date filters based on the system clock. You can also accomplish this
by using ApplySimple and allowing your database server to handle the
calculation.
To create a filter for current month using ApplySimple and a Microsoft SQL
Server databaseTM:

OK.
3 For Option, keep Custom expression selected.
4 Under Custom expression, enter the following syntax for an attribute

qualification:
Month@ID = ApplySimple("datepart(year, getdate()) *

100 + datepart(month, getdate())",0)
The above expression uses ApplySimple to let Intelligence Server know that it
needs to pass the datepart function directly to the SQL Server database.
2014 MicroStrategy Inc. Pass-through Functions 137

ApplySimple (like all Apply functions) takes two parameters:

ApplySimple(x,y). X is the function passed to the database server. Y
is a MicroStrategy object that you want to pass to the function, x.
InExample
Example 1, MicroStrategy objects are not used, so y is set to zero. In
2, shown below, you will see an example of a database
function that uses a MicroStrategy object.
Example 2: Filtering on Null Values
Suppose you need to filter a result set to show only information associated
with null values. One method is to use the IsNull function, found in the
Functions and Operators folder. Another method is to use ApplyComparison
followed by the native database expression for checking for null values.
To create a filter that only returns rows with null values, using
ApplyComparison and a SQL Server database:

OK.
3 In the Option drop-down list, keep Custom expression selected.
4 Under Custom expression, enter the following syntax for an attribute

qualification:
ApplyComparison("#0 is NULL",Day@ID)
In this example, you see the Day attribute used in the functions formula. #0
is used as a place-holder for the Day attribute. That attribute displays as the
argument for the ApplyComparison function.
Insyntax
a function that uses multiple MicroStrategy objects, the general
for Apply functions is as follows:
ApplyFunc(...#0...#1...,FirstObject,SecondObject)
138 Pass-through Functions 2014 MicroStrategy Inc.

Set Qualification: Qualifying on Metrics

List and define the methods for metric qualification. Describe the concepts of
Output Level and Break By. Create metric comparison filters.
Metric qualifiers restrict the rows of data returned in a report by constraining

the metrics. You can qualify on a particular function for the selected metric.
The functions include:
Metric valueThe value on which to qualify the metric. For example,

metric value greater than or equal to 100 returns rows for that metric that
are 100 or higher.
RankThe numeric rank of values. For example, rank top 40 returns the
40 highest values for the selected metric.
PercentPercentage of the values being ranked. For example, percent top

10 returns all values in the top 10% for the selected metric.
When you use a metric qualification filter in a report, you can choose to
qualify on any metric, not just the metrics on the report template.
Output Level
Metric qualifiers restrict the amount of data returned on a report by applying
some type of condition to a metric. The output level of the metric qualifier is
the set of attributes at which you want the metric to evaluate.
An example of a metric qualification with an output level of Month follows:
Revenue > 1000, Output Level: Month
The metric qualification limits the result set to the months that have sales
greater than $1,000. In other words, your filter qualification identifies the set
of months that have more than $1,000 in sales.
If an attribute other than Month, for example Day, is on the report, it shows
only those days in the months with $1,000 or more in sales. Note that this is
not the same as days that have $1,000 or more in sales.
2014 MicroStrategy Inc. Set Qualification: Qualifying on Metrics 139

When you define output level in a metric qualification filter, you can choose
from the following options:
Set Output Level
None selectedThis is the default setting. If this option is selected, the

results are calculated at the report level, as long as the metric is a
compound metric or its dimensionality is set to report level or nothing.
Otherwise, the metric's dimensionality is used.
Calculate the output at the Metric levelMetric level means that the
output level is defined by the level, or dimensionality, of the metric itself,
regardless of the level of the report. The qualification follows the metrics
level of calculation.
Calculate the output at the Report levelThis setting looks to the

lowest level of the attributes that appear on the report template. Looking
back to one of the previous examples, if Year and Day are on a report, the
filter restricts the data to the set of days (because Day is at a lower level
than Year) with revenue greater than $1,000. As another example, if Item
and Day are on the report, the result set returns the items with revenue
greater than $1,000 on a given day (the intersection of the two unrelated
attributesItem and Day).
140 Set Qualification: Qualifying on Metrics 2014 MicroStrategy Inc.

Incalculates
a case where you qualify on a simple metric, like Revenue, which
at the report level by default, the default output level
behaves the same as the report output level. In both cases, the
metric evaluates to the lowest attribute levels on the report
template.
Calculate the output for the list of attributesWith this setting, you
can select the attribute (or attributes) at which the qualification should
evaluate.
Break By
Break By enables you to choose the attribute level at which to restart the rank
or percentage for a metric. This level must be greater than or equal to the
level of aggregation of the metric.
A metric qualification filter that ranks the top 2 items by Units Sold returns
only those two items, as shown below:
Without Break By Example
If you modify the metric qualification filter to include a break by of Category,

you retrieve an entirely different result set:
Break By on Category Attribute Example

Notice how the break by Category forces the rank to restart for every
Category. Your result set returns the top 2 items by Units Sold for each
Category in the data warehouse, not just the top 2 items across all Categories.
Metric-to-Metric Comparison
Metric-to-metric comparison enables you to create reports that compare the
values of two metrics. For example, you can create a report that returns only
those records with Revenue greater than Last Year Revenue.
To create a metric-to-metric qualification filter:
2 In Filtering Options, select Add a Set qualification and click OK.
3 Under Choose a metric, beside the Metric box, click Browse.
4 Select a metric, and click OK.
5 Under Parameters, in the Function drop-down list, select Metric Value.
6 In the Operator drop-down list, select the desired operator.
7 In the Value drop-down list, select Metric.
8 Drag the comparison metric into the text box next to the Value drop-down
list.
You add only one metric on which to base the comparison. However, it
is possible to create an expression-based metric comparison. For
example:
Units Sold>(Last Year Units Sold+EOH Inventory)*1.1
For the above comparison, the procedure is the same, except that you
need to select Custom from the Value drop-down box. You can then
drag the desired metric and enter the rest of the expression.
142 Set Qualification: Qualifying on Metrics 2014 MicroStrategy Inc.

For a metric-to-metric comparison filter that qualifies on Revenue greater

than Last Years Revenue, the filter is defined as follows:
Example - Metric-to-Metric Comparison

Set Qualification: Relationship Filters

Define the purpose of a relationship filter. Create relationship filters.
To understand the concept of a relationship filter, you first need to recognize

the difference between directly and indirectly related attributes.
Direct and Indirect Attribute Relationships

A direct relationship between two attributes exists when you explicitly define
a parent-child relationship (with Architect) between two or more attributes.
In these cases, the relationship is defined through the attributes lookup
tables or through distinct relationship tables.
For example, the Country and City attributes have a one-to-many

relationship and relate to each other through their combination
lookup/relationship tables. As another example, the Item and Color
attributes have a many-to-many relationship and relate to each other through
a distinct relationship table.
An indirect relationship between two attributes occurs when a parent-child

relationship does not explicitly exist between them. There is no relationship
established in the lookup tables of these attributes, nor is there a distinct
relate table to establish a relationship. The only relationship between the two
attributes exists through a fact in one or more fact tables.
For example, the Customer and Date attributes have no relationship to one
another unless a customer purchases something on a certain date. Thus, the
Customer and Date attributes are only related through the Revenue fact, in
whichever fact tables the Revenue fact is stored.
The implications of whether attributes are directly or indirectly related

become clear when you begin building reports. You can successfully run a
report with two attributes that are directly related (for example, Country and
City). However, a report with two indirectly related attributes must include a
metric (which is based on a fact) that relates the two attributes. Without this
metric, the report returns a nonsensical result set (or in SQL terms, a
Cartesian product). To get beyond this metric requirement for relating
indirectly related attributes, you can use a relationship filter.
144 Set Qualification: Relationship Filters 2014 MicroStrategy Inc.

Relationship filters enable you to define a relationship between two

otherwise indirectly related attributes without having to include a metric on
the report.
One of the most common uses for relationship filters is to achieve product
affinity analysis, or market basket analysis. Affinity analysis is popular
among companies that need to identify the relationship between two
products. If one product is sold in conjunction with another product in the
same basket (or order/transaction) and if this situation often occurs, then
both products hold a high affinity. Contrarily, when two products are rarely
sold together, they have a low affinity.
While an in-depth discussion on market basket analysis is beyond the

scope of this course, you can learn more about it in the MicroStrategy
Data Mining and Advanced Analytics course or by researching
technical notes in the MicroStrategy Knowledge Base.
Relationship Filter
As mentioned earlier, a relationship filter is a filter that can be used to define
a specific type of relationship between two or more attributes that are
otherwise not directly related.
From a technical perspective, a relationship filter is nothing more than

a nested SQL subquery in the WHERE clause of a SQL pass. The
MicroStrategy Engine Essentials course teaches you about the SQL
behind relationship filters in detail.
2014 MicroStrategy Inc. Set Qualification: Relationship Filters 145

The image below shows the Filter Editor when you create a relationship filter.
Notice that the Set Qualification Type is Relationship and that there are
additional options for specifying the Output Level, a Filter Qualification, and
a Relate By option.
Set Qualification - Relationship Filter
There are three parts to the definition of a relationship filter:
Output level is a list of the attributes on which to filter. It dictates the

contents of the relationship filters output set. In SQL terms, it is
responsible for the GROUP BY clause.
The filter qualification defines filtering criteria to appear in the

WHERE clause of the nested subquery.
Itrelationship
is also possible to nest relationship filters. In other words, a
filter can be used as the filter qualification in another
relationship filter

The relation (or Relate By) is defined through a fact, a table, or the
system default. When you select the Use System Default option, the
MicroStrategy Engine picks a table based on the project schema.
However, most often, you should select a specific fact or table that you
know establishes the relationship between the output level(s) and the
filter qualification. From a SQL perspective, the fact or table you define as
the relation impacts the FROM clause of the SQL query that
corresponds to the relationship filter.
Defining the Relation Using Relationship Tables
A list of catalogs containing items A and B is an example of a relationship

filter that uses a relationship table to relate the attributes. There is a
many-to-many relationship between the Catalog and Item attributes because
any one catalog can contain many items, and one item can appear in many
catalogs. This relationship is defined through a relationship table in the data
warehouse.
Defining the Relation Using Fact Tables
A list of customers who bought items A and B is an example of a relationship

filter that uses a fact or a fact table to relate the attributes. The Customer
attribute is related to the Item attribute through the Revenue fact, which is
stored in one or more base or aggregate fact tables in the data warehouse.
When you specify a particular fact in the Relate by field of a relationship
filter, the MicroStrategy Engine chooses the best table containing data for the
fact, the output level attribute, and the filter qualification attribute.
For example, your data warehouse might have fact tables that contain the
Customer (output level) and Item (filter qualification) attributes and the
Revenue fact at the Year, Month, and Day levels. When you choose the
Revenue fact for your Relate by option, the MicroStrategy Engine
automatically chooses the fact table that contains Revenue data and is best
suited given the attributes on the report template.
On the other hand, if you choose a specific fact table for your Relate by
option, you force the MicroStrategy Engine to use that particular fact table to
establish the relationship between your output level and your filter
qualification.

Relationship Filter Editor

As mentioned earlier, the Relationship Filter Editor has sections for each of
the three parts of a relationship filter definition: output level, filter
qualification, and relation.
To create a relationship set qualification:
2 In Filtering Options, select Add a Set qualification and click OK.
3 Under Set Qualification Type, in the Type drop-down list, select

Relationship, as shown below:
Set Qualification - Relationship Filter
4 Under Output Level, click Browse.
5 In the Level window, add attributes from the Available objects window to
the Selected objects window and click OK.

6 Under Filter Qualification, click Browse to select a filter on which you

want to qualify.
You can also create a new filter qualification by clicking the Create
button.
7 In the Open window, browse through the folders to select a filter and click
OK.
8 In the Relate By drop-down list, select the table or fact to relate the output
level and filter qualification attributes. As mentioned earlier, if you select
the system default, the MicroStrategy Engine uses the project schema to
determine how to relate the filter qualification and output level. Most
often, you want to select a table or a fact by which to relate the filter
qualification and the output level. Click OK.
9 Click the Advanced button. Depending on the results you want to see on
your final report, select or clear the Also apply this qualification
independently of the relationship filter option.
When cleared, this advanced option applies the filter qualification

only to the relationship filter itself and not to the entire report. In
other words, the filter is included only in the SQL statement that
resolves the relationship filter (subquery) and not the outer query
that resolves the report as a whole. For more information on this
setting, see the following section.

Advanced Option - Also apply this qualification

independently of the relationship filter
The advanced option in the Filter Editor can affect the final report result for a
report that has one or more relationship filters. For example, the following
report that displays the customers who purchased two particular books: To
Kill a Mockingbird and The Old Man and the Sea. If the advanced option is
cleared for each of the relationship filters that correspond to each book, you
retrieve the following result set:
Relationship Filter - Advanced Option Cleared
Notice how the amount of revenue associated with each customer depicts
their overall revenue, not just for the two books that they purchased. In this
case, by clearing the advanced option in both relationship filters, you have
indicated that you do not want the filtering qualifications in the relationship
filters to be applied to the final report result.

If you edit the definition of one of the relationship filters (in the below case,
the To Kill a Mockingbird relationship filter) and select the advanced option,
you retrieve the following result set:
Relationship Filter - Advanced Option Selected for To Kill a Mockingbird
Notice that the report returns much smaller revenue numbers. The revenue
figures represent the sales for each customer for just that one book, To Kill a
Mockingbird. In this case, the filtering qualification in the To Kill a
Mockingbird relationship filter is also applied to the final report result.
Relationship Filters vs. Shortcut-to-a-report Qualification

In many cases, you can use the shortcut-to-a-report qualification option (also
referred to as report as filter) in the Filter Editor to achieve the same type of
analysis as you can with relationship filters. Conceptually and technically,
shortcut-to-a-report and relationship filters are very similar. The following
are guidelines as to when to use one approach over the other:
1 If the relationship between two attributes is captured by:
A fact. Then you can generally use either type of filter, as in the
example of items related to each other through the Revenue fact and
the customer who purchased those items.

A table that has no corresponding fact defined on top of it. A

relationship filter is necessary, and the relation needs to be the table
rather than a fact, as in the example with catalogs and items that are
only related to each other through a relationship table.
2 If your primary goal is to modify the output level of a filter, it is generally

more straightforward to use a relationship filter.
3 Default VLDB settings specify that relationship filters are applied as a

subquery. You can change these settings via the VLDB settings under
Query Optimization/Subquery Type. Shortcut-to-a-report qualifications
are always applied as intermediate tables.


Lesson Summary
In this lesson, you learned about the following:
Attribute-to-attribute comparison filters enable you to create reports that

compare values of two attributes using their respective attribute forms.
Joint element lists are combinations of attribute elements of different

attributes that filter reports.
A dynamic date filter is a fixed set of dates or ranges of dates that change
over time, based on the system clock of the Intelligence Server machine.
You can import filter elements into the Filter Editor from external
sources, like Microsoft Excel and text files.
Pass-through functions enable you to use functions or syntactic

constructs that are not standard in MicroStrategy but are available in
various RDBMS platforms. The five Apply statements enable you take
advantage of your databases native functionality.
Set qualification filters include metric qualification and relationship

filters.
Metric qualification filters enable you to constrain a metric based on its

value, rank, or percent.
When you define any type of set qualification filter (metric qualification
or relationship filter), you need to define the Output Level. The Output
Level determines the attribute level at which the set qualification applies.
Break By enables you to choose the attribute level at which to restart the
rank or percentage for a metric.
A metric-to-metric comparison filter compares the values of two metrics.
Relationship filters enable you to define a relationship between two or

more attributes that are otherwise not directly related.
Relationship filters contain a filter qualification, an output level, and a

relation.
You can often use the shortcut-to-a-report qualification (also known as

report-as-filter) to achieve the same results as relationship filters.


MicroStrategy Developer: Advanced Reporting Exercises: Advanced Filters 3

Exercises: Advanced Filters
Attribute-to-attribute Filtering
You want to determine the customers who have benefited from improved
efficiencies in your shipment process and their current profitability. Since
improvements began in October 2012 for the Books category, you can start by
analyzing book orders in November 2012. The goal is to ship out at least 50%
of all orders for books within 4 days of their order dates. Design the following
report:
Solution:
1 Create a report that contains the attributes and metrics shown above.
2 Define the local report filter as Month = November 2012 and Category
= Books and (Ship Date_ID < [Day_ID + 4]).
The Month and category filter are simple filters.
2014 MicroStrategy Inc. Exercises: Advanced Filters 155

3 Exercises: Advanced Filters MicroStrategy Developer: Advanced Reporting
To create the attribute-to-attribute comparison, begin the attribute

qualification by selecting Ship Date as the attribute on which to
qualify.
Qualify on ID.
Select the Less than operator.
Select Custom.
Drag and drop the Day Attribute into the text box to the right of
Custom. Type + 4 after Day@ID. The filter definition should look like
the following:
Your final report result may have more information, depending on the
attribute forms selected for display.
156 Exercises: Advanced Filters 2014 MicroStrategy Inc.

Joint Element List

Suppose you want a report showing how certain categories perform in select
months. You have Category, Month of Year, and Revenue on the report. You
want to restrict the data to Music in September, Movies in June, and Books in
January.
1 Create this report with a single, simple filter with January, June, and
September AND Books, Movies, and Music. Explain why this does not
yield the result you want.
_______________________________________
_______________________________________
2 Now, redesign the filter using a joint element list.
3 Explain why this is the correct approach for your report requirement:
_______________________________________
_______________________________________
Solution:
1 Create a new report named Special Sales with a template exactly like the
one shown at the beginning of this exercise.
2 Create a filter with a definition of ([Books, January],[Movies,

June],[Music, September]). Begin the advanced qualification by
selecting the Joint element list option.
Move the Category and Month of Year attributes to the Selected

Attributes window.

Click the Add button (cube icon) to the right of the element list
window three times. An attribute element from both attributes
displays in the Element list window three times.
Select the elements and click the Modify button, which is below the
Add button. Change the element pairs to Movies and June and Music
and September.
Import Filter Elements

You have received a text file of names (Last Names) for people who have
asked to be part of a weekly promotion. Before adding them as part of the
promotion, you want to see who has already purchased items from your
store.
1 Open Notepad and enter the following names separated by commas:
ToNotepad.
open Notepad, go to Start > All Programs > Accessories >
Artemis, Cao, Churchill, Kert, Johanna
2 Name the file Promotions.txt and save it to the c:/Program Files

(x86)/Common Files/MicroStrategy folder.
3 Create the following report by importing the filter elements from the
Promotions.txt file you just created.

Solution:
1 Create a filter called Import Customers.
Begin the attribute qualification by selecting Customer as the

attribute on which to qualify.
Qualify on Last Name.
Select the In List operator.
Click Import.
Browse to the text file created earlier in this exercise. Select it and click
Open. Click OK.
2 Use this filter on a report with Customer and Revenue on the template.
Dynamic Dates
Dynamic Dates: Month-to-date Sales
Create a report that gives the revenue of each Subcategory for the time period
between the first of this month two years ago and the current date minus two
years.

This exercise requires you to subtract two years for each dynamic date
qualification because the Tutorial data warehouse only contains data
for the years 2010, 2011,2012, and 2013. Your results for this exercise
will vary from the example report shown below since you will qualify
on a different current system date. The report below was executed
with a date of November 25, 2013 as the current system date.
Solution:
1 Create a filter called Dynamic Date with a definition of Day Between

First of this Month minus two years and today minus two years.
Begin the attribute qualification by selecting Day as the attribute on
which to qualify.
Qualify on ID.
Select the Between operator.
Select Value in both drop-down boxes below the operator box.

Click the Date Editor button to the right of the beginning date box.
Select the following options: Dynamic Date, Today minus 24
months, Choose date adjustments based on the view point check
box, Monthly, and Day 1. Check that the Preview box lists the current
days date and the dynamic date is resolved to the first day of the
current month two years ago. Click OK.
Click the Date Editor button to the right of the ending date box. Select
the following options: Dynamic Date and Today minus 24 months.
Check that the Preview box lists the current date and the dynamic date
resolves to todays date two years ago. Click OK.
2 Create a new report named Dynamic Month to Date Sales with a

template exactly like the one shown at the beginning of this exercise. Add
the filter created in step 1.
ApplyComparison: Compare Attribute Elements
Most of your customers are not based in the same area as the call centers
from which they purchase their goods. You have recently begun a new
initiative to advertise locally to try to increase the customer base in the towns
where your call centers are located. Create a report that shows the number of
customers who reside in the same city as the call center from which they
purchase goods and those who reside in a different city from the call center
from which they purchase goods.

Solution:
1 Use the ApplyComparison function in a filter with the following syntax:
ApplyComparison ("#0 = #1", [Customer City]@DESC,

[Call Center]@DESC)
2 Use the ApplyComparison function in another filter with the following

syntax:
ApplyComparison ("#0 <>#1", [Customer City]@DESC,

[Call Center]@DESC)
3 Create a metric to count Customers and apply the filter created in step 1.
You need to set certain parameters of the count metric to return

the desired results. First, make sure that the count function only
counts distinct customers. Also, since you need to count the
number of customers that actually generated revenue, you need to
specify Revenue as the FactID.
4 Create a second metric to count the customers and apply the filter created
in step 2.
Define this second metric with the same count parameters

described in the note above.
5 Create a report with Customer Region and the two count metrics and save
it as Local Customer Campaign.
6 Based on the report results, would you conclude that the advertising
initiative is working?
_______________________________________
_______________________________________

Set QualificationOutput Level

You want a report showing all subcategory sales for the companys top 2
categories. The top two categories are the two with the highest Revenue.
1 Create a report that determines the top two sales without adjusting the
Output Level. Why doesnt this report result satisfy your requirements?
_______________________________________
_______________________________________
2 What should the Output Level be?

_______________________________________
_______________________________________
Solution:
1 Create a new report named Top 2 Categories with a template exactly like
the one shown at the beginning of this exercise.
2 Create a filter with a definition of Rank: Top 2 Categories by Revenue.

Begin the set qualification by selecting a Set Qualification type of Metric.
Select Category as the Output Level.
Select the Revenue metric.

Set the following parameters: Function = Rank, Operator = Top.
Select Value.
Type 2 in the text box next to the Value drop-down list.
Set QualificationBreak by
You want to reward your top sales representatives in each Region. To do this,
you need a list of the top sales representative per Region, not the top sales
representatives in the company overall.
Solution:
1 Create a filter with a definition of Rank: Top Employee per Region.

Begin the set qualification by selecting a Set Qualification type of Metric.
Leave the Output Level at the default setting of None Selected.
Select the Revenue metric.
Set the following parameters: Function = Rank, Operator = Top.
Select Value.
In the text box to the right of the Value box, Type 1.
Click Break By and select Region as the attribute. Click OK.
2 Create a new report named Top Employee per Region with a template
exactly like the one shown at the beginning of this exercise. Add the filter
created in step 1.

Relationship Filter
You want to create a report to retrieve a list of customers who bought both
The Old Man and the Sea and To Kill a Mockingbird. You also want to see the
overall revenue for each of these customers.
Solution:
1 Create a relationship filter, called REL - The Old Man and the Sea,
defined as follows:
Output Level: Customer
Filter Qualification: Item=The Old Man and the Sea
You can create a new filter for the filter qualification directly within
the Set Qualification window. Simply click the Create button that
displays in the Filter Qualification section of the window and
follow the steps to define an attribute qualification filter for The
Old Man and the Sea item.
Relate By: Revenue fact
Advanced button: Clear the Apply this qualification independently

of the relationship filter option.

2 Create a second relationship filter, called REL - To Kill a Mockingbird,

defined as follows:
Output Level: Customer
Filter Qualification: Item=To Kill a Mockingbird
Again, you can create the filter for this item directly within the
Filter Qualification section of the Set Qualification window.
Relate By: Revenue fact
Advanced button: Clear the Apply this qualification independently

of the relationship filter option.
3 Create a new report with a template exactly like the one shown at the
beginning of this exercise.
4 Drag and drop both relationship filters into the filter definition pane.
Leave the default set operator of AND.
5 Save the report to the My Reports folder as List of Customers Who

Purchased Specific Classics.


knowledge of the concepts covered in this lesson. They do not provide any
Demographic Analysis
The business analysts at your company want to study the sales patterns of
certain demographic groups but only for those groups who live in five, large
urban cities and only for 2 months of the year. You need to create a report
that displays a combination of customer ages with particular income ranges
and the profit that corresponds to each group. Only show the profit for these
groups in September and October and for those who live in the following five
cities: New York, Boston, Los Angeles, San Francisco, and Chicago.
Use a joint element list that groups Customer Age and Income Bracket
in the following combinations:
21:<$20K, 26:$40-50K, 36:$50-60K, 46:$60-70K, 56:$70-80K
Include an attribute qualification on Month of Year for September and

October and on Customer City for New York, Boston, Los Angeles,
San Francisco, and Chicago.

Your final report result should look like the following:
September and October are attribute elements of Month of Year that

are listed in the columns above the Profit metric. They are not part of
the Profit metric name nor its definition.
Your report results may vary from the example above due to the fact
that the Customer Age attribute is a derived attribute that is calculated
based on the current system date of your computer.
Unit Sales for Bottom 5% of Items

You need to find out what the slowest selling items are in terms of Units Sold.
Create a report to display the bottom 5% of items for each Category. Use
advanced sorting to sort the report by Units Sold in descending order and
then by Subcategory. Use the page-by option to view the report results, one
Category at a time.
Use set qualification on the Units Sold metric to return the bottom 5% of
Items within each Category (this requires manipulating the Break By)
Advanced sorting
Page-by

When you choose to view the Books category, your final report results
should look like the following:
Market Basket Analysis

You want to see the items that sold most often with a particular item that you
select at report run-time. Use a relationship filter or a shortcut-to-a-report
qualification to identify the orders in which your selected item was sold.
Apply the relationship filter or the shortcut-to-a-report qualification as the
filter in your final report, which shows the Category, Item, Revenue, and
Units Sold for all items that sold in the same orders with your selected item.
Sort the report by Units Sold in descending order to view which items sold
most often with your selected item.
Create a relationship filter that returns the orders in which a selected item
was sold. (This should be a prompted attribute qualification filter on
Item.)
Use the relationship filter for the final report, which contains Category,
Item, Revenue, and Units Sold.
Sort by Units Sold in descending order.

If you select The Beatles Anthology item at report run-time, your final
report results should resemble the following result set:

MicroStrategy Developer: Advanced Reporting Advanced Filter Exercise Solutions 3

Advanced Filter Exercise Solutions
Joint Element List

In step 1, when you initially create a simple, single filter, the report data
set returns the incorrect months and categories. For example, it returns
Movies and Music in January, but you only want Books in January.
In step 3, when you create a joint element list you create requirements
that limit the data output solely to the data you want to see returned on
the report.
Pass Through Functions

In step 6, based on the report results, you can conclude that the
advertising initiative is not working.
Set Qualification - Output Level

In step 1, without adjusting the Output Level, the results do not satisfy
your requirements because the results reflect the top 2 of all revenue
generating items at the Subcategory level.
In step 2, the output level should be Category.
2014 MicroStrategy Inc. Advanced Filter Exercise Solutions 171

3 Advanced Filter Exercise Solutions MicroStrategy Developer: Advanced Reporting
172 Advanced Filter Exercise Solutions 2014 MicroStrategy Inc.

4
CONSOLIDATIONS AND
CUSTOM GROUPS
Lesson Description
Consolidations and custom groups are two special reporting features that
enable you to surpass basic reporting functionality. With consolidations, you
can group attribute elements to define virtual attributes that enable you to
analyze data at levels that are not inherently available in the business model.
With custom groups, you can create reports that qualify on a row-by-row basis,
greatly enhancing the flexibility of report design and the capabilities of
report-level qualifications.
In this lesson, you will learn the significance of consolidations and custom
groups as well as how to create them. This lesson builds on your knowledge of
basic filter concepts and your understanding of attributes and attribute
elements.

4 Consolidations and Custom Groups MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Create consolidations and custom groups and determine the appropriate use
of both objects.
Explain the significance of consolidations and use the Consolidation

Editor to create them. (Page 175)
Explain the significance of custom groups and use the Custom Group
Editor to create them. (Page 182)

MicroStrategy Developer: Advanced Reporting Consolidations and Custom Groups 4
Consolidations

Explain the significance of consolidations and use the Consolidation Editor to
create them.
Business Scenario: Seasonal Analysis

Suppose you are the Marketing Director of the Central region. It is important
for you to understand how products are performing on a seasonal basis to
gain insight into consumer buying patterns and provide guidance on
establishing pricing strategies and promotions. With this objective in mind,
you need to see the seasons in the rows of your report and various metrics,
such as Revenue and Units Sold, in the columns.
If seasons (winter, spring, summer, fall) was an attribute in your project

data model, then this report would not be very difficult to create. All you
would need to do is place the Seasons attribute on the template along with
the desired metrics. However, Seasons is not an attribute in your project
schema.
Time Hierarchy
2014 MicroStrategy Inc. Consolidations 175

How can you satisfy this report requirement?

Seasonal Revenue and Units Sold
What Is a Consolidation?
Consolidations enable you to group attribute elements together and place
these groupings on a template, just like an attribute. The elements of the
consolidation appear as rows in your report results.
For example, for the seasonal analysis business scenario, you want to see
each season as a separate row on a report, but Season does not exist as an
attribute in your project. A consolidation enables you to group together the
attribute elements of the Month of Year attribute to define the various
seasons, which you can then place on a template. This consolidation contains
four consolidation elements, one for each season.
You define the Summer consolidation element as June + July + August,

the Fall consolidation element as September + October + November,
and so forth. You place the Seasons consolidation in the rows of your
template with any desired metrics in the columns. When you run the report,
the metric values for June, July, and August are added together to yield the
value for Summer. This occurs for each season.
176 Consolidations 2014 MicroStrategy Inc.

Consolidations provide two powerful functions that can help satisfy unique
reporting needs. These two functions are:
Serving as virtual attributes
Performing row-level math
Consolidations as Virtual Attributes
In the Seasons consolidation example, the four consolidation elements are

defined by adding together the respective Month of Year attribute elements
that belong to the different seasons. The fact that you can add attribute
elements together means that you can aggregate report data to a level other
than one of the predefined attributes in a project. The consolidation
essentially produces the same result as having an actual Seasons attribute in
your project schema without you having to define an attribute in your
business model or project.
Of course, you could also add a Seasons attribute to your Time hierarchy.
However, it is generally best to have the project architect add an attribute.
This person knows how to define the attribute properly and associate it with
the proper tables in the data warehouse. Sometimes, modifying the data
model is not always an option either. The benefit of consolidations is that
they enable you to avoid changing the data model.
Performing Row-level Math
As mentioned above, you build the Seasons consolidation by adding attribute

elements of Month of Year together to define the different seasons. However,
you are not limited to just adding. In fact, you can perform any simple
arithmetic operation when defining a consolidation. Consolidations enable
you to perform mathematical operations between elements or element
groups. That is, you can do other arithmetic operations such as
multiplication, division, and subtraction. You can even use constants in the
mathematical expressions.
This feature, known as row-level math, makes consolidations an even more

powerful tool.

You can assign heterogeneous formatting to individual consolidation

elements. In the sample below, notice that in the same consolidation,
there are elements formatted as dollars ($) and percentages (%).
Consolidation Example: Seasonal Analysis
In the above report, every row of data is a mathematical operation of its own.
The first four rows perform simple addition of the Month of Year attribute
elements. The final rows divide two consolidation elements. The Seasons
consolidation includes row-level math to make this report possible.
Consolidation Elements
Just as each attribute is made up of one or more attribute elements, each
consolidation is made up of one or more consolidation elements. One or
more attribute elements are often combined with mathematical operators to
define a consolidation element. Consolidation elements appear as rows in
your report results. Consolidation elements can contain any of the following:
Attribute elements of the same attribute (such as two cities)
Attribute elements of different-level attributes (such as Region and

Country)
Attribute elements of unrelated attributes (such as Country and Year)
Existing consolidation elements (such as the ratio of Fall and Spring sales
or Winter and Summer sales)
Existing consolidation elements from any existing consolidation in the

project. Consolidation elements from any consolidation can be imported
into another consolidation.

Consolidation Editor
Creating a consolidation involves using the Consolidation Editor to define the
various consolidation elements:
Consolidation Editor
The Consolidation Editor enables you to select attribute elements from any of
the hierarchies found in the Data Explorer.
Creating a Consolidation with Attribute Elements
To create a consolidation with attribute elements:
1 In the Consolidation Editor, under Elements for this consolidation, click

Click here to add new consolidation element.
2 Type a name for your consolidation element.
3 In the Object Browser, locate the attribute element that you want to
include in your consolidation element and drag it into the expression
window.

4 If your consolidation element consists of more than one attribute

element, select the desired mathematical operator in the expression
window and repeat the above step.
5 Repeat steps 3 to 4 until the consolidation element contains all the

necessary attribute elements.
6 Repeat steps 2 to 5 to add multiple consolidation elements.
Creating a Consolidation With Other Consolidation Elements
To include consolidation elements in a consolidation:
1 In the Consolidation Editor, under Elements for this consolidation, drag

and drop an existing consolidation element into the expression window.
2 Select the appropriate mathematical operator.
3 Repeat steps 1 to 2 as necessary.
You can also import consolidation elements from other consolidations in the
project.
To import a consolidation element:
1 In the Consolidation Editor, on the Elements menu, select Import

Elements.
2 In the Select a Consolidation window, browse to and select another

consolidation.
3 Click Open.
4 In the Import Consolidation Elements window, select the consolidation

elements you want to import and click OK.
5 In the Consolidation Editor, click Save and Close.

To format individual consolidation elements:
1 In the Consolidation Editor, under Elements for this consolidation,

right-click the consolidation element you want to format, point to
Formatting properties and select Element Headers or Element Values.
2 In the Format Cells window, specify the formatting for the element
headers or values and click OK.
3 In the Consolidation Editor, click Save and Close.
You can also specify whether or not to show subtotals for the consolidation
elements of a consolidation.
To enable subtotals for a consolidation:
1 In the Consolidation Editor, on the Elements menu, point to Subtotals

and select Enabled.
Tochange
ensure that the subtotals are the correct values, you may need to
the Subtotals over Consolidations Compatibility VLDB
property. VLDB properties are covered in depth in the MicroStrategy
Engine Essentials course.

Custom Groups

Explain the significance of custom groups and use the Custom Group Editor
to create them.
Business Scenario: Special Promotions

As the Marketing Director, your job is to maintain a strong relationship with
your best customers and to increase sales. You want to offer your best
customers special promotions, like heavily discounted prices for items that
have not sold very well. To do this, you need to obtain a list of your top 10
customers along with a list of your 5 poorest-selling items on the same report.
How can you accomplish this?
Top 10 Customers and Bottom 5 Items
182 Custom Groups 2014 MicroStrategy Inc.

What Is a Custom Group?

A custom group is an object consisting of custom group elements that you
can place on a template. Each custom group element is defined with its own
set of filter qualifications.
As you know, a MicroStrategy report is made up of one template and one

report filter. A custom group enables you to apply different filter
qualifications to different rows of a report. Thus, the final report is similar to
a collection of smaller reports stacked one on top of another.
For example, in the special promotions business scenario, you want to be able
to view your 10 best customers along with your 5 worst products. You are
essentially asking for two different report result sets, but you want them to be
displayed on the same report. To do this, you can create a custom group with
two custom group elements. Each custom group element has a different filter
qualification applied to it. In this case, the first custom group element is a
rank of Revenue for the top 10 customers, and the second custom group
element is a rank of Revenue for the bottom 5 items.
Custom Groups and SQL Query

Custom groups are essentially made up of different reports that are stacked
one on top of the other to display as if they were one report. Because custom
groups can include several custom group elementsthat all use different filter
qualifications, the SQL query for a report with a custom group can be
complex.
Each of the custom group elements, or mini-reports, that make up the

custom group require at least one, and possibly several, SQL passes. In
addition, there is a final SQL pass to consolidate the different data sets from
the many passes for the final report. Numerous intermediate tables are often
created (and later dropped) to store the intermediate data sets.
Running a report with a custom group is equivalent to running many

different reports and consolidating their result sets into a single report. A
single report with a custom group that is made up of x number of custom
group elements uses at least as many resources as running x number of
reports, individually. As a report designer, you should evaluate the resources
required for a report that generates many SQL passes against the benefits of
the report result.
2014 MicroStrategy Inc. Custom Groups 183

Custom Group Editor

You create custom groups using the Custom Group Editor:
Custom Group Editor
The Custom Group Editor is similar to the Filter Editor. This is not surprising
because custom groups are very closely related to filters. The difference is
that a custom group enables you to apply different filter qualifications to each
row of a report.
Custom Group Elements
A custom group is made up of custom group elements. Each custom group

element is defined with its own filter, and similar to a filter, each custom
group element is made up of the following:
A name (or header): This name is displayed as a row in the report results
and can be modified as desired. For example, you could create a custom
group with two custom group elements. The first custom group element
name could be Top 10 Customers and the second could be Bottom 5
Items.

Qualifications: You can define any type of filter qualification, including

attribute, metric, and embedded filters, to define a custom group element.
Each custom group element contains one or more qualifications. In the
previous example, Top 10 Customers includes one metric qualification
defined as Revenue, rank top 10 with the Output Level set to Customer.
Bottom 5 Items is defined with one metric qualification of Revenue, rank
bottom 5 with the Output Level set to Item.
Custom Group Definition
The Custom Group definition area displays the custom group element
headers and their associated qualifications. From this area, you can access
the following panes:
Custom Group element headerused to name the custom group element
Add Qualificationused to define the qualifications that should be

applied to the custom group element
Similar to a regular filter qualification, when adding a qualification for a

custom group element, you have the ability to choose any of the following
types of qualifications:
Attributefor example, Region = Northeast, Mid- Atlantic, and Southeast
Setmetric or relationship filter
Shortcut to a reportAny saved report
Shortcut to a filterAny saved filter
Custom group banding
Advanced qualificationJoint element list or custom expression
The advanced qualification is not displayed by default. You can enable this
option by following the steps below:
To display the Advanced Qualification option in the Custom Group Editor:
1 In Developer, right-click the project and select My Preferences.
2 In the My Preferences window, under Categories, expand Editors.
3 Click Custom Group.

4 Under Options, select Show advanced qualification.
5 Click OK.
When creating a custom group element based on a metric qualification, you

must set an Output Level. When defining a metric qualification in a report
filter, the attributes on the template typically dictate what displays on the
report, and thus, the level at which the metric qualification should apply.
With custom group elements, the metric qualification itself dictates what
displays on the report. Therefore, when basing a custom group element on a
metric qualification, you must specify the Output Level at which the metric
qualification should apply.
For more information on Output Level, see the Output Level

appendix starting on page 139.
To create a custom group:
1 In the Custom Group Editor, under Custom Group definition,

double-click Double-click here or drag an object from the object
browser to add a custom group element.
2 Name your custom group element.
3 Do on of the following to create a new qualification:
In the Object Browser, drag and drop a filter onto [Add Qualification]
OR
Double-click [Add Qualification].
IfBrowse
you create a metric qualification, for Output Level, click the
(...) button. In the Level window, add the attribute level at
which the metric qualification should apply to the Selected objects
window. Click OK.
4 To add more than one qualification to define a custom group element,

repeat steps 3 to 4.
5 To add more than one custom group element, repeat steps 2 to 5.

Custom Group Element Formatting
Each custom group element within a custom group can have different display
options. The display options vary from displaying only the custom group
element header to showing all the detailed items in the custom group
element. The following options are available:
Show only the element names on the report
Show only the individual items within this element
Show only the individual items within this element and also expand these
individual items if possible
Show the element names, individual items within this element and also,
expand these individual items if possible
The flexibility in display options enables you to show both summary and
detailed views of data on the same report.
To change the display option for a custom group element:
1 In the Custom Group Editor, right-click a custom group element header

and select Show Display Options.
2 In the Choose a display option window, select one of the available options.
3 Click OK.
To change the formatting options for a custom group:
You can also specify how you want to display the custom group as a whole (in
hierarchical or flat display) and whether or not to show subtotals.
1 In the Custom Group Editor, on the Custom Groups menu, select

Options.

2 In the Options window, select from the following display options:

Custom Group - Options Window
The Custom Group interaction with report filter section determines if

and how the report filter affects the custom group. Use the Help
button to learn more about this feature.
3 Click OK.
You can also sort report results by a custom group.
To sort by a custom group:
1 In a report with a custom group, on the Data menu, select Advanced

Sorting.
2 In the Sorting window, click Add.
3 Under Currently defined sorts, in Sort by list, select the custom group and
specify the sort criteria and order as usual.
4 Click OK.

Custom Group Banding

Rather than applying different qualifications to different rows of your report,
you may want to slice your data into multiple ranges or bands based on
metric values. Custom group banding enables you to easily define these data
ranges, and each band displays as a row on your report results.
Below is an example of a report with a custom group called Customers

Declining. Each row in this report was named and given rules for
determining how much data to include in its calculation.
Example - Custom Group Banding

After selecting a metric on which to band, you can choose to perform the
banding according to metric value, rank, or percent. Banding options include
Band Size, Band Count, Banding Points, and Band for each distinct metric
value.
Custom Group Banding Definition
Band Size enables you to slice a range of metric values defined by

beginning and ending values into a number of equal-sized bands. The
Step size parameter defines the size of each band.
Band Count enables you to define a number of equal-sized bands that will
slice through a specified range of metric values.
Banding Points enables you to specify the exact placement of bands to

display different-sized bands. This option provides the greatest degree of
flexibility when assigning bands. The example report that contains the
Customers Declining custom group is created using Banding Points.
Because banding involves metrics, you must select a Level when defining
a custom group banding qualification. The Level indicates the level at
which to apply the banding qualification.

Band for each distinct metric value enables you to create a separate band
for each value calculated by the metric. The bands appear as rows on a
report. This type of banding qualification directly uses the results of a
metric as bands. It is very useful when used with metrics that already
contain the logic needed to calculate sequential band numbers. Such
metrics use mathematical formulas, NTile functions, Band functions, or
Case functions.
For example, a metric uses the NTile function to group revenue values
into three groups. If you use this metric to define the custom group, the
elements are also sliced into three bands as shown below:
Example - Band for Each Distinct Metric Value
The following report is an example of custom group banding based on band

size. It displays customer revenue sliced into 10 value ranges, or bands:
Example - Band Size

In the example report earlier, the creator used the band size method (Start at:
$1, Stop at: $4000, Step size: 400):
Custom Group Editor - Band Size
To create the same report using the band count option, you would use the
same starting and stopping points with a band count of 10 (for the 10 bands
you see above). With the banding points option, the banding points would be
as follows: 1, 401, 801, 1201, 1601, 2001, 2401, 2801, 3201, 3601, 4000.
Creating a Custom Group Banding Qualification
To create a custom group banding qualification:
1 In the Custom Group Editor, under Custom Group definition,

double-click Double click here or drag an object from the object
browser to add a custom group element.
2 Name your custom group element.

3 Double-click [Add Qualification].
4 Under Custom Group Options, click Add a Custom Group Banding

qualification.
5 Click OK.
6 For Metric, select a metric on which to base the custom group banding.
7 For Band on, use the drop-down menu to select how you want to band:
Metric Value, Rank, or Percent.
8 For Banding type, select how you want to create your bands: Band Size,
Band Count, Banding Points, and Band for each distinct metric
value.
9 Based on the banding type you select, set the corresponding properties.
10 Click Level.
11 In the Level window, in the Output tab, add the appropriate attribute level
at which you want to band and click OK.
12 Click OK.
13 To customize the display of the band names, right-click the custom group
element and select Show Band Names Editor.
14 In the Band Name Editor, click Add to add new band names and Edit to
rename existing band names.
15 Click OK.
16 In the Custom Group Editor, click Save and Close.

Consolidations vs. Custom Groups

The following table provides a summary of the Consolidations and Custom
Group features:
Consolidations vs. Custom Groups
Consolidations Custom Groups
Differences Based on attribute element Based on filter qualifications

groupings
Usage To create a virtual attribute for To apply different filter qualifications

reporting at levels not existing in to different rows of a report
the project schema and displaying
row-level math calculations
Examples Seasons consolidation that groups Custom group that displays Top 10
specific months of the year into Customers and Bottom 5 Items
different seasons
Keep in mind that custom groups offer added flexibility because you do not
have to know much about your data to create the filter qualifications that
define each custom group element of the custom group. You set up the Top 10
Customers custom group element like you would any filter. By contrast,
consolidations require that you know exactly which attribute elements to
group to define the consolidation elements of the consolidation.


Lesson Summary
Consolidations enable you to group attribute elements together and place

them on a template like an attribute.
Consolidations serve as virtual attributes and enable you to perform

row-level math.
Consolidations consist of consolidation elements, which can be

individually formatted. You can even import consolidation elements from
other consolidations in the project to create consolidation elements.
Custom groups are objects that you can place on a report template. They
are made up of custom group elements, each defined with its own set of
filter qualifications.
Custom groups can be SQL-intensive, depending on the number of

custom group elements within them.
You can define a custom group element with any type of filter
qualification (attribute, set, shortcut-to-a-report, shortcut-to-a-filter,
custom group banding, and advanced).
You can control the formatting of each custom group element through the
Show Display Options window.
Custom group banding enables you to slice your data into multiple
ranges, or bands, based on metric values.
There are four custom group banding options: band size, band count,
banding points, and Band for each distinct metric value.
Custom group banding requires a specified Output Level.


MicroStrategy Developer: Advanced Reporting Exercises: Consolidations and Custom Groups 4

Exercises: Consolidations and Custom Groups
ConsolidationAnalysis by Customer Geography

You are the North American Vice President of Sales and Marketing in a
thriving start-up company. You need to see sales data from your four defined
customer areas in the US: Northeast, South, Central, and West.
Unfortunately, your data warehouse does not store data at the defined
regional level. A customer area attribute does not exist for you to place on the
report template. You need to create a customer area consolidation using the
Customer Region attribute to define the consolidation elements.
Solution:
1 Create a consolidation named US Customer Geography. Define the

following consolidation elements:
Northeast = Customer Region Northeast
South = Customer Region Mid-Atlantic + South + Southeast
Central = Customer Region Central
West = Customer Region Northwest + Southwest
2 Create a report using the above consolidation and Revenue.
3 Save the report to the My Reports folder as US Sales (Consolidation).
2014 MicroStrategy Inc. Exercises: Consolidations and Custom Groups 197

4 Exercises: Consolidations and Custom Groups MicroStrategy Developer: Advanced Reporting
ConsolidationMore Analysis by Customer Geography

You now need to analyze the sales ratio of each of the four customer areas to
total US sales. You can use your existing consolidation elements in the new
consolidation. Your report should look like the one below (notice that the
ratio elements are formatted as percentages):
Solution:
1 Create a new consolidation. For the first four consolidation elements,

import the consolidation elements from the US Customer Geography
consolidation created in the previous exercise. Then, add the following
consolidation elements:
For the US element:
US = Northeast + South + Central + West
Create all other consolidation elements using row-level math as shown

in the above report.
Right-click the last four consolidation elements to format the Element

Values as percentages with 2 decimal places.
2 Create a report using the new consolidation and the Revenue, Units Sold,
and Profit metrics.
3 Save the report to the My Reports folder as Geography Consolidation

Report.
198 Exercises: Consolidations and Custom Groups 2014 MicroStrategy Inc.

Custom GroupsTop 5
Suppose you are the business intelligence engineer at a large retail company.
The Director of Customer Sales submits a report request to identify the top
five customers, items, and employees in terms of Revenue. She would like to
see Revenue and Units Sold for these top five customers, products, and
employees. Instead of three separate reports, she asks for a single report with
all of the data.
The report should look like the one below:
Solution:
1 Create a custom group with three custom group elements that rank on
Revenue:
Top 5 Customers
Top 5 Items
Top 5 Employees
2 Set the display option for each custom group element to Show the
element names, individual items within this element and also,
expand these individual Items if possible.

4 Exercises: Consolidations and Custom Groups MicroStrategy Developer: Advanced Reporting
3 Create a report using the custom group you just created, Revenue, and
Units Sold.
4 Save the report to the My Reports folder as Top 5 Report.
Customer Banding
You need to analyze the Revenue for Customers that fall into the following
groups: top 10 percent by Revenue, the next 40 percent, and the bottom 50
percent. You want to see the figures for all groups on the same report. The
finished report should look like the following:
Solution:
You can create this report by creating a custom group with four custom group
elements, each representing the four rows of the above report. However, the
Custom Group Editor enables you to create this more conveniently with
custom group banding, using the steps below:
1 In the Custom Group Editor, start by creating a custom group called All
Customers.
2 Create a Custom Group Banding qualification with the following settings:
Metric = Revenue
Band on = Percent
Banding type = Banding Points
200 Exercises: Consolidations and Custom Groups 2014 MicroStrategy Inc.

For Banding points, enter the following band names and their points.
Level = Customer
3 Create a report with the custom group you created above and the Revenue
metric.


knowledge of the concepts covered in this lesson. They do not provide any
Brand Comparisons
The Marketing department of your company is looking to create a new
promotion for the Movie subcategory. Business analysts are curious to see
the past sales performance for a collection of movies grouped by Brand.
Specifically, they need to know what movies are selling morethose with
classic actors (like Gregory Peck and Paul Newman) or those with current
actors (like Tom Hanks and Tom Cruise). Create a report that displays
Revenue and Units Sold for these two Brand groups. This report should also
display the difference in Revenue and Units Sold between the two Brand
groups.
Use a consolidation to create two new groups of brands, Classic Actors

and Current Actors.
Classic Actors include Jack Nicholson, Gregory Peck, Anthony

Perkins, Marlon Brando, Paul Newman and Robert Redford.
Current Actors include Tom Hanks, Tom Cruise, Nicolas Cage, Mike
Myers, Leonardo Di Caprio and Mel Gibson.
Current Actors - Classic Actors would provide you with the difference
between the two Brand groups.

Your final report results should look as follows:
Employee Experience Analysis

You need to create a report that groups your employees by their experience
levels (in terms of tenure) and displays the Profit associated with them.
A custom group that displays Less experienced employees (8 years

tenure or less) and More experienced employees (9 years tenure or
more).
Use the Employee Experience attribute to define the custom

group elements.
Your report results may vary from the image below because the
Employee Experience attribute calculates tenure based on the
current system date:


5
ADVANCED OLAP SERVICES
Lesson Description
This lesson describes the functionality of MicroStrategy OLAP Services. As an

extension of Intelligence Server, OLAP Services uses Intelligent Cube
Technology, an in-memory version of report data that can be manipulated by
the MicroStrategy Analytical Engine. Developer, MicroStrategy Web, and
MicroStrategy Office users can slice and dice data in reports within the
Intelligent Cubes without having to re-execute SQL against the data
warehouse.

5 Advanced OLAP Services MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Explain the concept of Intelligent Cubes. Create Intelligent Cube reports that
share Intelligent Cube caches. Understand how view filters and dynamic
aggregation work while adding and moving objects from the grid to the
Report Objects pane.
Understand the concept of Intelligent Cubes. (Page 207)
Understand how Intelligent Cubes enables sharing sets of data among

different reports. Create and publish Intelligent Cubes. (Page 208)
Understand the various ways to build reports on Intelligent Cubes.

Describe features available for reports that access Intelligent
Cubes. (Page 221)
Understand how to apply view filter qualifications at the report or grid

level. You will also understand the concept of dynamic
aggregation. (Page 230)

MicroStrategy Developer: Advanced Reporting Advanced OLAP Services 5
Intelligent Cubes

Understand the concept of Intelligent Cubes.
MicroStrategy Intelligent Cube Technology enables you to create

Intelligent Cubes, which operate within Intelligence Server. Intelligent Cubes
are multi-dimensional cubes (sets of data) that can be shared as a single
in-memory copy among many different reports created by multiple users.
Rather than returning data from the data warehouse for a single report, you
can return sets of data from your data warehouse and save them directly to
Intelligence Server memory. You can then build multiple reports that gather
data from the Intelligent Cube instead of querying the data warehouse. The
reports accessing Intelligent Cubes can use all of the OLAP Services features
for analysis and reporting purposes.
MicroStrategy OLAP Services Intelligent Cube
2014 MicroStrategy Inc. Intelligent Cubes 207

Sharing Intelligent Cubes

Understand how Intelligent Cubes enables sharing sets of data among
different reports. Create and publish Intelligent Cubes.
MicroStrategy enhances the scalability and manageability of OLAP Services

with the addition of sharable Intelligent Cubes. With Intelligent Cubes you
can return a specific set of data from your data warehouse. Users can then
create reports that display and analyze a subset of the set of data defined in
an Intelligent Cube. This process is different than the common approach of
creating a report that directly accesses the data warehouse.
You create Intelligent Cubes and publish them as a shared data source from
which users can build reports. Intelligent Cubes provide the fast response
time and analytic calculations that are often associated with
Multidimensional Online Analytic Processing (MOLAP) cubes. They also
provide the ability to use Relational Online Analytic Processing (ROLAP) by
drilling into the full set of data outside of the Intelligent Cube.
Intelligent Cubes act as a layer between your data warehouse and

MicroStrategy reports that analyze and display data, as illustrated below:
Sharing Intelligent Cubes
208 Sharing Intelligent Cubes 2014 MicroStrategy Inc.

The abstraction that Intelligent Cubes provide between your data warehouse
and reports can improve the performance of your business intelligence
application. Reports that connect to an Intelligent Cube can perform
reporting and analysis manipulations within the Intelligent Cube without
hitting the data warehouse. These manipulations are executed much faster
than running a new query against a data warehouse.
Intelligent Cubes help to limit the amount of processing done in the data
warehouse and improve performance. A user working on a report that
connects to an Intelligent Cube can be restricted to add attributes to the
report grid, only if the attributes are included in the Intelligent Cube.
You can enable Relational Online Analytic Processing (ROLAP) by

drilling into the full set of data outside of the Intelligent Cube. For
more information see Enabling ROLAP Drilling for Reports
Accessing Intelligent Cubes starting on page 214.
Creating Intelligent Cubes

Creating Intelligent Cubes is as easy as creating reports. After you have
created your Intelligent Cube, you can publish its set of data to be shared by
multiple reports.
Intelligent Cubes act as a set of data to which other reports can connect and
use OLAP Services to report, analyze, and display data. There is no need for
Intelligent Cubes to contain objects that modify the display of data. The
display of data is handled by the reports that access the Intelligent Cube.
2014 MicroStrategy Inc. Sharing Intelligent Cubes 209

Building an Intelligent Cube
Building an Intelligent Cube is similar to building a report. You can add data
to your Intelligent Cube by including objects such as attributes, metrics,
filters, and so on as report objects or report filters.
Example - Intelligent Cube

Since Intelligent Cubes are used simply to share a set of data, no data or
report results are displayed when you execute an Intelligent Cube. However,
executing an Intelligent Cube publishes the Intelligent Cube, which can then
be accessed as a set of data for multiple reports.
Publish the Intelligent Cube
To build an Intelligent Cube:
1 In Developer, on the File menu click New and select Intelligent Cube.
2 In the New Intelligent Cube window, select Empty Intelligent Cube and
click OK.
3 In the Report Editor, add objects such as attributes, metrics, and so on to

the Intelligent Cube, as you would add report objects.
You can also create an Intelligent Cube that contains data from an
MDX cube integrated into MicroStrategy from an MDX source,
such as SAP BI, Hyperion Essbase, or Microsoft Analysis Services.
For information on integrating MDX sources into MicroStrategy,
see the MDX Cube Reporting Guide.
4 Create a filter for the Intelligent Cube as needed.
Ifrestricted
you create a filter on an Intelligent Cube, any data that is
from the Intelligent Cube is not available for any reports
that connect to the Intelligent Cube. This helps reduce the size of
the Intelligent Cube.

5 Click Save and close to save the Intelligent Cube and close the Report
Editor.
6 In Developer, right-click on the Intelligent Cube and click Run.
The following list of objects and features cannot be included in the definition
of Intelligent Cubes as they can be for reports:
Consolidation and Custom Groups cannot be included in Intelligent

Cubes or in reports that access Intelligent Cubes.
OLAP Services features such as view filters, derived metrics, and

derived elements cannot be included in Intelligent Cubes, but these
features are the main analysis tools for reports that access Intelligent
Cubes.
Prompts cannot be included in Intelligent Cubes. However, reports that

access Intelligent Cubes can use prompts that access only data included in
the Intelligent Cube.
Converting Reports into Intelligent Cubes
You can convert an existing report that contains a set of required objects, into
an Intelligent Cube. Creating an Intelligent Cube by converting a report
enables you to view the set of data you are including in your Intelligent Cube.
With this method you can verify that you have the set of data you want, rather
than just the required report objects, before you publish it as an Intelligent
Cube.
When you convert a report to an Intelligent Cube, some parts of the report
are not included in the resulting Intelligent Cube. Intelligent Cubes are not
used for the same display and analysis purposes as a report. Intelligent Cubes
simply act as a sharable set of data. Therefore, when a report is converted
into an Intelligent Cube, some of the display and analysis features are no
longer necessary.
To convert a report to an Intelligent Cube:
1 Right-click the report and select Edit.
You need the Use Intelligent Cube Editor privilege to create

Intelligent Cubes. This privilege is part of OLAP Services.
2 In the Report Editor, from the Data menu, point to Intelligent Cube
Options and select Convert to Intelligent Cube.

3 Save the Intelligent Cube.
Ifconverting
you save the Intelligent Cube using the same name after
a report to an Intelligent Cube, the original report is
lost. To keep the original report, select Save As and save the
Intelligent Cube using a different name than the report.
4 You must publish an Intelligent Cube to make it available for multiple

reports to access and report on its set of data.
The general recommendation is to design in-memory cubes with less than

5GB. Staying within this threshold should provide optimal performance in
terms of report response times and overall user scalability. While larger cube
sizes are feasible, it is important to consider user concurrency requirements
and make sure they can be supported using larger cube sizes or alternatively
look for opportunities to reduce the overall cube size. The table below
displays throughput considerations for in-memory cubes, depending on the
cube size:
For further explanation about optimal cube sizes and best practices
recommendations, refer to the MicroStrategy Administration:
Configuration and Security course.
Refreshing Intelligent Cubes using Schedules
While you are creating or modifying an Intelligent Cube, you can schedule
when an Intelligent Cube should be re-executed against the data warehouse
to refresh its data. For example, you can schedule to re-execute and republish
an Intelligent Cube when a database load occurs.
If the Intelligent Cube is updated based on one or more attributes, you can
enable Incremental Refresh to update the Intelligent Cube with only new
data. This significantly reduces the time and system resources needed to
update the cube periodically.
For detailed instructions on defining the incremental refresh settings, refer to

the MicroStrategy Administrator: Configuration and Security course.
The procedure below describes the steps to create a subscription that

schedules the re-execution and republication of a single Intelligent Cube.

To schedule an Intelligent Cube to be refreshed:
1 In Developer, browse to an Intelligent Cube.
2 Right-click the Intelligent Cube, point to Schedule Delivery To and click

Refresh Cube.
3 In the Refresh Cube Subscription Editor, select a schedule from the

Schedule drop-down list.
4 Select other schedule options as required.
5 Click OK.
Once the schedule is triggered the Intelligent Cube is re-executed against the
data warehouse and published to the Intelligent Cube Monitor.
Enabling ROLAP Drilling for Reports Accessing Intelligent

Cubes
All reports that access Intelligent Cubes can drill within the data included in
an Intelligent Cube. This provides ROLAP-type analysis without having to
re-execute against the data warehouse. For example, an Intelligent Cube
includes Year and Quarter. A report accessing the Intelligent Cube only
includes Year on the report. On the report, you can drill down from Year to
Quarter, which returns the results without any extra load on the data
warehouse or Intelligence Server.

Reports that access an Intelligent Cube can be granted full ROLAP access to
the data warehouse through drilling. This means that you can enable drilling
outside of the Intelligent Cube to access the full set of data available in the
data warehouse.
While this extends the analysis and data access capabilities of reports that
access Intelligent Cubes, drilling outside of an Intelligent Cube can require an
additional load on the Intelligence Server and data warehouse. This is
because drilling outside of an Intelligent Cube requires a new report to be
executed against the data warehouse.
The decision to enable or disable drilling outside of an Intelligent Cube

depends on a few factors. You should consider the size and complexity of
your Intelligent Cubes when deciding whether to enable drilling outside of an
Intelligent Cube. While enabling drilling outside of relatively small
Intelligent Cubes can give the benefit of ROLAP analysis through drilling,
enabling this analysis on relatively large Intelligent Cubes has the potential to
cause increased load on your data warehouse and Intelligence Server.
By enabling drilling outside of an Intelligent Cube, reports that access the

Intelligent Cube have ROLAP access to data through drilling. For example, an
Intelligent Cube includes Year but not Quarter. A report accessing the
Intelligent Cube includes Year and drilling outside of the Intelligent Cube is
enabled. On the report, you drill down from Year to Quarter. This causes a
new report to be created and executed against the data warehouse. The
benefit of enabling this extra analysis can come with a performance cost.

When this drilled-to report is created, only objects that were on the report
layout of the report you drilled from are included in the drilled-to report. Any
objects that are only in the Report Objects pane of the report you drilled from
are not included in the drilled-to report. This can help reduce the size of the
drilled-to report. However, if you are drilling from a report that accesses a
large Intelligent Cube, it is possible that a user could include all objects of an
Intelligent Cube on a report. Drilling outside of the Intelligent Cube on such a
report could cause extra load on the data warehouse and the Intelligence
Server.
To enable or disable drilling outside of an Intelligent Cube for reports

accessing a specific Intelligent Cube:
1 In Developer, browse to an Intelligent Cube.
2 Right-click the Intelligent Cube and select Edit.
3 On the Data menu, select Configure Intelligent Cube.

Configure Intelligent Cube
4 In the Intelligent Cube Options window, in the Categories list, expand

Options and select General.
5 Under Drilling, clear the Use default settings check box.
6 Select the Allow reports to drill outside the Intelligent Cube check box.
You can clear this check box to disable drilling outside of an

Intelligent Cube.
7 Click OK.

9 You must publish the Intelligent Cube again, to make your changes
available for reports accessing the Intelligent Cube.
To enable or disable drilling outside of an Intelligent Cube for reports

accessing any Intelligent Cubes in a project:
1 In Developer, log in to a project with an administrator user account.
2 Right-click the project and select Project Configuration.
3 In the Project Configuration Editor, in the Categories list, expand

Intelligent Cubes and select General.
4 Select the Allow reports to drill outside the Intelligent Cube check box.
Project-Level Setting for Intelligent Cube
5 Click OK.
6 You must re-publish the Intelligent Cubes, to make your changes

available for reports accessing the Intelligent Cubes.

Publishing Intelligent Cubes

Publishing an Intelligent Cube retrieves data from the data warehouse and
stores this data as an Intelligent Cube in the Intelligence Servers memory.
Once an Intelligent Cube is stored in Intelligence Server memory, you can be
created multiple reports to view and analyze the set of published data.
When an Intelligent Cube is published, the Intelligent Cube Monitor displays

the Intelligent Cube. You can manage your published Intelligent Cubes from
the Intelligent Cube Monitor.
For more information on managing Intelligent Cubes, see the System

Administration Guide.
Monitor Published Intelligent Cubes
You can publish Intelligent Cubes manually, or you can schedule the
publication of Intelligent Cubes.
Publishing Intelligent Cubes Manually
The procedure below provides the steps to manually publish an Intelligent

Cube.

The act of publishing an Intelligent Cube can require memory

resources approximately two times greater than the size of the
Intelligent Cube. This can affect the performance of your Intelligence
Server.
To publish an Intelligent Cube manually:
1 In Developer, browse to the Intelligent Cube.
2 Right-click the Intelligent Cube and select Run.

Published Intelligent Cube
Rather than displaying report results, a message is displayed indicating that

the Intelligent Cube has been published. After the Intelligent Cube is
published, the Intelligent Cube displays in the Intelligent Cube Monitor,
which you can use to manage your published Intelligent Cubes.
Unpublishing an Intelligent Cube
You can remove a published Intelligent Cube so that its data is no longer
accessible by reports. This action does not delete the Intelligent Cube object
saved in a MicroStrategy project, it only removes the Intelligent Cube from
the Intelligent Cube Monitor.

Unpublishing an Intelligent Cube prevents reports that access an Intelligent

Cube from being able to load the Intelligent Cube into Intelligence Server
memory. This gives you more administrative control of when to make an
Intelligent Cube available to reports.
Unpublishing an Intelligent Cube means that any reports that access

the Intelligent Cube cannot be executed.
To unpublish an Intelligent Cube:
1 In Developer, log in to a project source with administrative privileges.
2 From the Folder List, expand Administration, expand System Monitors,

expand Caches, and select Intelligent Cubes.
3 Right-click an Intelligent Cube and select Delete.
The Intelligent Cube is unpublished from the Intelligent Cube Monitor, and
its data cannot be accessed by reports.
Unpublish an Intelligent Cube

Reporting and Analyzing Data with Intelligent

Cubes

Understand the various ways to build reports on Intelligent Cubes. Describe
features available for reports that access Intelligent Cubes.
After an Intelligent Cube is created and published, its data is made available
in the memory of the Intelligence Server, and multiple reports can access it
for data.
Reporting on a shared Intelligent Cube provides quick access to data, as the

data is pre-aggregated. This returns report results much faster than querying
the data warehouse.
You can restrict reports that access an Intelligent Cube to only the data
available within the Intelligent Cube. This ensures that report results are
returned quickly, and it also prevents full ROLAP analysis. Report filters,
consolidations, and custom groups are not available for reports that access
Intelligent Cubes.
Creating Reports that Access Intelligent Cubes
To create a report that accesses an Intelligent Cube, you must first choose an
Intelligent Cube.
To create a report that accesses an Intelligent Cube in Developer:
1 In Developer, log in to a project.
2 From the File menu, point to New and select Report.
2014 MicroStrategy Inc. Reporting and Analyzing Data with Intelligent Cubes 221
3 In the New Grid window, select the Intelligent Cubes tab.

New Grid Window - Intelligent Cubes Tab
4 Browse to an Intelligent Cube, select it, and then click OK.
The Report Editor opens with all of the objects of the Intelligent Cube
included in the Report Objects pane on the left. You can begin to create your
report.
InCubeDeveloper, you can also create a report that accesses an Intelligent

by right-clicking an Intelligent Cube and selecting Create
Report.
Accessing a Different Intelligent Cube
You can change the Intelligent Cube accessed by a report. This enables you to
switch to an Intelligent Cube that fits your reporting requirements without
having to create a new report.
However, the Intelligent Cube to which you should have the same, or at least
similar, data to the previous data. If the report contains objects that are not in
the new Intelligent Cube the report looses the availability and functionality of
the objects. A warning message displays for any mismatch in data between
the report and the Intelligent Cube to which you want to link the report.
222 Reporting and Analyzing Data with Intelligent Cubes 2014 MicroStrategy Inc.
To access a different Intelligent Cube:
1 In Developer, browse to a report that is connected to an Intelligent Cube,

right-click the report and select Edit.
2 In the Report Editor, from the Data menu, point to Intelligent Cube
Options and select Point grid to Intelligent Cube.
Access a Different Intelligent Cubes
3 Browse to the new Intelligent Cube to which you want to link the report,
select it, and click Open.
IfCube,
there are data mismatches between the report and the Intelligent
a warning message is displayed that lists the differences.
Attributes on the report that are not contained in the new
Intelligent Cube are removed from the report to display valid
report results. Missing metrics are converted into derived metrics
and may be able to display correct data, but they should be
removed if no data is returned for their values.
4 If the differences are acceptable, click OK.
The report is updated to access the Intelligent Cube selected. You should
remove any attributes and metrics if they no longer return correct results.
Run-time Reporting with Prompts

Prompts enable users to choose which objects and filtering criteria to apply to
a report during report execution. They serve the same purpose in any report,
including reports that access Intelligent Cubes. However, instead of
modifying SQL at report run time, prompts enable reports to select data
within the Intelligent Cube, as illustrated below:
Intelligent Cube and Prompted Reports
The image above shows standard run-time reporting with prompts, while
using OLAP Services to execute against the Intelligent Cube rather than
against the data warehouse. The performance of your business intelligence
application is improved by reducing execution against your data warehouse
and maintaining only a single Intelligent Cube for multiple prompted
reports.
Prompts on reports that access Intelligent Cubes can only access data that is
available within the Intelligent Cube. These restrictions are applied
automatically when creating prompts. For example, the attributes Year and
Region and the metrics Cost and Revenue are included in the Intelligent
Cube shown in the image on the previous page. If you create an object prompt
in your report that accesses this Intelligent Cube, then you can only choose
from the four objects Year, Region, Cost, and Revenue.
Any prompts that include objects or data not included in an Intelligent

Cube are not available for reports that are connected to the Intelligent
Cube. An error message is displayed if you attempt to include these
prompts in reports that cannot support their data requirements.
The following prompts are supported in Intelligent Cube reporting:
Element prompt
Attribute Qualification prompt
Metric Qualification prompt
Value prompt
Object prompt
These prompts can be added to the Intelligent Cube report only in design
mode. They can be part of the Intelligent Cube report template or view filter
expression.
Hierarchy prompts and level prompts are not supported in Intelligent Cube
reports.
Relational Analysis with Drilling

As with any MicroStrategy reports, you can drill on reports that access
Intelligent Cubes to analyze data at different logical levels.
A report connected to Intelligent Cubes can drill within the set of data
available in the Intelligent Cube. This means that you can drill from an
attribute on the report grid to an attribute that is not on the report grid but
available in the Report Objects pane. If the attribute is not available in the
Report Objects pane, it is not an available drilling option by default.
However, you can enable the option to drill outside of an Intelligent Cube to
the full relational data warehouse.
For example, your report includes the Year attribute. After analyzing data at
the Year level, you want to analyze data for each quarter. You can drill down
from Year to the attribute Quarter to view and analyze data at the new logical
level. This drilling action is performed within an Intelligent Cube.
You have a report connected to an Intelligent Cube that is defined as shown

below:
ExampleDrilling Within the Intelligent Cube
The Quarter attribute is not on the report, but it is included in the Report
Objects pane, as it is a part of the Intelligent Cube to which the report is
connected. In the image above, the user right-clicks the 2011 attribute
element for Year and drills down to Quarter. The drilled-to report is shown
below:
ResultDrilling Within the Intelligent Cube
This drilled-to report is executed within and connected to the same

Intelligent Cube as the original report. This is verifiable by looking at the
Report Objects pane, which shows that the report objects are returned from
the same Intelligent Cube as the original report. This provides relational
analysis without having to execute the report against the data warehouse.
In the scenario above, drilling is performed within the Intelligent Cube,

which is achievable through any report connected to the Intelligent Cube.
However, if the Intelligent Cube is defined to enable drilling outside of the
Intelligent Cube, you can also drill to any object not included in the
Intelligent Cube. While drilling outside of the Intelligent Cube requires
execution against the data warehouse, it provides access to the full ROLAP
schema of the project outside of the Intelligent Cube.
In the next example, the same scenario of drilling from Year to Day is used,
except that the Intelligent Cube does not contain the Day attribute. As shown
in the report below, you right-click the 2011 attribute element for Year and
drill down to Day.
ExampleDrilling Outside the Intelligent Cube
Notice in the report shown above that all the attributes in the Time hierarchy
are available drilling options even though they are not all included in the
Intelligent Cube. These attributes are available drilling options because the
Intelligent Cube is defined to enable drilling outside of the Intelligent Cube.
As shown in the report above, you right-click the 2011 attribute element for
Year and drill down to Day.
The drilled-to report is shown below:

ResultDrilling Outside the Intelligent Cube
This drilled-to report is executed against the data warehouse, and it enables
you to access data outside of the Intelligent Cube for further relational
analysis. Also, all report objects that were not on the report grid are removed
from the Report Objects pane. This is because the drilled-to report is not
connected to the Intelligent Cube to which you were originally connected.
You should consider the execution time requirements for a report

before drilling outside of an Intelligent Cube.
Unavailability of Intelligent Cubes

If an Intelligent Cube is unavailable, this can cause your reports connected to
that Intelligent Cube to fail. The reports fail because they depend on the
Intelligent Cube to provide the data for the report.
The scenarios listed below cause reports to return error messages when
Intelligent Cubes are unavailable:
The Intelligent Cube is not published.
The Intelligent Cube is currently offline.
The Intelligent Cube is being published, but the publishing process is not
finished yet.
Reporting on Intelligent Cubes with Dynamic Sourcing

You can manually connect a report to an Intelligent Cube to perform all
reporting and analysis within the shared in-memory copy of data. Even if you
do not manually connect your reports to an Intelligent Cube, the reports you
create can still access Intelligent Cube data instead of querying the data
warehouse automatically. Enabling this automatic link between reports and
Intelligent Cubes is referred to as dynamic sourcing.
Dynamic sourcing extends the accessibility of Intelligent Cubes by allowing

regular reports to access published Intelligent Cubes, as long as the
Intelligent Cubes can satisfy the data requirements of the report.
Supporting Report Execution through Dynamic Sourcing
When creating reports, you can enable and increase the chances that your
report utilizes dynamic sourcing by following the recommendations below.
First, you must enable dynamic sourcing for your report. This can add some
overhead to the execution of your report, because the report checks whether
there are any Intelligent Cubes that meet the data requirements of your
report. This overhead usually has no effect on performance. If an Intelligent
Cube is used, the report results return quickly from the in-memory copy of
data rather than querying the data warehouse.
For more information on dynamic sourcing, see the MicroStrategy

OLAP Services Guide product manual.
View Filters and Dynamic Aggregation

Understand how to apply view filter qualifications at the report or grid level.
You will also understand the concept of dynamic aggregation.
View filters are a feature of OLAP Services that enable you to further narrow
down a result set of a report using only the objects in the Report Objects
window. You build view filters with report objects regardless of whether they
are displayed in the report view. When you add or modify a view filter, the
original report filter stays intact, and the view filter criteria is applied to the
report view.
View Filter: Metric Qualification Level

When a metric qualification in a view filter is evaluated by the system, the
evaluation can be performed for all data that is returned for the report, or
only the view of data that is currently available on the report grid. The two
options can produce different report results when using the OLAP Services
feature called dynamic aggregation.
Dynamic aggregation occurs when an attribute is moved between the report

layout and the Report Objects pane.The action of moving attributes on or off
the report grid aggregates the metric values at the new level of the report.
For more information see Dynamic Aggregation starting on

page 405.
When creating view filters, it is important to be aware that the filtering is

processed against the base report results. The default behavior is that any
metric calculations in a view filter are evaluated at the level of data that is
available on the report grid. This means that any attributes that are included
in the Report Objects pane, but not on the report grid, are not used to
determine the level of the metric qualification.
230 View Filters and Dynamic Aggregation 2014 MicroStrategy Inc.

For example, you create a report with Year, Region, Category, Revenue, and
Profit on the report. You can use dynamic aggregation to drag and drop the
Category attribute from the report grid to the Report Objects pane. This
allows Category to affect the report level without being displayed on the grid
and data is aggregated dynamically at the Year and Region level.
ExampleDynamic Aggregation
You decide to analyze this report to show only the data that is within the top
10% of profit. The view filter and resulting report are shown below:
ExampleEvaluate View Filter at Grid Level
The metric qualification has been evaluated at the level of the report grid,
which is Year and Region. This gives you a view of data within the top 10% of
profit for the data displayed on the report grid.
2014 MicroStrategy Inc. View Filters and Dynamic Aggregation 231

However, this report also includes the Category attribute in the Report
Objects pane. Since this attribute is available on the report, you can also view
data within the top 10% of profit at the Category, Region, and Year level.
Evaluating the metric qualification at this level returns the report results
shown below:
ExampleEvaluate View Filter at Report Objects Level
Notice that there are many more rows of data that are within the top 10% of
profit. This is because Category is now included in the calculation of the
metric qualification.
To modify the evaluation level of metric qualifications in a view filter:
1 Run the report in Grid View.
2 In the View Filter area, right-click a metric qualification and select one of
the following options:
Apply Condition at the Grid Level
Apply Condition at the Level of the Report Objects
3 If the Auto-Apply Changes check box is cleared, click Apply to apply the
view filter to the report.
232 View Filters and Dynamic Aggregation 2014 MicroStrategy Inc.


Lesson Summary
An Intelligent Cube (also known as a Shared Intelligent Cube) is a set of

data that reports can share as a single in-memory copy by multiple users.
OLAP Services can analize them, while reducing access to the data
warehouse.
The steps to utilizing an Intelligent Cube are as follows:
Create your Intelligent Cube
Publish its set of data for multiple reports to share
An Intelligent cube cannot include: Consolidations, custom groups, view

filters, derived metrics, derived elements and prompts.
Drilling can grant full ROLAP access to the data warehouse to Reports
that access an Intelligent Cube.
Prompts on reports that access Intelligent Cubes can only access data that
is available within the Intelligent Cube.
You can drill on reports that access Intelligent Cubes to analyze data at
different levels within the Intelligent Cubes.
If your administrator has given you the necessary privileges,you can

define Intelligent Cubes to enable drilling outside of an Intelligent Cube to
the data warehouse.
Enabling an automatic link between reports and Intelligent Cubes is

referred to as dynamic sourcing.
You can apply view filter metric qualifications at the lowest attribute
levels in the Report Objects window or at the report grid level.


MicroStrategy Developer: Advanced Reporting Exercises: Intelligent Cubes 5

Exercises: Intelligent Cubes
Creating and Reporting on Intelligent Cubes
Overview
The purpose of this exercise is to demonstrate how to create, publish and

build reports on an Intelligent Cube. Your Intelligent Cube should be
comprised of the following objects:
Intelligent Cube Objects
2014 MicroStrategy Inc. Exercises: Intelligent Cubes 235

5 Exercises: Intelligent Cubes MicroStrategy Developer: Advanced Reporting
You should receive the following confirmation that your Intelligent Cube was
published successfully:
Published Intelligent Cube
You will then create a report accessing the Intelligent Cube My First
Intelligent Cube. The report you create should look like the image below:
Report Accessing Intelligent Cube
236 Exercises: Intelligent Cubes 2014 MicroStrategy Inc.

How do you know you are creating this report based off My First Intelligent
Cube intelligent Cube?
Solution:
1 Right-click in the Object Viewer, point to New, and select Intelligent

Cube.
2 Select Empty Intelligent Cube, and click OK.
3 In the Intelligent Cube Editor, using the Object Browser, browse to the
Year, Category, and Subcategory attributes and add them to the Report
objects window.
4 Browse to the Revenue and Cost metrics and add them to the Report
objects window.
Unlike a standard report, the objects in the Intelligent Cubes Report

objects window automatically get added to the Intelligent Cube
template as well.
5 Add the Year 2011 and 2012 attribute elements to your report filter.
6 Click the Run Report icon to publish the Intelligent Cube.
7 A pop up window displays, requesting you to save the report before

executing it. Click Yes and name the Intelligent Cube My First Intelligent
Cube.
You can also save and close the report after step 5, then double-click
the Intelligent Cube to run it.
8 After the Intelligent Cube is published a confirmation display message

displays.
After the Intelligent Cube is published, it will be displayed by the

following item in the Intelligent Cube monitor.
10 Right-click My First Intelligent Cube and select Create Report.

11 Add the Year and Category attributes and the Revenue metric to the
template.
The Object Browser is not available for reporting on Intelligent Cubes

and you should see the My First Intelligent Cube name in the Report
Template window, which indicates the report is based on the
Intelligent Cube.
12 Click the Run Report icon.
13 Click Save and Close, and name your report My First Intelligent Cube
Report.
Alternative solution to create a report accessing your Intelligent Cube:
1 Right-click in the Object Viewer, point to New, and select Report.
2 Select the Intelligent Cubes tab, and browse to the Intelligent Cube you
want to use for your Intelligent Cube report.
Utilizing Prompts in Intelligent Cube Reports

In this exercise, you will first convert an existing report into an Intelligent
Cube. You will then create an Intelligent Cube report and include an object
prompt to analyze the result. You will also study the behavior by adding an
attribute that is not a part of the Intelligent Cube.
1 In the MicroStrategy Tutorial project, browse to Public

Objects/Reports/MicroStrategy Platform Capabilities/MicroStrategy
OLAP Services/OLAP Reports folder, and run the OLAP Report - Sales
by Geography, Product and Time report.
2 On the File menu, select Save As. Name the report OLAP Report and
save it in My Reports folder.
3 On the View menu, select Design View.

4 On the Data menu, point to Intelligent Cube Options and select Convert
to Intelligent Cube.
The Convert to Intelligent Cube option is available only in design

mode. It will not be seen in the grid, graph or grid graph view of a
report.
5 Click Yes on the warning message. The report gets converted to an

Intelligent Cube.

6 Save and Close the Intelligent Cube with the default name Cube
converted from OLAP Report and save it in My Reports folder.
Atyetthis point the report is just converted into Intelligent Cube. It is not
published.
7 Double-click the Cube converted from OLAP Report to publish it.
Create a prompted report using the above define Intelligent Cube
8 Right-click Cube converted from OLAP Report and select Create

Report.
9 Add the Category attribute to the template.
10 On the Template menu, select Add New Prompt.
Aprompt
prompt generation wizard window opens, and only Object
is available.
11 In the Prompt Generation Wizard, click Next.
12 Click Add, browse to the Public Objects/Metrics/Sales Metric folder and

select Revenue, Cost, and Units Sold metrics.
13 Click OK.
14 Click Next.
15 Name the prompt Metric Objects.
16 Click Finish.
17 Name the prompt Metric Objects and save it to the My Reports folder.
18 On the toolbar, click Run Report.
19 Choose the Revenue and Units Sold metrics when prompted.
20 Click Finish to run the Intelligent Cube report.

Your Intelligent Cube report results should look like the following:
21 Click Save and Close and name the report Intelligent Cube Report with
Object Prompt.
Your report should be saved as a prompted report.
Add an attribute prompt for Country to this Intelligent Cube report.
22 Right-click in the Object Viewer, point to New, and select Prompt.
23 Click Filter definition prompt, select Choose from an attribute element

list, and click Next.
24 In the Attribute box, type Country and press Enter.
25 Select the Country attribute, click OK, and click Finish.
26 In the Save As window, name the prompt Country Prompt and click
Save.
27 Right-click the Intelligent Cube Report with Object Prompt Intelligent

Cube report and select Edit.
28 In the view filter panel, for Field option, select Select a Prompt.
29 Browse to the My Reports folder and select your Country Prompt.

30 You should get the following error message because the Country attribute
is not part of the Intelligent Cube of which this Intelligent Cube report is
based:
31 Click OK and close the report without saving it.

View Filters in Intelligent Cube Report
Overview
The purpose of this exercise is to demonstrate how to create a view filter by

qualifying on attributes and metrics on your report. You will see how the view
filter is applied to an Intelligent Cube. In this exercise, you will use the My
First Intelligent Cube Report. Your view filter and results should look like
the following image:
What happens when you change the behavior of the view filter condition for
Revenue to the following options?
Apply Condition at the Level of the Report Objects
______________________________________
______________________________________
Apply Condition at the Grid Level
______________________________________
______________________________________

Detailed Instructions
1 Using the Object Browser, browse to My Personal Objects/My

Reports/Intelligent Cube Exercise folder, right-click My First Intelligent
Cube Report and select Edit.
2 On the File menu, select Save As, and save your report as My First
Intelligent Cube Report with View Filter.
3 On the Insert menu, select New Metric.
4 Define the new metric with the definition of Revenue - Cost. Name this
metric Profit.
5 Click OK.
7 In the view filter panel, create a metric qualification on Revenue where

Revenue is Greater than $1,000,000.
8 In the view filter panel, create an attribute qualification for Year 2012.
View filters on attributes are always displayed at the top of the

View filter panel.
9 Create another attribute qualification for the Books and Electronics

categories.

Asexpressions
soon as this filtering condition is added, the two view filter
on attributes Category and Year get combined using
the logical operator AND. A user can click AND to change the
logical operation to either OR or NOT.
10 Create another metric qualification for Cost Greater than $500,000, and
press Enter on the keyboard.
Your view filters and results should look like the following:

Changing the Behavior of View Filter Conditions by Applying Metric

Filtering at the Intelligent Cube or Grid Level.
11 In the View filter panel, right-click the Revenue metric and select Apply
Condition at the Level of the Report Objects, as shown below:
The attributes Category and Year represent all the attributes present
in the Intelligent Cube.
After the option is selected, the View filter panel looks like:
The metric expression on Revenue gets combined with the other

attribute expression in the view filter. Also, a symbol displays next to
the metric qualification indicating that it is applied at the Intelligent
Cube level.

12 Your new report results are as follows:
13 Right-click Revenue and select Apply Condition at the Grid Level to

apply the Revenue metric qualification at the grid level.
Your new report results are as follows:
There are two ways to apply view filtering on a metric:

Using the view filter panel (described above)
Applying the condition to the metric in the Intelligent Cube report

template
Right-click the metric on the template, point to Filter On and select

the appropriate submenu option, as described below.
The Filter On menu item has submenu items, depending on whether

the metric already has a view filter or not.
If there is no prior filtering condition on the metric, the following

submenu items are available:
If the metric has other view filter conditions on it, the following
submenu items are available:

6
DERIVED ELEMENTS
Lesson Description
This lesson describes a functionality provided by MicroStrategy OLAP Services

called Derived Elements. Similar to derived metrics that define metrics on the
fly, derived elements define attribute elements on the fly using the Report
Editor. This lesson also describes how to create a derived element using the
Derived Elements Editor to format each individual derived element.

6 Derived Elements MicroStrategy Developer: Advanced Reporting
Lesson Objective

Explain the derived elements feature, the different types of derived elements,
and how they can be created in Developer.
Describe the purpose and usage of derived elements. (Page 251)
Describe the different types of derived elements. (Page 253)
Describe the various ways to create derived elements. (Page 260)
Explain various derived element formatting options. (Page 273)
Explain how to use derived elements in combination with other reporting

and analysis features. (Page 275)
250 Lesson Objective 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Derived Elements 6
Introduction to Derived Elements

Describe the purpose and usage of derived elements.
A derived element is a grouping of attribute elements on a report, that is

defined by a list, filter, or calculation. These groups provide a new view of
report data for analysis and formatting purposes. Derived elements are
evaluated on the report dataset retrieved from an Intelligent Cube, without
regenerating or re-executing SQL.
You can use derived elements to create these groups on the fly while viewing
a report, rather than defining consolidations or custom groups. For example,
you have a report with Region, Category, and Profit on the template as
displayed on the left hand side in the image below:
Example - Derived Elements
In the report on the right, the Region attribute elements are grouped into
four different derived element groups.
2014 MicroStrategy Inc. Introduction to Derived Elements 251

These elements are defined as:
East CoastCombines the data for the Mid-Atlantic, Northeast, and

Southeast regions
West CoastCombines the data for the Northwest and Southwest regions
South and CentralCombines the data for the South and Central regions
WebDisplays the data for the Web attribute element, which is not
included in any of the derived elements listed above
You can do more than just simple combinations of attribute elements with
derived elements. For example, after you have defined the East Coast derived
element, you can determine the East Coast regions contribution to profit, as
shown below:
Example - Derived Elements
The previous example demonstrates only a fraction of the analysis and

formatting capabilities of derived elements. With derived elements you can
also create custom sort orders for attribute elements, use aggregation
functions such as, Average to combine attribute elements, and perform other
analysis and formatting tasks.
252 Introduction to Derived Elements 2014 MicroStrategy Inc.

Types of Derived Elements

Describe the different types of derived elements.
The different types of derived elements provide a wide range of reporting and
analysis features.
Group Derived Element

A group derived element is a combination of attribute elements grouped into
a single derived element. All attribute element data is added together to form
the metric values for the new Group derived element.
The report shown below includes the derived elements defined using groups
of Region attribute elements:
Example - Group Derived Elements
ToGroups
learn how to create a group derived element see Create Quick
and Calculations starting on page 261.
2014 MicroStrategy Inc. Types of Derived Elements 253

You can quickly create Group derived elements by selecting attribute

elements to include in each derived element. The image below shows how the
Southern Regions derived element is created in the Derived Elements Editor:
Derived Elements Editor
Group derived elements can only combine attribute elements, they

cannot combine derived elements. If you want to create a derived
element that is a combination of other derived elements, you must use
a Calculation derived element.
To create Group derived elements, you can either quickly create derived
elements with right-click options or you can use the Derived Elements Editor
to access the full functionality of derived elements.
254 Types of Derived Elements 2014 MicroStrategy Inc.

Filter Derived Element

A Filter derived element uses a filter qualification to determine the
combination of attribute elements for a derived element.
For example, in a report with Region and Category attributes and a Profit
metric, you can filter the regions on the report into various geographical
groups based on the region names.
The report shown below includes the following derived elements defined with
filters of Region attribute elements:
Southern RegionsReturns attribute elements that begin with the word

South.
Northern RegionsReturns attribute elements that begin with the word

North.
Example - Filter Derived Elements
Central, Mid-Atlantic, and Web are displayed since they are a part of
All Other derived elements.
To create Filter derived elements, you must use the Derived Elements Editor.
There are two methods to create Filter derived elements:
Attribute element list qualification

Attribute form qualification
Using lists of attribute elements. This includes using the In list and Not in
List operators.
In List: A filter qualification using In list returns data for all the
attribute elements you select. An In list filter qualification that returns
all the southern regions is shown below:
Derived Elements Editor - Attribute Element List Qualification
Not in List: A filter qualification using Not in List returns data for all
the attribute elements for an attribute that you do not select.

Using filter qualifications on attribute forms. This enables you to use

various logical and mathematical operators to create filter qualifications
on attribute forms. An attribute form qualification using a Begins with
operator that returns all southern regions is shown below:
Derived Elements Editor - Attribute Form Qualification
Calculation Derived Element

A Calculation derived element uses operators and functions to combine
attribute elements and existing derived elements to define a single derived
element.

For example, in a report with Region and Category attributes and a Profit
metric, you can group the regions on the report into various groups for profit
analysis, as shown below:
Example - Calculation Derived Elements
You can create calculation derived elements by defining expressions with

valid combinations of operators, functions, attribute elements, and derived
elements. An example of a valid expression is shown below:
Derived Elements Editor - Calculation Derived Elements
You can include attribute elements or other derived elements in your

expression by selecting them and dragging them into the expression area.

To create Calculation derived elements, you can either quickly create derived
elements with right-click options or you can use the Derived Elements Editor
to access the full functionality of derived elements.
All Other
The All Other derived element collects all attribute elements that are not
included in derived elements, and includes them on the report as individual
attribute elements by default. This derived element is created automatically
when the first derived element is created for an attribute. For example, recall
the report created with a filter derived element that included Southern
Regions and Northern Regions derived elements as shown below:
Example - All Other Derived Element
Central, Mid-Atlantic, and Web are all attribute elements that are not
included in any derived elements. The All Other derived element gathers
these attribute elements, and includes them on a report as separate attribute
elements.

Creating Derived Elements

Describe the various ways to create derived elements.
Derived elements require static report datasets so that they can be evaluated
without regenerating or re-executing SQL. Due to this requirement, derived
elements can only be created in the following types of reports and
documents:
Intelligent Cube reportsAn Intelligent Cube report is a report that is

connected to and retrieves its dataset from an Intelligent Cube.
Grid Graphs in report services documentsA Grid Graph is a report

that has been added to a section of a report services document. You can
create derived elements in Grid Graphs regardless of whether its dataset
comes from an intelligent cube, directly from a data warehouse, or from
some other type of data storage system.
For more information about derived elements using the Grid Graphs
in a document see the MicroStrategy Report Services: Document
Essentials course.
You can create derived elements with the following methods described in this
section:
Create Quick Groups and CalculationsWhile reviewing the data on a

report or document, you can quickly group attribute elements into
derived elements for further analysis of your data.
Using the Derived Elements EditorUsing the Derived Elements

Editor provides the full set of derived elements functionality when
creating derived elements.
Create and Sharing Stand-alone Derived ElementsYou can create

stand-alone derived elements that can be shared by multiple reports and
Grid Graphs.
260 Creating Derived Elements 2014 MicroStrategy Inc.

Create Quick Groups and Calculations

While reviewing the data on a report or document, you can quickly group
attribute elements into derived elements for further analysis of your data.
You can quickly create derived elements on attribute elements and other
derived elements in reports using right-click options.
Create Quick Derived Elements
You can create the following types of quick Group derived elements:
Quick groupsCreates a simple group of the attribute elements that are

selected.
Quick calculationsCreates a calculation on the attribute elements or

derived elements (or a combination of both) that are selected.
Quick sortsCreates a derived element that sorts the attribute elements

on the report or document in any order you wish. This option is only
available if no derived elements are defined for the attribute on the report
or document.
These quick group options to create derived elements are quick and easy
ways to create derived elements.
These quick group techniques are not available in Graph View mode.
2014 MicroStrategy Inc. Creating Derived Elements 261

Quick Groups
Using right-click options, you can group attribute elements into a single
derived element. The derived element created by this action aggregates and
displays all the data for the attribute elements selected into one group of
data.
For example, you have a report with Region, Category, and Profit displayed.
To get a more summarized view of data, you decide to group the regions into
East, West, and South and Central elements.
Create Quick Groups
Creating a derived element on reports with a quick group:
This procedure assumes a report is connected to an active Intelligent Cube

and you have the Define Derived Elements privilege.
1 In Developer, log in to a project that contains Intelligent Cube reports.
2 Browse to the Intelligent Cube report and run it in Grid View or Grid
Graph View.
3 Press CTRL and select multiple attribute elements for the same attribute
in the grid display of the report.
Do not select derived elements for the attribute, as you cannot create
quick groups on derived elements. To group derived elements, you
must use the Derived Elements Editor.

4 Right-click your selection and select Create Group.
5 In the Defining Group window, type a name for the derived element, and
click OK.
The group is created as a derived element and displayed on the report. You
can modify the derived element using the Derived Elements Editor.
Quick Calculations
You can group attribute elements and derived elements into a single derived
element, using the Create Calculation feature found in the right-click menu.
Quick calculations enable you to view various types of data and analysis on
the same report. You can add, subtract, average, divide, as well as find the
greatest and least values for each metric.
For example, you have a report with Region, Units Sold, Profit and Revenue.
Create Quick Calculations

You can quickly create the average Unit Sold, average Profit and average
Revenue based on their location as shown below:
Example - Quick Calculations
This type of analysis enables you to quickly understand your market and
create targeted promotion at locations that have weaker sales.
To create a derived element with a quick calculation:

and you have the Define derived elements privilege.
1 In Developer, log in to a project that contains reports that are connected

to active Intelligent Cubes.
2 Browse to the Intelligent Cube report and run it in Grid View or Grid
Graph View.
3 Press CTRL and select multiple attribute elements for the same attribute
in the grid display of the report.
4 Right-click your selection, point to Create Calculation and select one of

the following calculations:
Ifallyou selected exactly two attribute elements, you can choose from
of the calculations listed below. However, if you select more
than two attribute elements, Subtract and Divide are not available
calculations as they can only accept two operands. If you want to
create a subtraction or division including more than two attribute
elements, you must use the Derived Elements Editor.
Add
Subtract
Average
Greatest

Least
Divide
5 In the Create Calculation window, type a name for the derived element
and click OK.
The calculation is created as a derived element and displayed on the report.

You can modify the derived element using the Derived Elements Editor.
Using the Derived Elements Editor

While quick groups enable you to quickly create derived elements while
reviewing reports, the Derived Elements Editor provides the full set of
derived elements functionality.
If you create a quick calculation on a report connected to an Intelligent Cube,

the expression can only include one type of function or operand such as +, /,
and Average, as shown in the last group in the image above. From the
Derived Elements Editor, you can create expressions with a valid
combination of different functions. You can create a derived element from
the Derived Elements Editor with an expression of the following form:
(AttributeElement1 + AttributeElement2) / Sum(AllAttributeElements)

Accessing the Derived Elements Editor
With the Derived Elements Editor you can create derived elements with
groups, filters, and calculations. You can access the Derived Elements Editor
from a report in Design View, Grid View, and Grid Graph view in Developer.
You can also access the Derived Elements Editor from outside reports or Grid
Graphs to create stand-alone derived elements. Stand-alone derived
elements can be shared by multiple reports and Grid Graphs.
To access the Derived Elements Editor in reports connected to an active

Intelligent Cube:
1 In Developer, log in to a project that contains reports connected to active

intelligent cubes.
2 Open a report in Grid View, Grid Graph View, or Design View.
3 In the grid display of the report, right-click the attribute for which you
want to create the derived element or modify an existing derived element,
and select Derived Elements.
Create New Derived Element
As discussed earlier, you can create group, filter, and calculation types of
derived elements. In the Derived Elements Editor.

To create a derived element using the Derived Elements Editor:
This procedure assumes a report is connected to an active Intelligent Cube.
1 Log in to a project in Developer.
2 Execute the report connected to an Intelligent Cube in Grid View or Grid

Graph View.
3 To access the Derived Elements Editor, right-click the attribute for which
you want to create new derived elements or modify existing derived
elements and select Derived Elements.
To create a group derived element
4 In the Derived Elements Editor, click the New drop-down list, and select
List.
Two new derived elements are created, a blank group derived

element and an All Other derived element.
5 Select the New List derived element.
6 In the Definition tab, from the left pane, select attribute elements to
include in the group and click > to add your selections to the group
derived element.
This moves the attribute elements to the Selected objects pane.

7 To rename the derived element, click the Change Element drop-down
list and select Rename Derived Element. Type a name for the derived
element.
Derived Elements Editor - Rename Derived Element

To make derived element modifications
8 From the Change Element drop-down list, you can format derived
element headers and values.
9 You can change the order in which the derived elements are displayed on
the report using the up and down arrows.
To complete derived element creation
10 You can continue to create more derived elements, or you can click OK to
close the Derived Elements Editor and return to the report.
11 You can save the derived element as a stand-alone object using the Save
button. This object can be shared by multiple reports and Grid Graphs.
To create a Filter derived element:

and the Derived Elements Editor is open.
1 To create a new filter derived element, click the New drop-down list and
select Filter.
Two new derived elements are created, a blank filter derived element and
an All Other derived element.
2 Select the new Filter derived element.

Create Filter Derived Element

3 In the Definition tab, click Click here to start a new qualification.

Filter Derived Element Definition
The definition pane of the Derived Elements Editor is similar to the

View filter pane.
4 Create the qualification by defining the Field, Operator, and Value fields.
To create a Calculation derived element:

and the Derived Elements Editor is open.
1 To create a new Calculation derived element, click the New drop-down

list and select Calculation.
Two new derived elements are created, a New Calculation derived

element and an All Other derived element.
2 Select the New Calculation derived element.
This displays the available attribute elements in the Definition tab.

3 In the Definition tab, select attribute elements to include in the
calculation expression, and add them to the expression area as shown
below.

You can also use the toolbar above the expression to include operators
and functions, as well as validate or clear the expression.
Calculation Derived Element Definition
Create and Share Stand-Alone Derived Elements

Stand-alone derived elements can connect to matching attributes in reports
connected to Intelligent Cubes. This enables you to share derived elements in
multiple reports.
There are two methods with which you can create stand-alone derived
elements:
Create a derived element from within a report and save it as a stand-alone

object.
Create a stand-alone derived element outside of any report.

After it is created, a stand-alone derived element has the following

functionality:
All available group, calculation, filter, and All Other derived elements are
saved as part of the derived element. You cannot select a subset of the
derived elements, you must save and share the entire collection of derived
elements.
The stand-alone derived element can only be connected to the attribute

that was used to define the derived elements.
The stand-alone derived element itself can be modified, but you cannot
modify it from within a report. Any modifications for the derived element
are applied to the derived element in all of the reports in which it is
shared.
The stand-alone derived element can only be deleted if it is not shared in

any report. A list of reports that include the derived element is returned
when trying to delete the derived element.
To create a stand-alone derived element:
1 In Developer, log in to a project.
2 On the File menu, point to New and select Derived Element.
3 In the Select Attribute window, browse to and select the attribute on

which you want to base your derived element and click Open.
4 In the Derived Elements Editor, you can create list, calculation, or filter
derived elements to define your stand-alone derived elements by selecting
an option in the New drop-down list.
5 After you create the required derived element click Save and Close.
6 In the Save Derived Element As window, type a name and click Save.
The Derived Elements Editor closes and the derived element is saved as a
stand-alone object.

Sharing Derived Elements in Reports
You can share stand-alone derived elements in multiple reports. To share a

stand-alone derived element, you apply the derived element to an attribute in
reports connected to an Intelligent Cube. However, a derived element can
only be connected to the attribute used to define the derived element.
For example, if a derived element is created on the Year attribute, this

derived element can only be applied to the Year attribute in reports. You
cannot apply this derived element to any other attributes such as Quarter,
Month, Category, Customer, and so on.
To apply a stand-alone derived element to an attribute:
This procedure assumes the report, on which you connect the derived
element to an attribute, is connected to an active Intelligent Cube.
1 Log in to a project in Developer.
2 Open the report and run it in Grid View or Grid Graph View.
3 Right-click the attribute on which you want to apply a stand-alone derived

element and select Derived Elements.
Ifreport,
an attribute already has a derived element defined for it in the
applying a stand-alone derived element overwrites the existing
definition.
4 In the Derived Elements Editor, click Link.
5 In the Select Derived Elements window, browse to and select the derived
element to apply to the attribute
6 Click Open.
7 Click OK to save your changes and close the Derived Elements Editor.
You can modify a stand-alone derived element, which updates the derived
element in all reports it is used in. Since these modifications are applied to
multiple reporting objects, you can only update the stand-alone derived
element itself. You cannot update it from a report.
Toderived
modify a stand alone derived element, right-click the stand-alone
element and select Edit.

Derived Element Formatting

Explain various derived element formatting options.
Formatting derived elements highlights important data and correctly reflects

value and character formats. For example, you may require the values of a
derived element to be displayed as a percentage. You can also format
individual attribute elements and metric values associated with individual
attribute elements. Formatting derived elements is only available from the
Derived Elements editor.
For example, consider the report shown below that has derived elements
created from the Region attribute.
Example: Derived Element Formatting
The derived element East Coasts % Contribution uses derived element

formatting to apply a percentage format to the profit values. If you used
metric formatting to apply a percentage format to profit values, the
percentage format applies to all profit values across all elements. Using the
Derived Elements Editor you can apply formatting to different derived
elements headers and values.
2014 MicroStrategy Inc. Derived Element Formatting 273

Formatting derived elements:
1 In the Derived Elements Editor, select a derived element.
2 On the Change Element menu, point to Format and select from the
following options:
ValuesFormatting the values of a derived element applies

formatting to all metric values associated with a derived element.
HeadersFormatting the headers of a derived element applies

formatting to the derived element name.
Formatting Values of Derived Elements
For information on how to use the Format Cells window to format

data, see the Advanced Reporting Guide.
3 After you are finished formatting derived elements headers or values,

click OK.
4 Click OK to save your changes and close the Derived Elements Editor.
274 Derived Element Formatting 2014 MicroStrategy Inc.

Interaction with Other Reporting Features

Explain how to use derived elements in combination with other reporting
and analysis features.
It is important to understand how derived elements are used in combination

with other reporting and analysis features because this can affect the
behavior and results of your reports and documents. This topic explains how
derived elements interact with other MicroStrategy features, such as:
View filters
Derived Metrics
Page-by
Thresholds
Drilling
Prompts
View filters
View filters restrict the amount of data displayed on a report, resulting in a
different view of the data. They can only include qualifications on attributes
and metrics. View filters cannot use derived elements in the view filter
qualification; neither can you select a derived element in a view filter.
Derived elements cannot be selected in a view filter, and therefore, cannot be

a part of a view filter qualification that restricts report results. View filters can
only include qualifications on attributes and metrics.
Since derived elements are associated with attribute elements, any view filter
qualifications that restrict the data for a derived elements associated
attribute elements also restrict data returned for the derived element.
2014 MicroStrategy Inc. Interaction with Other Reporting Features 275

For example, a report includes a group derived element defined to combine

profit values for the Music and Movies elements of the Category attribute.
This report is shown below:
Report with Group Derived Element for Music and Movies
The Music and Movies derived element combines the profit values for the
Music attribute element and Movies attribute element. When you create a
view filter qualification that restricts the report data to the individual Movies
attribute element and Electronics attribute element, the report results
change, as shown below:
View Filter Qualification Restricting Movies and Electronics Attribute
Elements
The Music and Movies derived element is still displayed, but the profit value
has decreased. The view filter has restricted the data to only Movies and
Electronics, so the Music and Movies derived element can only return profit
values for the available Movies attribute element data. When using view
filters and derived elements on the same report, any view filter qualifications
restrict the data available to analyze and format with derived elements.
You may have to clear your derived element, create a view filter on a
category attribute and then recreate your derived element on that
category.
276 Interaction with Other Reporting Features 2014 MicroStrategy Inc.

Derived Metrics
A derived metric is a metric based on metric data that is included in the
report dataset returned from the data warehouse. You can use derived
metrics to perform column math. That is, calculations on metrics included in
the report dataset, without regenerating or re-executing SQL.
Most derived metrics are represented correctly by derived elements.

However, derived metrics that have the same evaluation order requirements
as smart metrics can initially be calculated incorrectly when using derived
elements.
For example, a derived metric that uses a division such as Profit/Revenue is

initially calculated incorrectly when using derived elements. By default
derived metrics are evaluated before derived elements. You must change the
evaluation order to evaluate derived elements before derived metrics to
obtain the correct result set.
Evaluation order is the order in which the analytical engine calculates

the metric values. For more information about changing the
evaluation order see Evaluation Order starting on page 313.
Page-by
You use the page-by feature to group data into subsets. Page-by makes
viewing a report easier than scrolling through long lists of data. Attributes are
one of the most common objects included in the page-by area of a report.
When an attribute is included in the page-by area, you can select the attribute
element for which you want to view data.

If you create derived elements for an attribute included in the page-by area,
the derived elements are available for selection to display their associated
data. For example, the report below shows East Coast, West Coast, and
Central and South derived elements based on the Region attribute:
Derived Elements based on Region Attribute
If Region is moved to the page-by area, the derived elements are available for
selection from the page-by field, along with the Web attribute element, as
shown below:
Derived Elements in Page-by Field
You must move attributes from the page-by area to the grid of the
report to create or modify derived elements for the attribute.

Thresholds
Thresholds highlight particular data in a report by displaying special cell
formats, symbols, images, or replacement text.
They are created using conditions on attributes or metrics. Thresholds

created using conditions on attributes are not displayed for derived element
data. However, the more commonly used thresholds created using conditions
on metrics are applied to derived element data.
For example, you have a report with East Coast, West Coast, and Central and
South derived elements defined, based on the Region attribute and the Profit
metric.
You create a threshold on the Profit metric, as shown in the following image:
Metric Threshold
You do not have the option to select the Region attribute since that
includes derived elements. You cannot display this type of
threshold formatting for derived elements. However, you can still
apply formatting to the data for each derived element.

When the report is executed, the Profit > $400,000 threshold is displayed, as
shown below:
Profit > $400,000 Results
Drilling
Drilling enables you to view displayed report data at levels other than that
returned in the original grid or graph report.
When you drill on attribute elements in a report, the resulting report restricts
the results to data only for the attribute elements used when drilling. For
example, if you drill down from the Year 2012 attribute element to Quarter,
the resulting report only includes Quarters that are within 2012. The same
logic applies to drilling on derived elements. Drilling on a derived element
restricts the resulting report to data only for the attribute elements used to
define the derived element.

For example, the report below shows East Coast, West Coast, and Central and
South derived elements based on the Region attribute:
Derived Elements based on Region Attribute

If you drill down from the East Coast derived element to Call Center, the
resulting report shown below returns data for Call Centers within the East
Coast regions (Northeast, Mid-Atlantic, and Southeast):
Drill Down from East Coast Derived Element to Call Center
Prompts
There is no interaction between prompts and derived elements. Intelligent
Cube reports retrieve their data from the Intelligent Cube, so because derived
elements are not part of the Intelligent Cube, they are not available during
object prompts.


Lesson Summary
Derived elements are grouping of attribute elements on a report that are
created on the fly.
Derived elements created within an intelligent cube report are local to

that report.
Group, filter, calculation and All Other derived elements are examples of
the different types of derived elements.
A group derived element is a combination of attribute elements into a

single derived element.
A filter derived element uses a filter qualification to determine the

combination of attribute elements for a derived element.
A calculation derived element uses operators and functions to combine

attribute elements and derived elements into calculations that define a
singe derived element.
The All Other derived element collects all attribute elements that are not
included in derived elements, and includes them as individual attribute
elements by default.
Derived elements can only be created in Intelligent Cube reports and Grid
Graphs in Report Services documents.
When formatting derived elements you can apply formatting to individual

attribute elements and the metric values associated with individual
attribute elements.
Derived elements are associated with attribute elements so any view filter
qualifications that restrict the data for a derived elements associated
attribute elements also restricts data returned for the derived element.
If you create derived elements for an attribute included in the page-by

area of your report, the derived elements are available for selection to
display their associated data.


MicroStrategy Developer: Advanced Reporting Exercise: Derived Elements 6

Exercise: Derived Elements
All of theses exercises should be completed in the MicroStrategy Tutorial
project in the three-tier project source.
Browse to My Personal Objects/My Reports folder. Create a new folder called

Derived Elements Exercises. For this lesson, you will create all of your
exercises in this folder.
Create Quick Calculations
Overview
The purpose of this exercise is to demonstrate how to create a simple derived

element by creating calculations of selected attribute elements. In this
exercise, you will create an Intelligent Cube report based off an Intelligent
Cube, then create the specified derived elements.
Your Intelligent Cube should contain the following objects:
Attributes: Category, Subcategory and Item
Metrics: Revenue, Profit, Cost, Unit Profit, Unit Cost, Unit Price and
Units Sold
Your Intelligent Cube report should contain the following objects:
Attributes: Subcategory and Item
Metrics: Unit Cost, Unit Price, Unit Profit, and Units Sold
All metrics should be in the currency format with two decimal

places.
The Category attribute in your Intelligent Cube report should be in the

Page-by section.
2014 MicroStrategy Inc. Exercise: Derived Elements 285

6 Exercise: Derived Elements MicroStrategy Developer: Advanced Reporting
Your quick calculations should be based off the Movies category, and
provide an Average for the following:
Action subcategory with unit cost between $7.00 and $10.00
Action subcategory with unit cost between $10.01 and $14.00
Action subcategory with unit cost above $14.01
Your final Intelligent Cube report results should display all three of the
derived elements noted above, as shown in the image below:
Final Quick Derived Elements for Calculations - Average

Cube.
Category, Subcategory and Item attributes and add them to the Report
objects window.
4 Add the Revenue, Profit, Cost, Unit Profit, Unit Cost, Unit Price and
Units Sold metrics to the Report Objects window.
286 Exercise: Derived Elements 2014 MicroStrategy Inc.

6 A pop up window displays requesting to save the report before executing

it. Click Yes and name the Intelligent Cube Quick Derived Element for
Calculation.
7 Click Save.
9 Right-click Quick Derived Element for Calculation, and select Create

Report.
10 Add the Subcategory and Item attributes, and the Unit Cost, Unit Price,
Unit Profit and Units Sold metrics to the template.
11 Right-click the Unit Cost metric, point to Formatting, then All Metrics,
and select Values.
12 On the Number tab, select Currency and set to 2 decimal places. Click
OK.
13 Right-click the Category attribute in the Report objects window, and

select Add to Page-by to move the attribute to the page-by area.
15 Select Movies in the Category Page-by drop-down list.
16 For the Action subcategory, with Ctrl key pressed, select the items with
unit cost between $7.00 and $10.00.

17 Right-click the selected elements, point to Create Calculation, and select

Average, as shown below:
Create Calculation Option - Average
18 Name the group Avg Unit Cost between $7.00 and $10.00 and click OK.
19 Repeat the steps above to create two additional groups named Avg Unit
Cost between $10.01 and $14.00 and Avg Unit Cost above $14.01.
Your final Intelligent Cube report, which includes the derived elements,
should look like the image below (only a part of the report is shown):
Final Quick Derived Elements for Calculations - Average

20 Click Save and Close, and name the Intelligent Cube report Quick
Calculation - Average.
Creating Derived Elements Using The Derived Elements Editor
Overview
The purpose of this exercise is to create various types of derived elements

using the Derived Elements Editor. In this exercise, you will create an
Intelligent Cube for reporting needs. After you create your Intelligent Cube
report, you will create various types of derived elements through the Derived
Elements Editor. The requirements for this exercise are listed below:
Your Intelligent Cube should contain the following objects:
Attributes: Year, Day, Category, Subcategory and Distribution Center
Metrics: Revenue, Cost and Profit
Your Intelligent Cube report should contain the following objects:
Attributes: Distribution Center
Metrics: Profit
Assume your manager is in charge of the Seattle, San Francisco, and San
Diego distribution centers, and wants to compare her performance with
respect to other regions.
Create the following derived elements through the Derived Elements

Editor:
My Distribution Centers: Seattle, San Francisco, and San Diego
East Managers Distribution Centers: Washington DC, Miami, New

York, and Atlanta
Remaining Centers: all other distribution centers

Your Intelligent Cube report should look like the following image:
Derived Elements for Distribution Centers
Your manager now wants to compare her profit with the other centers.
Create the following additional derived elements through the Derived

Elements Editor:
Ratio of My Profit to East Managers: My Distribution Centers/East

Managers Distribution Centers
Ratio of My Profit to Remaining Centers: My Distribution

Centers/Remaining Centers
Format the calculations as Fixed with two decimal places.
Your Intelligent Cube report should now look like the following image:
Results for Additional Derived Elements - Ratios
Finally, your manager wants to change the display of the derived elements
on the Intelligent Cube report. Change the order so the ratios display at
the bottom and profits for each derived element display at the top.

Creating Group Derived Elements

Cube.
Year, Day, Category, Subcategory and Distribution Center attributes
and add them to the Report objects window.
4 Add the Revenue, Cost and Profit metrics to the Report Objects window.
6 A pop up window displays requesting to save the report before executing

it. Click Yes and name the Intelligent Cube Using Derived Elements
Editor.
7 Click Save.
9 Right-click the Using Derived Elements Editor Intelligent Cube, and

select Create Report.
10 Add the Distribution Center attribute and the Profit metric to the
template.
12 Right-click the Distribution Center attribute, and select Derived

Elements.

13 In the Derived Elements window, on the New menu, select List, as shown
below:
Derived Elements Editor - List
The definition window changes to the following:


14 Add distribution centers Seattle, San Francisco and San Diego to the
Selected objects list using the > button.
Desired Attribute Elements Selected
15 On the Change Element menu, select Rename Derived Element, and

name the group My Distribution Centers, as shown below:
New Derived Element Name
16 On the New menu, select List to create another new derived elements
group.
All the elements will be displayed again in the Definition tab.

17 Select the Unused Elements check box (if not already selected) since you
want to create a group from the remaining elements.

After it is selected, you will only see those elements that are not part of
any other derived element.
Unused Elements Check Box
18 Select Washington DC, Miami, New York and Atlanta, and use the right
arrow to move them to the Selected objects pane.

name this group East Manager's Distribution Centers.
20 Repeat the steps above to create another derived element which contains
the remaining Distribution Centers that are not part of the above two
groups. Name it Remaining Centers.
21 Click OK.

The report should look like the image below:

New Derived Elements for Distribution Centers
Creating Calculation Derived Element to do Additional Analysis
Your manager wants to compare her profit with the other centers. The
calculation derived element enables you to take the analysis a step further.
22 Right-click the Distribution Center attribute and select Derived

Elements.
In the summary window, you see the derived elements and its
expressions, as shown below:
Summary of all Derived Elements Created
23 On the New menu, select Calculation.

Ina drop-down
the definition window for calculation derived element, you will see
list. You can choose from the attribute elements or the
derived elements to perform mathematical operations, as show below:
Calculation Options
24 Select Derived Element from the drop-down list to see the available
derived elements, as shown below:
Calculation Groups Selections

25 Double-click My Distribution Centers to add it to the expression pane.

Select the division operator (/), and double-click East Manager's
Distribution Centers. The image below illustrates your calculation:
26 Click Validate to make sure the expression is correct.
To see the list of available functions, click f(x).

name it Ratio of My Profit to East Manager's.
28 Follow the steps above to create another calculation derived element

defined as My Distribution Centers/Remaining Centers.
29 Name the calculation derived element Ratio of My Profit to Remaining

Centers.
30 Select the derived element Ratio of My Profit to East Manager's, and on

the Change Element menu, point to Format, and select Values.
31 On the Numbers tab, select Fixed with 2 decimal places.
32 Click OK.
33 Select the derived element Ratio of My Profit to Remaining Centers,

and on the Change Element menu, point to Format, and select Values.
34 On the Numbers tab, select Fixed with 2 decimal places.
35 Click OK.

The derived element summary window should look like the image below:
Derived Element Summary
The display order of your derived elements can be different than the
image above depending on where you added the new derived
elements.
36 Click OK.
The report results should like the following image:

Calculation Result Set
Now your manager wants to see the ratios at the bottom and profits of each
custom derived element at the top of the report.
37 Right-click the derived elements in your report, and select Derived

Elements.
38 In the editor, select the derived element Ratio of My Profit to Remaining

Centers, and on the toolbar, click the Move Down arrow to move the
derived element to the bottom of the group list.
39 Repeat the previous step to move the other derived element, Ratio of My
Profit to East Manager's, to the bottom of the group list.

The list should now look like the image below:

New Sort Order for Ratios and Profits
40 Click OK.
Your results should look as follows:

Result Set for Ratios and Profits
41 Click Save and Close and name the report Regional Analysis.


7
REPORT DATA OPTIONS
Lesson Description
This lesson teaches you how to use Developers Report Data Options to further
control the behavior of reports.

7 Report Data Options MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Define all of the Report Data Options and understand how to enable them.
List the various settings in Report Data Options. (Page 303)
Describe and set report limits, change the join type of a metric on a report,
change the processing order of objects on a report, and set subtotal options
for custom groups and consolidations. (Page 305)
Change various display settings for a specific report. These settings include
object aliases, custom group display, specific syntax or text for null values,
report display and general drilling options at the report level. (Page 322)
Enable, disable, or limit the amount of drilling that can be performed on a

report. Specify whether or not to retain page-by selections when saving a
report. Specify the MicroStrategy Mobile data view. (Page 331)

MicroStrategy Developer: Advanced Reporting Report Data Options 7
Overview of Report Data Options

List the various settings in Report Data Options.
There are several options that can be set at the report-level, using Report
Data Options. The Report Data Options window is grouped into three
categories:
Calculations
Report LimitEnables you to set limits on any metrics to apply to a

report
Metric Join TypeEnables you to set the join type of each metric that
is present on the report
Attribute Join TypeEnables you to control how lookup tables are

joined with intermediate tables during report execution
Evaluation OrderEnables you to modify the evaluation order of

metrics, consolidations, report limits, and subtotals when present on a
report
SubtotalsEnables you to add a subtotal to custom groups and

consolidations present on a report
Display
AliasEnables you to specify an alias for each template object present

on a report
Object DisplayEnables you to specify custom group display settings
Null ValuesEnables you to control the display of null values present

on a report
Grid Graph ModeEnables you to specify the display settings for a

report when viewed in Grid Graph mode in Developer
2014 MicroStrategy Inc. Overview of Report Data Options 303

General
DrillingEnables you to control drill behavior
AdvancedEnables you to specify whether or not to retain page-by

selections and control other advanced, report-level settings.
MicroStrategy Mobile Data ViewEnables you to control the design

options and report functionality for reports displayed in
MicroStrategy Mobile.
All of the above options apply to the report on which you set them. In other
words, you set Report Data Options on a report-by-report basis.
304 Overview of Report Data Options 2014 MicroStrategy Inc.

Calculations

Describe and set report limits, change the join type of a metric on a report,
change the processing order of objects on a report, and set subtotal options
for custom groups and consolidations.
Report Limit
The Report Editor enables you to apply limits to metrics on a report. Report
limits are maximum and minimum values for a metric that you specify at the
report-level. They determine the rows of a data set that are displayed in the
final report results.
A report limit specifies metric criteria that is used to restrict the final result
set after the report metrics are calculated. The Report Editor enables you to
set limits with any metric, regardless of whether or not that metric displays
on the report.
For example, on a report containing Call Centers and Profit, you might apply
a report limit of Revenue Rank Bottom 10%. This constrains the list of Call
Centers in the final report results to only those that had Revenue in the
bottom 10% of all Call Centers. Although the Revenue metric is used in the
report limit, it does not appear on the report template nor is it in the Report
Objects window.
2014 MicroStrategy Inc. Calculations 305

You can define report limits in terms of Value, Rank, and Percent as
described in the table below:
Function Example Operators
Value Include only items greater than a specified Greater than, Less than
value for the metric Exactly
Between
Not between
Different from
Greater than or equal to
Less than or equal to
Is null, Is not null
Rank Include only the top 10 items based on the Top

metric values Bottom
Between
Exclude top
Exclude bottom
Not between
Exactly
Different from
Percent Include only those items whose metric value is Top

in the top 10 percent Bottom
Between
Exclude top
Exclude bottom
Not between
Exactly
Different from
Report limits are similar to metric qualifications but are defined on the
template rather than the report filter. Thus, unlike metric qualifications,
which you can save as separate filter objects, report limits are saved locally to
a particular report and are not reusable across reports. Also, unlike metric
qualifications, report limits do not allow you to control the output level at
which the limit is evaluated. A report limit always evaluates at the Report
Level (the lowest attribute levels on the template). In general, report limits
can produce the same results as metric qualifications but with fewer passes of
SQL.
Both metric qualification report filters and report limits can be used
on the same report because they are applied at different stages of the
report execution process. A report filter affects the data used to
calculate metrics, whereas a report limit does not affect how the
metrics are calculated. Report limits are instead applied at the level of
the report after the metrics have been calculated.
306 Calculations 2014 MicroStrategy Inc.

To set a report limit:
1 In the Report Editor, on the Data menu, select Report Data Options.
2 In the Report Data Options window, expand Calculations and select

Report Limit.
3 In the right-hand window, click Modify.
4 In the Report Limit Editor, double-click the Limit Definition area.
5 In the Report Limit Qualification window, you can add a metric to the
Metric box in one of the following ways:
Type the metrics name: In the Metric box, type the desired metric
name and click OK.
Browse for the metric: Click the Browse button. In the Open window,
locate and select the desired metric. Click OK.
Drag and drop a metric: In the Object Browser, locate the metric and
drag it into the Metric box.
6 For Function, select either Metric Value, Rank, or Percent.
7 For Operator, select an operator.
8 For Value, select Value, Simple Prompt, Metric, or Custom. Complete

the expression or the simple prompt definition and click OK.
10 In the Report Data Options window, click OK.
Metric Join Type

Metric join types come into play when reports display more than one metric,
and you must decide how to join information across the different metrics.
There are two types of metric joins:

An inner join includes only records that have data present for all metrics
in the report results.
An outer join shows all records for that metric, regardless of whether data
exists for other metrics in the report results.
By default, Developer uses an inner join between metrics on a report.

Occasionally, you may want to display all of the records in the report results,
whether or not they have data for all of the metrics. An outer join helps you
achieve this. Below are some tabular examples of different metric join types.
In this first example, both the Sales and Inventory metrics are defined with
an Outer join. Notice the North and West Regions have no data present for a
metric. Even so, both records appear in the report results because both
metrics have been defined with an outer join.
Region Sales Inventory
North 100
South 200 500
East 100 400
West 300
In this second example, both the Sales and Inventory metrics are defined
with an Inner join. Only records that have data across both metrics are
present in the report. Since the North and West Regions do not meet this
condition, they are omitted. The North Region does not appear because it
does not have data for Inventory. The West Region does not appear because it
does not have data for Sales.
South 200 500
East 100 400

In this last example, the Sales metric is defined with an Outer join while the
Inventory metric is defined with an Inner join. The North region displays on
the report because it has data for Sales, even though it does not have data for
Inventory. Alternately, the West region does not appear on the report
because it does not have data for Sales.
North 100
South 200 500
East 100 400
In summary, outer joins display all the records that have a value for that
metric. MicroStrategy enables you to specify metric join types for each metric
on a report.
To set the metric join type at the report-level:

Metric Join Type.

3 Under Join Type, select the join type for each metric.
Report Data Options Editor - Metric Join Type
Depending on your database platform and default configuration

settings, you may need to change your VLDB settings for Metric
Join Type as well.
4 Click OK.
Setting the metric join type using Report Data Options affects only
the results for the report being modified. To globally set the join
type of a metric, specify it at the metric-level with the Metric
Editor. A metric join type that you set globally affects the results of
all reports using that metric. However, you can still override the
global setting at the report-level with Report Data Options.

To set the metric join type at the metric-level:
1 In the Metric Editor, on the Tools menu, select Metric Join Type.
2 In the Metric Join Type window, clear Use default inherited value -
(Default settings level).
3 Select the metric join type.
4 Click OK
Attribute Join Type

The Attribute Join Type setting enables you to force an outer join against
lookup tables of select attributes.
Attribute Join Type Options
The options are as follows:
Preserve common elements of lookup and final pass result tableKeep

only the attribute elements that exist in both the lookup and fact tables
Preserve lookup table elements joined to final pass result table based on
fact table keysKeep only the attribute elements that are joined to the
fact table based on fact table keys
Preserve lookup table elemetns joined to final pass result table based on
template attributes without filterKeep all attribute elements and ignore
all related filtering conditions

Preserve lookup table elements to final pass result table based on

template attributes with filterKeep all attribute elements and apply all
related filtering conditions.
As an example, if you want the year 2013 to appear in a report (even though
there is no sales data for this year in the data warehouse), you must force the
SQL Engine to perform an outer join against the lookup table, LU_YEAR.
Doing so brings back all of the years in the lookup table, whether or not there
is data for these years in the sales fact table.
Changing the setting to Preserve lookup table elements joined to final

pass result table based on template attributes without filter (produces
same result set as the with filter option because there is no filter criteria in
the below report) brings back the following results:
Example - Attribute Join Type
The following example assumes that there are 4 years stored in the
LU_YEAR lookup table, 2010-2013. It also assumes that only the
years 2010, 2011 and 2012 have data in the fact table.
The example above demonstrates how the Attribute Join Type setting affects
the joins made between a lookup table and a fact table.
Intables,
the case where you need to force an outer join between two lookup
you can use the logical view functionality that is available in
MicroStrategy. For more information on logical views, refer to the
MicroStrategy Advanced Data Warehousing course.
Ifsetting
you are using Microsoft Access, you also need to change another
for the join type to take effect. On the Data menu, select VLDB
Properties. In the VLDB Properties window, in the Joins folder, clear
the Use default inherited value check box and click Join 92. Click
Save and Close.

Evaluation Order
The order in which data is calculated for a report can affect the final result
set. By using evaluation order settings, you can control the order in which
consolidations, certain metrics, report limits, and subtotals are calculated
and resolved for a given report.
Evaluation order determines the order in which the Analytical Engine

performs different kinds of calculations. The following simple example
illustrates how evaluation order can influence the result set of a report.
States Consolidation Revenue Dollar Cost Revenue/Cost
New York 15 10 1.5
Virginia 20 15 1.33
NY & Virginia 35 25 ?
In the above example two calculations are resolved by the Analytical Engine:
the States Consolidation and the smart compound metric, Revenue/Cost. If
the Analytical Engine calculates the consolidation before the compound
metric, the empty cells value is based on the last row of the consolidation,
35/25=1.4. If, however, the Analytical Engine calculates the smart
compound metric first, the empty cells value is based on the last column of
the result set, 1.5+1.33=2.83.
In general, calculations on MicroStrategy reports are achieved in one of two

possible ways:
Executing SQL against the data warehouse
With the Analytical Engine
Many types of calculations can be handled by the Analytical Engine:
Metric qualification
Smart compound metrics
AMetric
smart compound metric is one that has the Allow Smart
setting enabled, which is on the Subtotals/Aggregation tab
of the Metric Editor.) The Analytical Engine always calculates
smart compound metrics. Only a metric with mathematical
operators in its formula can have this setting enabled.

Consolidations
Report limits
Subtotals
Custom groups
Intelligent Cube operations (such as derived metrics, view filters, and

dynamic aggregation)
Derived elements
Sorting
Thresholds
Page-by
Crosstabbing
The MicroStrategy Engine makes a plan to decide the evaluation order of all
Analytical Engine calculations. Of the calculation types listed above, metric
qualification is always performed first. Page-by, sorting, and cross-tabbing
are always done during the last stage of the report execution process. For the
other types of calculations, the Analytical Engine enables you to specify the
evaluation order.
Currently, the MicroStrategy Engine supports user-defined evaluation order

for four types of calculations:
Smart compound metrics
Consolidations
Report limits
Subtotals
The above calculations can be defined in any sequence. However, the default
calculation order is as follows:
1 Smart compound metrics
2 Consolidations
3 Report limits
4 Subtotals

To set the evaluation order:

Evaluation Order.
Set Evaluation Order
3 Clear the Use default evaluation order checkbox.
4 Under Data Set evaluation order, next to the appropriate object, select a
number indicating the order of evaluation. A 1 causes the relevant object
to be evaluated first, a 2 second, and so on.
5 Click OK.

Example: Reports with Multiple Consolidations
A report can contain one or more consolidations. If there is more than one
consolidation on a report, you must pay attention to the evaluation order.
This is important when one of the consolidations involves division (or
multiplication) and the other involves addition (or subtraction) in the
definitions of the consolidation elements.
The default calculation order among consolidations is determined by the

positions of the consolidations on the template. First, consolidations on the
row axis are calculated, from left to right (outside to inside). Then,
consolidations on the column axis are calculated, from top to bottom (outside
to inside).
For example, suppose you have a report that has a Revenue metric and two
consolidations. One is the Seasons consolidation that was described earlier,
and the other is a Years consolidation that contains three consolidation
elements: 2011, 2012, and 2011/2012. Depending on the evaluation order of
the consolidations, you may get different result sets.
The row for Spring 2010/2011 can be either calculated as (April 2011 +
May 2011 + June 2011) / (April 2012 + May 2012 + June
2012) or (April 2011 / April 2012) + (May 2011 / May 2012)
+ (June 2011/June 2012). You need to specify the order in which you
want Spring 2011/2012 to be calculated.

The following example shows two reports where the only difference is the
evaluation order of the consolidations. The first report evaluates the Seasons
consolidation first. The second report evaluates the Years consolidation first.
Evaluation Order ExampleEvaluate Seasons Consolidation First
Evaluation Order ExampleEvaluate Years Consolidation First

View Evaluation Order
If you are using MicroStrategy OLAP Services and you add view filters,
derived elements or derived metrics to a report, you can also control the
evaluation order for these OLAP Services objects. Since all report view
manipulations that do not cause new SQL to be generated are performed
after the report result is retrieved from the data warehouse, they need their
own view evaluation order.
The view evaluation order is used to control the evaluation order of subtotals,
derived metrics, and smart metrics. All of these calculations can potentially
change if the report view is manipulated.
Report Data Options EditorView Evaluation Order
The view evaluation order is only enabled if you perform report view
manipulations. The report view manipulations are as follows:
There are objects in the Report Objects Window that are not on the report
view.
A view filter is being used.

A derived metric is on the report.
Analytical Engine Calculation Plan
You can view the Analytical Engine calculation plan in the SQL View of a
report. For example, the following report contains several objects that are
calculated by the Analytical Engine: a consolidation, a smart metric, and a
subtotal. You can switch from Grid View to SQL View to see the order in
which the Analytical Engine calculates each object.
Grid View
SQL View
The Analytical engine calculation steps section displays all operations

performed by the Analytical Engine after data is returned from the data
warehouse. The calculation plan in SQL View can include any of the following
Analytical Engine operations:
Report limits
Metric calculations
Subtotals
Consolidations
Intelligent Cube operations (such as derived metrics, view filters, dynamic

aggregation)
Thresholds

Crosstabbing
By viewing the Analytical Engines calculation plan for any given report, you
can gain a clearer understanding of the order of operations for the report and,
if necessary, change the evaluation order to obtain the desired result set.
Subtotals
In Report Data Options, the Subtotals are used for custom groups and
consolidations. If custom groups or consolidations are on the report, this
window provides you with the option to display the subtotals of these objects:
Default, Yes, and No. If Default is selected, the object inherits the Subtotal
property as it is set in the object itself through either the Custom Group or
Consolidation Editor.
To set the Subtotal option at the custom group or consolidation-level:
1 Open the Custom Group Editor or the Consolidation Editor.
2 Do one of the following:
In the Custom Group Editor, on the Custom Group menu, select

Options.
In the Options window, select or clear the Enable Subtotals Display

check box and click OK.
OR
In the Consolidation Editor, on the Elements menu, select Subtotals

followed by Enabled or Disabled.
3 In the Custom Group or Consolidation Editor, click Save and Close.

To set the Subtotal option at the report-level:

Subtotals.
Report Data Options Editor - Subtotals
3 Under Allow Subtotals for the object, choose the appropriate value:
Default, Yes, or No.
4 Click OK.
For consolidation subtotals to produce the best results, you may also
need to change the Subtotals over Consolidations Compatibility VLDB
property in the Analytical Engine folder.

Display

Change various display settings for a specific report. These settings include
object aliases, custom group display, specific syntax or text for null values,
report display and general drilling options at the report level.
Alias
In certain cases, you may want to change the name of an object for report
display purposes. By specifying an alias for an object, you can have it display
under a different name without actually changing the objects name in the
project.
The ability to create object aliases on a report-by-report basis enables you to

create reports to meet the needs of a diverse end-user community. Naming
conventions of business measurements may differ from one department to
another and among organizations sharing data. For example, you may create
a metric named Sales. The Sales department refers to this measurement as
Revenue, while Marketing refers to it as Sales. Aliases provide greater
flexibility for naming conventions in situations such as this one.
In addition, you may want to name metrics from a more technical

perspective. For example, you may name a level metric as Revenue
(Level=Call Center, Filtering=Ignore, Grouping=None) to easily identify its
level properties. However, when you place it on a report, you may want it to
display as All Revenue to make it more understandable to the end-user
community, as seen below:
Example - Alias
Aliases can be given to many objects on a report including:
322 Display 2014 MicroStrategy Inc.

Attributes
Metrics
Consolidations
Custom Groups
To specify an alias on a report:
1 In the Report Editor, 0n the Data menu, select Report Data Options.
2 In the Report Data Options window, expand Display and select Alias.
Report Data Options Editor - Alias
3 Under Alias, enter the alias in the text box next to the object.
4 Click OK.
2014 MicroStrategy Inc. Display 323

Object Display
The Object Display setting is used to further customize the display of custom
groups on a report. You can use the Object Display option to change the view
of the custom group to either flat or hierarchical. The following is an example
of a flat custom group display. Look closely at the display of the Top 10
Customers and Bottom 5 Items.
Custom Group Flat Display
With a flat display, there is no clear distinction between the custom group
header and the elements within it. Even if the custom group header is given a
name to distinguish it from its elements, it may be difficult to discern in large
report results.

The report below uses a hierarchical display:

Custom Group Hierarchical Display
To change the display of custom groups:
1 Open a report with a custom group in with the Report Editor.

3 In the Report Data Options window, expand Display and select Object
Display.
Report Data Options EditorObject Display
4 Under Display, select the desired display for the custom group object.
5 Click OK.
You can also specify custom group display settings in the Custom
Group Editor.
Null Values
A null value is an unknown value. Null values can result from a number of
reasons as discussed below.
Nulls Found in the Data Warehouse or As a Result of

Crosstabbing.
Often, null values can be found in the data warehouse. For example, if a
customer chooses to withhold personal information, such as birth date, the
value for that customer's birth date would appear as null in a table in the data
warehouse.

Nulls can also be a product of the calculations and crosstabbing that are
performed on a report. For example, on a report that contains no data for a
certain field, you can pivot attributes on the template such that the
cross-tabbing action produces a null value in the report results.
Rather than leave the null values as blank cells, you can have them display
with text or numbers. Often, you might want to display a zero or the word
NULL instead of a blank cell.
The value that replaces the null only replaces it in the report results. In the
case of a data warehouse null, it does not change the actual value in the data
warehouse table. It is used only for calculations in the report SQL, or it is
used for display in the final report results.
Sorting With Nulls
If a null value is returned, how is it sorted? Since a null is an unknown value,

there is no truly correct or incorrect position for nulls on a report that is
sorted. By default, the null is treated as a zero for sorting purposes. To change
the value of nulls for sorting purposes, select the Set the value to be used in
place of empty values when the report data is sorted check box and enter
a value.
Aggregation Null Values
There are cases when dynamic aggregation performed in the Intelligent Cube
results in null values on a report. The example below shows that Average
Revenue dynamically aggregates from Call Center to Region, returning null
values in the report view.
ExampleNull Values Display

By default, the null values for such metrics are replaced by -- on the report.
You can change this default display via the Aggregation Null Values setting in
Report Data Options.
More information on how to change the dynamic aggregation function, see

Dynamic Aggregation starting on page 405.

To change the display of dynamic aggregation null values:
2 In the Report Data Options window, expand Display and select Null
Values.
Report Data Options EditorNull Values
3 Under Aggregation null values, clear the Use default check box.
4 For Set the value to be displayed in the reports when the metric
value cannot be calculated at the desired level, enter a value.
5 Click OK.

Toreports
change the display of dynamic aggregation null values for all
in a project, modify the null display options in the Project
Configuration Editor. You can learn more about the Project
Configuration Editor in the MicroStrategy Administration:
Configuration and Security course.
Missing Object Display
This setting pertains to Freeform SQL reports that may, under certain
circumstances, reference objects that are not available. Freeform SQL reports
are beyond the scope of this course. For more information on Freeform SQL
reports, refer to the MicroStrategy Freeform SQL Essentials course.
Grid Graph Mode

The Grid Graph Mode settings in Report Data Options enable you to specify
the display settings for a report when you view it in Developer in Grid Graph
View.
Grid Position setting: Affects the alignment of the grid on the report page
Grid Percentage and Minimum Grid Percentage settings: Controls the

amount of space that the Grid View of the report occupies in the report
window.
Report Data Options EditorGrid Graph Mode

General Options

Enable, disable, or limit the amount of drilling that can be performed on a
report. Specify whether or not to retain page-by selections when saving a
report. Specify the MicroStrategy Mobile data view.
Drilling
Drilling enables users to view data at levels either higher or lower than those
shown on a report. To review drilling concepts, refer to the Drill Maps lesson
in this course see What is Drilling? starting on page 353, or the Report
Manipulations lesson in the MicroStrategy Developer: Reporting Essentials
course.
On the Drilling tab of Report Data Options, you can:
Enable drilling for the report
Specify whether drilling is enabled in all directions or only down
Determine whether or not users will see the attribute from which they
drilled in the drilled report. For example, if you drill from State to City
and you keep the parent, State remains on the report along with City. The
options are
DefaultInherits the setting from the drill path
YesKeeps the parent while drilling
NoRemoves the parent while drilling
The Keep parent option can be set either in the Report Data
Options window or the Drill Map Editor. Setting the Keep parent
option in the Report Data Options window enables you to specify
that parent objects are automatically kept whenever a user drills on
the report.
2014 MicroStrategy Inc. General Options 331

Keep Thresholds while drilling: Retains thresholds from the original

report on the drilled report. For example, a threshold is set to display
Revenue values greater than $1 million in bold. If you drill from Store to
Employee with this option selected, any employee Revenue over $1
million displays in bold. The options are:
DefaultInherits the setting from the drill path
YesKeeps thresholds while drilling
NoRemoves thresholds while drilling
The Keep threshold setting can be set in either the Report Data
Options window or the Drill Map Editor.
Inherit subtotals from parent: Retains the across-level subtotals when

drilling. For example, a subtotal is applied across the Product hierarchy
on a report. If you drill from Category to Subcategory when this option is
selected, a Subcategory subtotal is still displayed in the drilled-to report.
If this option is not selected, a subtotal is not calculated across
Subcategory. The options are:
Defaulttakes the value set in the drill path.
Yeskeeps across-level subtotals while drilling.
Noprevents across-level subtotals from being displayed while

drilling.
Add the current page-by element as part of the filter when drilling from:
Adds the current page-by element to the filter when drilling. The options
are:
Any page-by fieldThe page-by element is added to the filter only

when you drill from a page-by field.
Any other part of the reportThe page-by element is added to the

filter when you drill from anywhere on the report except a page-by
field.
Drilling filter options: Enables you to select whether to keep the child
elements in the filter when drilling. This setting affects the SQL generated
for the drilled report, not the report results. The options are:
332 General Options 2014 MicroStrategy Inc.

Include in the filter only elements of attributes positioned to the left of

or above the selected attribute elements(s)When you drill on a
report, only the elements to the left of or above the elements on which
you drilled are included in the SQL for the new, drilled report. For
example, a report contains Year, Country, Category, and Revenue. If
you select a single country and drill to Region, the rows for the Year
and Categories are highlighted. With this setting enabled, only Year
and Country are included in the SQL for the new, drilled report, not
Category since it is to the right of Country.
Include in the filter all highlighted attribute elementsWhen you drill

on a report, all of the selected elements are included in the SQL for the
new, drilled report. In the example above, Year, Country, and Category
are included in the SQL for the new, drilled report.
Drill Attributes Join TypeOpens the Drill Attributes Join Type window,
which enables you to set the join type for the attributes that appear in
new, drilled reports.
To set the drilling options on a report:

2 In the Report Data Options window, expand General and select Drilling.
Report Data Options Editor - Drilling
3 Clear the Enable report drilling check box if you want to disable drilling
for the report (the check box is selected by default).
4 Select the appropriate option to specify whether you can drill anywhere or
only down.
5 For Keep parent while drilling, select Yes or No. (The Default setting
inherits the value from the drill path.)
6 For Keep Thresholds while drilling, select Yes or No. (The Default setting
inherits the value from the drill path.)
7 Inherit subtotals from parent, while drilling, select Yes or No. (The
Default setting inherits the value from the drill path.)
8 Set the page-by options as needed.

9 Select the appropriate Drilling filter option.
10 Click Drill Attributes Join Type to specify the join type on specific
attributes to which you drill.
11 Click OK.
Advanced
Page-by enables you to select and display subsets of your report data on
separate pages. This feature is most useful when you have large report results
which require you to scroll to view all of the results.
ToManipulations
review more about the page-by feature, refer to the Report
lesson in the MicroStrategy Developer: Reporting
Essentials course.

Using the page-by setting in the Report Data Options window, you can
specify whether or not to retain page-by selections when you save a report. By
default, page-by selections are retained when you save the report.
Report Data Options Editor - Advanced
Always open this report in full screen mode sets the default display mode for
the report to be full screen, when viewed in MicroStrategy Web.

MicroStrategy Mobile
The MicroStrategy Mobile Data View settings in Report Data Options enable
you to control the design options and report functionality for reports
displayed in MicroStrategy Mobile.
Report Data Options Editor - MicroStrategy Mobile
Tabular interactive enables the widest variety of report manipulations on the

mobile device.
Original layout view disables some Mobile user features, but it enables some
report designer configurations to enable greater flexibility in report design. It
also improves the loading time of a report if the report is paged.
You will learn about SQL generation feature usage in Advanced

Project Design course.


Lesson Summary
Report Data Options are report-level settings that enable you to control
the appearance and behavior of certain objects on your reports.
Report limits enable you to set limits on any metric to apply to a report. A
report limit is applied after all of the reports metrics are calculated. While
they are similar to metric qualification filters, report limits are defined in
the template of a report. Report limits are saved locally to a report and
cannot be reused across other reports.
Metric join type enables you to control how multiple metrics on a report
interact.
Attribute join type enables you to force outer joins to use lookup tables.
The Evaluation Order setting enables you to control the order in which
objects are calculated by the Analytical Engine.
The Subtotals setting enables you to enable or disable the display of

subtotals for custom groups or consolidations on a given report.
The Alias setting enables you to display different names for objects on
your report without changing the actual object names.
The Object Display setting enables you to display a custom group as flat or
hierarchical.
The Null Values settings enable you to control the display of nulls on a
report, whether they come from the data warehouse or are a result of
cross-tabbing. You can also control how nulls are sorted on a report.
Furthermore, you can choose how you want to display nulls that result
from dynamic aggregation.
The Grid Graph Mode subcategory enables you to specify settings for
positioning the grid report on the screen and the percentage of the area
covered by the grid report on the screen.
The Drilling options enable you to control drill behavior on a given report.
The Advanced options include a setting for retaining page-by selections
when you save a report.

MicroStrategy Developer: Advanced Reporting Exercises: Report Data Options 7

Exercises: Report Data Options
Report Limit
Suppose you want to retrieve only those Subcategories with more than
$500,000 in Revenue. There is more than one way to obtain the result set,
but for this exercise, use a report limit.
1 With the Report Editor, create a new report with Subcategory and
Revenue.
2 In the Report Editor, from the Data menu, select Report Data Options.
3 In the Report Data Options window, under Calculations, select Report

Limit
4 Click Modify.
5 In the Report Limit Editor, double-click anywhere in the Limit Definition

pane.
6 Under Choose a metric, beside the Metric box, click Browse.
7 Browse through the folders and double-click the Revenue metric.
8 Under Parameters, in the Function drop-down list, select Metric Value.
9 In the Operator drop-down list, select Greater than.
10 In the Value box, type 500,000.
11 Click OK to close the Report Limit Qualification window.
13 In the Report Data Options window, click OK.
14 Save the report to the My Reports folder as Sales Over $500,000.
2014 MicroStrategy Inc. Exercises: Report Data Options 339

7 Exercises: Report Data Options MicroStrategy Developer: Advanced Reporting
15 Run the report. You should see subcategories with Revenue above
$500,000:
16 Next, drill down from the Cameras subcategory to Item.
Does the report limit apply to the drilled report?

_______________________________________
17 Optional: Obtain the same result set using a metric qualification in the
report filter instead of a report limit. Compare the SQL for the two
reports. Which has more SQL passes? Which is more efficient?
Joins
To observe the effects of join types:
1 With the Filter Editor, create a filter for Category = Books and name it
Books.
340 Exercises: Report Data Options 2014 MicroStrategy Inc.

2 With the Metric Editor, create a conditional metric defined as

Sum(Revenue) (where Revenue is the fact) with the Books filter and
name it Book Revenue.
3 With the Report Editor, create a report with Customer, Revenue, and
Book Revenue.
4 Run the report.
How many rows are returned?
_______________________________________
Not every customer bought an item from the Books Category, so many
customers have no data for that metric. With both metrics defined with
inner joins, you see only the customers that have data for both metrics. To
see all the customers that have data for Revenue, you need to set the
Revenue metric to an outer join.
5 In the report, on the Data menu, select Report Data Options.
6 In the Report Data Options window, under Calculations, select Metric

Join Type.
7 For Revenue, change the Join Type to Outer and click OK.
Ifsetting
you are using Microsoft Access, you also need to change another
for the join type to take effect. On the Data menu, select
VLDB Properties. In the VLDB Properties window, in the Joins
folder, clear the Use default inherited value check box and
change Join Type to Join 92. Click Save and Close.
8 In the Report Editor, save that report as Customer - Joins.
9 Run the report. How many rows were returned?
_______________________________________
All customers who have data for Revenue are returned.
10 With the Report Editor, create a report with Customer in the rows and
Year and Revenue in the columns. When you run the report, you should
see all the customers who have sales in either year.
Why is an outer join not necessary in this case?
_______________________________________
_______________________________________

How would the report result be different if you used two conditional
metrics, 2011 Revenue and 2012 Revenue, instead of the Year attribute?
_______________________________________
_______________________________________
Evaluation Order Compound Metric and Report Limit

You want to see a rank of Subcategories by Revenue. This exercise is based on
the report you created in the first exercise on Report Limits.
1 With the Metric Editor, create a metric as Rank(Revenue), where

Revenue is the metric not the fact. Set the Rank parameter of ASC to
True. That way, the lowest Revenue value gets the rank of 1.
2 In the Metric Editor, click the Subtotals/Aggregation tab and select the
Allow Smart Metric box.
3 Save the metric as Rank Revenue Ascending.
4 Open Sales Over $500,000 report with the Report Editor.
5 Add the Rank Revenue Ascending metric to the columns.
6 Run the report.

Notice that you do not see Ranks 1 through 6 in the report results. This is
because the ranks of all the subcategories were calculated before the
report limit of Revenue > $500,000 was applied.
Suppose you want to see the ranking of Subcategories with Revenue

greater than $500,000, but only against the other subcategories meeting
the same criteria. You can change the evaluation order of the report to
first apply the report limit to restrict the data to just those subcategories
with Revenue exceeding $500,000 and then calculate the ranks of those
subcategories.
7 In the report, on the Data menu, select Report Data Options.
8 In the Report Data Options, under Calculations, select Evaluation Order.
9 Clear the Use default evaluation order check box.
10 Under Data Set evaluation order, for Report Limit, select 1 and for Rank
Revenue Ascending, select 2.

11 Click OK. The report runs again with the new evaluation order, and you
should see the ranks change accordingly:
12 Save the report to the My Reports folder as Subcategory Revenue

Greater Than 500,000.
13 Optional: What happens if the Rank Revenue Ascending metric does not
have its Allow Smart Metric setting enabled?
_______________________________________
_______________________________________
Subtotals
By default, the subtotals of custom groups are disabled. You can turn
subtotals on in the Custom Group Editor or through Report Data Options.
1 With the Report Editor, create a report with the Age Groups custom
group in the rows and Revenue in the columns. You can find the Age
Group custom group in the Public Objects/Custom Groups folder.
2 Save the report to the My Reports folder as Custom Group Subtotals.

3 Run the report.
4 In the report, on the Data menu, select Subtotals.
5 In the Subtotals window, select the Total check box and click OK. Notice
that subtotals are not displayed.
6 On the Data Menu, select Report Data Options.
7 In the Report Data Options window, under Calculations, select Subtotals.
8 Next to Age Groups, click Allow Subtotals drop-down list and select Yes.
9 Click OK.
11 Subtotals are now displayed on the report, as shown below:
12 What is the difference between enabling custom group subtotals in Report

Data Options and enabling the Custom Group Editor?
_______________________________________
_______________________________________
13 Save the report to the My Reports folder as Age Groups Revenue.

Alias
Suppose you want to change the attribute and metric names that display on a
single report.
1 With the Report Editor, open any report that contains a metric and at
least one attribute.
3 In the Report Data Options window, expand Display and select Alias.
4 Under Alias, enter an alias for the metric.
5 Enter an alias for the attribute.
6 Click OK.
7 Execute the report. You should see the aliases displayed in place of the
original names in the report results.
8 Where else does the alias appear? In Design View? In SQL View?
_______________________________________
_______________________________________
9 Save the report to the My Reports folder as Alias Options.
Drilling
You can customize drilling capabilities in the Report Data Options window.
1 With the Report Editor, create a simple report containing Category on

the rows, Units Sold on the columns, and Customer State in the page-by
field.
2 On the Data menu, select Report Data Options.
3 In the Report Data Options window, expand General and select Drilling.

4 If you enable the Drill down only and Keep parent while drilling
options, do these settings also apply to the drilled report?
_______________________________________
_______________________________________
5 Under Add the current page by element as part of the filter when drilling
from section, select Any other part of the report. (You can keep the Any
page-by field option selected as well.) Click OK.
6 In the Report Editor, run the report.
7 Drill from the Books category down to Subcategory. What did the Any
other part of the report option do? (Hint: Look in the Report Details
pane.)
8 Save the report to the My Reports folder as Drilling Options.

7 Report Data Options Exercise Solutions MicroStrategy Developer: Advanced Reporting

Report Data Options Exercise Solutions
Report Limit
In step 15, when drilling down from Cameras to Item, the report limit is
applied to the drilled report.
In the optional question, the report with the report limit is more efficient
than the report with the metric qualification report filter. The report with
the report limit has only one pass, whereas the metric qualification report
filter has 5 passes.
Joins
In step 4, the report returns 9,940 rows.
In step 9, the report returns 10,000 rows.
In step 10, an outer join is not necessary in this case because the report
already contains cross-tabbing nulls. Additionally, using conditional
metrics instead of the Year attribute will result in only the values which
have results.
Evaluation Order
In step 12, you can only change the evaluation order of Rank Revenue
Ascending if Allow Smart Metric is enabled.
Subtotals
In step 10, if the subtotal is enabled in the Report Data Options, it is only
specific to the report. When the subtotal is enabled in the Custom Group
Editor, the subtotal is applied to all instances of the Custom Group.
348 Report Data Options Exercise Solutions 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Report Data Options Exercise Solutions 7
Alias
In step 8, Alias displays in the following views:
Design
Grid
Graph
Grid Graph
There is no option for setting the alias in SQL View.

Drilling
In step 4, if you enable Drill down only and Keep parent while drilling,
these settings are applied to the drilled report.
2014 MicroStrategy Inc. Report Data Options Exercise Solutions 349

7 Report Data Options Exercise Solutions MicroStrategy Developer: Advanced Reporting
350 Report Data Options Exercise Solutions 2014 MicroStrategy Inc.

8
DRILL MAPS
Lesson Description
Drill maps enable you to create fully customized drill paths to control
end-users drill actions on reports. By the end of this lesson, you will know how
a drill map works, how to create a custom drill map, and how it can affect
drilling on a report.

8 Drill Maps MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Describe the purpose of drill maps in reporting. Create and edit drill maps.
Explain the basic concept of drilling and define drill

terminology. (Page 353)
Describe how you create and customize drill maps. (Page 356)

MicroStrategy Developer: Advanced Reporting Drill Maps 8
What is Drilling?

Explain the basic concept of drilling and define drill terminology.
After executing a report in MicroStrategy, you may want to see more detailed
or additional information about the result set. For example, after looking at
annual sales of a certain city, you may want to look at the monthly sales for
the same city. Alternatively, after noticing that a certain item had a very high
profit margin, you may want to analyze the entire category of that item. Such
actions, where you create a related report based on an existing report, are
referred to as drilling.
Drill Maps and Drill Paths

Every project has a default, project-wide drill map. The drill map is
responsible for specifying the drill options available to end-users when they
drill on a report. By right-clicking an object on a report and selecting the Drill
option, you are using the default project-wide drill map.
To truly understand drill maps, you need to understand the concept of a

template unit. A template unit is any MicroStrategy object that can be placed
on a template, such as:
Attribute
Hierarchy
Consolidation
Custom group
Metric
You can also think of a template unit as any part of a template that can be
pivoted.
The MicroStrategy Architect: Project Design Essentials course covers

the creation drill hierarchies.
2014 MicroStrategy Inc. What is Drilling? 353

The different options available to you when you right-click to Drill are drill
paths.
In summary, a drill map determines what drill paths are available for the
different template units on a report. By default, the drill paths available are
based on the system hierarchy (which includes all of the attributes in a
project and their respective relationships) of the project. You can also create
custom drill maps that can override these defaults.
The image below illustrates one of the drill paths in the default project-wide
drill map for the MicroStrategy Tutorial project. In the image, you can
right-click the Electronics category to Drill and choose the Other directions
option to drill to the Brand attribute of the Products hierarchy.
Example - Drill Path
Hierarchy of Drill Map Associations
You can associate drill maps with different objects. For example, you can
associate the Item attribute with a customized drill map such that whenever
Item displays on any report or template, the custom drill map takes effect. In
this example, you associate the custom drill map to a specific template unit
(Item) and the drill map applies globally throughout the project whenever
you use the template unit (a report or template on which you place Item).
You can also define drill maps on a template or a report basis. For example,
assume that you want end-users to be able to drill from the Item attribute to a
particular template, but only from a specific report. You can associate a
custom drill map to the Item attribute on that specific report, so end-users
can drill from Item, only on that report, to the desired template.
354 What is Drilling? 2014 MicroStrategy Inc.

Since Developer offers different levels of drill map associations, it follows a

hierarchy to determine which drill map takes precedence. When a report, its
template, or any of its template units have different drill maps associated
with them, the reports drill map always takes precedence over the templates
drill map, which always takes precedence over individual template unit drill
maps.
2014 MicroStrategy Inc. What is Drilling? 355

Creating and Customizing Drill Maps

Describe how you create and customize drill maps.
Now that you have reviewed the basic principles of drilling and drill maps,
you are ready to learn how to create custom drill maps.
Using the Drill Map Editor, you can create or edit a drill map and associate it
with a report, template, or template unit to view a different set of drill paths
than the default project drill paths.
There are three basic steps in defining a custom drill map:
1 Create a drill map.
2 Define the drill paths that will be available with the drill map.
3 Specify the drill map association to define the reports, templates, or

template units that use the drill map.
Create a Drill Map

The Drill Map Editor enables you to create a drill map or edit an existing drill
map. You can create a drill map and associate it with various objects, or you
can create or modify a drill map for a particular object.
To create a drill map:
1 You can open the Drill Map Editor in one of several ways:
On the File menu, point to New and select Drill Map.
In the Report or Template Editor, on the Data menu, select Edit Drill
Map.
In the Custom Group Editor, on the Custom Group menu, select Edit
Drill Map.
356 Creating and Customizing Drill Maps 2014 MicroStrategy Inc.

In the Consolidation Editor, on the Tools menu, select Edit Drill Map.
In the Attribute Editor, on the Tools menu, select Edit Drill Map.
2014 MicroStrategy Inc. Creating and Customizing Drill Maps 357

The Drill Map Editor

There are several components in the Drill Map Editor.
OriginThe Origin window displays any objects found on a report or in

the specific object for which you are creating a drill map. Whether or not
you see objects in this window depends on how you access the Drill Map
Editor. You can select an object and then create the drill paths for that
object in the Drill Map section of the editor. After you associate an object
with a drill map, that object displays in bold.
Drill Map Editor - Origin
Object BrowserThe Object Browser provides a hierarchical view of the

objects contained in a project.
Drill Map Section

NameThis is the name of the drill map. This field is enabled when
changes are made to the drill map, such as adding or deleting a drill path
or modifying drill properties. The default drill map name is Object Name
Drill Map, where the Object Name is the object you have selected as the
destination object for the drill path. In other words, if you create a drill
path to Customer, the default name would be Customer Drill Map. For
objects in reports and templates, the default name displays as Report
Name Object Name Drill Map.
Drill PathsThis is where you specify the destination to which you want
to drill. Selecting any of these buttons opens up a Select Destination
Object window and enables you to navigate to the desired object. Select
the object and click Open.
Up: You may select other attributes, consolidations, or custom groups

as destinations. The destination is shown as part of the Drill Up menu
when users drill on a report.

Down: You may select other attributes, consolidations, or custom

groups as destinations. The destination is shown as part of the Drill
Down menu when users drill on a report.
Across: You may select other attributes, consolidations, or custom

groups as destinations. You may also select hierarchies as destinations
with this option as well. The destination is shown as part of the Other
directions menu when users drill on a report.
Template: This option enables you to select templates as destinations,

which replace the template of the original report.
Drill Map: This option enables you to select existing drill maps as
destinations. It enables you to create shortcuts to existing drill maps.
Ifmenu,
you do not see this option in the Drill Map Editor, on the View
select View Drill Map Path List.
PropertiesThe Properties options are enabled as soon as you have

created a drill path.
Drill Path Display Name: This is the name that users see in the
right-click menu when they drill on a report.
Drill Path Description: This field enables you to enter a description for
the drill path. There is a limit of 250 characters.
Set Name: Used in coordination with the Across drill paths, this
option enables you to group destinations under one heading. For
example, if you add three attributesCustomer, Day, and
Employeeto the Across drill option, you can create a set name, such
as Lowest, and associate it to all three destinations. The drill menu
would look like this:
Drill Map Editor - Set Name

Priority: This option enables you to set the default drill path. The
priority setting also impacts how the drill path is displayed for reports
in MicroStrategy Web. You may select one of the following:
Low: Selecting Low priority makes the drill path available as a

right-click menu option. In a MicroStrategy Web report, this drill path
is not available as a right-click menu option and can be accessed only
from the More Options link.
Medium: Selecting Medium priority makes the drill path available as a

right-click menu option in both Developer and MicroStrategy Web.
High: Selecting High priority makes the drill path available as a

right-click menu option in both Developer and MicroStrategy Web. It
also makes the drill path the default drill path that is used when a user
double-clicks an object in a report in Developer or clicks the hyperlink
of an object in a report in MicroStrategy Web.
Apply user filter: If selected, this option enables you to use the filter
created by the user when performing the drill. It is selected by default.
Apply report filter: If selected, this option applies the filter of the
original report to the drilled report. This option is selected by default.
Keep parent: If selected, the parent object is kept on the drilled report.
Keep thresholds: If selected, the thresholds are kept on the drilled

report.
Display Mode: This option is enabled when you select a template as a

destination, which replaces the template of the original report. If you add
a template to the Template drill option, you can select the display mode of
the destination template.
Default: Selecting Default means that the destination template display
setting is used.
Grid: Selecting Grid displays the destination report in Grid view.
Graph: Selecting Graph displays the destination report in Graph view.
Grid graph: Selecting Grid graph displays the destination report in

Grid Graph view.

Include other filter: This property is related to an advanced option in the

report filter. Both of these settings determine whether existing attribute
qualifications are merged when the report filter is evaluated. The Drill
Map Editor setting applies only when you drill on a report.
Default: Selecting Default means that the report filters setting is used.
Yes: Selecting Yes means that other qualifications are considered.
No: Selecting No means that other qualifications are ignored,

regardless of the setting in the report filter.
The Consider other filter qualifications property is applied only if

Apply user filtering conditions, Apply original report filter
conditions, or both settings are selected.
Apply addtional filter: This property enables you to apply additional
filtering criteria while drilling.
Clear all: Clicking this option removes all the drill paths defined in the
current drill map. It empties the drill path information completely. It does
not affect the drill map associations.
Reset: Clicking Reset reverses any changes and resets the drill map to its
last saved definition. Drill Map associations are reset as well.
Associate with: This option enables you to associate the drill map to one
or more objects on a report.
The Associate with option is only available within the Drill Map
Editor when it is opened by editing an existing drill map or
creating a new drill map. If you open the Drill Map Editor from
within a given template unit (for example, a report, a
consolidation, or an attribute), you will see an Associations option.
This option merely displays the objects that are already associated
with the drill map.
Remove Associations: This option disassociates an object from the

current drill map.


Lesson Summary
Every project has a default, project-wide drill map. This drill map is based
on the user hierarchies that are also designated as drill hierarchies
(created by the project designer with Architect).
A template unit is any MicroStrategy object that can be placed on a

template.
Drill paths are the different options available to you when you drill on a
report.
You can associate drill maps with different objects: an individual template
unit (to associate the drill map with the object globally across the entire
project), an individual template unit on a specific report, or a template
unit on a specific template.
The hierarchy of drill map associations is: report overrides template, and
template overrides object.
You can access the Drill Map Editor in six different ways: via the Report
Editor, via the Template Editor, via the Custom Group Editor, via the
Consolidations Editor, via the Attribute Editor, and by creating a new drill
map.
There are many components to the Drill Map Editor, including: Origin,
Object Browser, Drill Map Name, Drill Paths, Properties, and so forth.

MicroStrategy Developer: Advanced Reporting Exercises: Drill Maps 8

Exercises: Drill Maps
Drill to Template: Customer Details

These exercises require you to build several template objects for use in a
custom drill map. The exercises are designed to emulate a real-world report
analysis workflow, where you glean more detailed information by performing
a series of drill actions.
1 Using the Report Editor, create a report that contains Customer and
Revenue on the template and Customer State = Florida in the filter.
Save this report as Florida Customers. When you execute the report, the
first few rows should resemble the image below:
2014 MicroStrategy Inc. Exercises: Drill Maps 363

8 Exercises: Drill Maps MicroStrategy Developer: Advanced Reporting
2 When you double-click on any customer in the above report, you want to
see the details of the customers purchases. Using the Template Editor,
create a template that contains the following: Customer, Item, Day, Units
Sold, and Revenue (see image below). Save this template as Customer
Purchase Information.
3 Using the Drill Map Editor, create a new drill map and associate it with
the Customer attribute on the Florida Customers report. When you
double-click any customer on the report, you should drill to the
Customer Purchase Information template (Hint: you need to modify
the priority level for the drill path). Your drill map definition should
resemble the image below. Save this drill map as Customer Purchase
Drill Map.
364 Exercises: Drill Maps 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Exercises: Drill Maps 8
4 After you drill to a customers purchase details, you want to be able to drill
on Customer again to see a customers personal contact information (see
Drill 2 at the end of this exercise). Using the Template Editor, create
another template that contains the following: Customer (display the
attribute forms Last Name, First Name, Address and Email), Customer
State, Customer City, Zip Code, and Customer Age. Save this template
as Customer Contact Information.
5 Using the Drill Map Editor, create another drill map and associate it with
the Customer attribute on the Customer Purchase Information
template. When you drill on any customer, you should be able to drill to
the Customer Contact Information template. You also want to have all
of the project default drill options available to you, so you need to include
the Tutorial Standard Drill Map as a drill path. (Hint: If you do not see
Drill Map under Drill Paths, in the view menu, select View Drill Map Path
List.) Save this drill map as Customer Contact Information Drill Map.
2014 MicroStrategy Inc. Exercises: Drill Maps 365

8 Exercises: Drill Maps MicroStrategy Developer: Advanced Reporting
6 Run the Florida Customers report and perform the drilling as described
in the steps below to see the effects of the custom drill maps you have
created. The images below depict the drill paths for each of the drill
actions described in this exercise.
Drill 1: Double-click or right-click and drill from the first customer,

Abbott Delores, to the Customer Purchase Information template.
You can also drill on the Customer attribute header, but your result
set will take longer to process since you are drilling on all of the
customers on the Florida Customers report.
Drill 2: Drill from the first customer, Abbott Delores, on the drilled
report (the Customer Purchase Information template) to the Customer
Contact Information template. Notice how you also have other drill
options.
366 Exercises: Drill Maps 2014 MicroStrategy Inc.

9
REPORT DEVELOPER TOOLS
Lesson Description
In this lesson, you will learn about some of the tools available to you in
Developer. You learn how the Find and Replace window enables you to define
Report Data Options settings, metric formatting, graph font specifications, and
graph preferences for multiple objects simultaneously. You will also learn how
to use the Project Documentation Wizard to generate project-related
information as HTML files.

9 Report Developer Tools MicroStrategy Developer: Advanced Reporting
Lesson Objectives

Use Find and Replace to change multiple objects simultaneously. Create
project documentation HTML files.
Globally find and replace Report Data Option settings, metric formatting,
graph font specifications, and graph preferences for selected
objects. (Page 369)
Use the Project Documentation Wizard to create documentation of a

projects application objects, schema objects, and configuration
objects. (Page 373)

MicroStrategy Developer: Advanced Reporting Report Developer Tools 9
Find and Replace

Globally find and replace Report Data Option settings, metric formatting,
graph font specifications, and graph preferences for selected objects.
The Find and Replace window enables you to apply certain types of changes
to multiple MicroStrategy objects simultaneously. Using Find and Replace,
you can accomplish the following:
Apply an autostyle to a group of reports or templates
Apply formatting to metrics in bulk
Define graph fonts and character sets for multiple graph reports or
templates
Apply graph preferences to a group of reports
You can change general preferences, such as font and line resizing,
scrolling, and labels; layout preferences; resizing behavior;
available graph types and their settings for MicroStrategy Web and
Developer; and graph object colors.
Modify certain Report Data Options for a group of reports and templates
Since many of the Report Data Options (such as evaluation order) are
relevant only to the report/template in which they are defined, globally
changing these settings can result in unexpected outcomes. Therefore,
only the following Report Data Options are available in Find and Replace:
DisplayNull Values
GeneralDrilling
GeneralAdvanced
GeneralMicroStrategy Options
2014 MicroStrategy Inc. Find and Replace 369

Modify certain Export Options for a group of reports and templates.

Find and Replace - Export Options
370 Find and Replace 2014 MicroStrategy Inc.

To change a Report Data Options property for a group or Reports/template
1 In Developer, on the Tools menu, select Find and Replace.
You must be logged in to a project before you can access the Find and
Replace window.
Find and Replace Tool
2 In the Find and Replace window, select Report Data Options as the
category of properties to modify.
3 Find: Either browse to a group of reports/templates or use a search

object.
4 Replace With: Define the properties to apply to the group of

reports/templates. The Report Data Options button provides access to
the null value display, drilling, and page-by settings.
5 In the Report Data Options window, modify the Report Data Option
setting as needed. Select Apply next to each setting and click OK.
2014 MicroStrategy Inc. Find and Replace 371

6 In the Summary section, there is a list of the reports/templates to be

modified. You can select or clear reports/templates to further refine the
list.
If you want to quickly change the formatting of multiple metrics

simultaneously, choose Metric Formatting as the category. When you
select this option, the Find and Replace window displays slightly different
sections:
FindEither browse to a group of metrics or use a search object.
Replace WithDefine the set of properties to apply to the selected

metrics. You can base the formatting properties on an existing metric
or define new formatting properties for the metric header and metric
values. The formatting modifications you can specify include the
following: number formatting, fonts, colors, alignment borders and
background.
SummaryThis section lists the metrics to be modified. You can select

or clear metrics to further refine the list.
To change the character sets and graph fonts for a group of reports or
templates, you can choose Graph Fonts as the category. The Find and
Replace window displays a similar set of sections as shown above, but
they are tailored to selecting reports or templates and applying specific
character sets and fonts for their graphs.
7 In the Find and Replace window, click Update Summary.
8 Click Replace.
9 In the confirmation window, click OK.
10 In the Find and Replace window, click Close.
372 Find and Replace 2014 MicroStrategy Inc.

Project Documentation

Use the Project Documentation Wizard to create documentation of a projects
application objects, schema objects, and configuration objects.
The Project Documentation Wizard enables project administrators and

report developers to create detailed documentation by translating the
metadata information of application objects, schema objects, and
configuration objects.
This tool is extremely useful when you want to capture the contents of a
project at a given point in time. You can use the HTML documentation files to
identify object owners; view detailed object definitions, creation, and
modification dates/times; and so on.
ToDocumentation
use this feature, you must have the Developer Designer Use Project
privilege.
To open the Project Documentation Wizard:
1 In Developer, log in to a MicroStrategy project.
2 On the Tools Menu, select Project Documentation.
3 In the Project Documentation Wizard, on the Welcome page, click Next.
2014 MicroStrategy Inc. Project Documentation 373

4 Select a project or projects to document and click Next.
5 Select the object categories you want to document: Folders, Application

objects, Schema objects, MDX Cubes and Configuration objects.
Click Next.
6 Choose how much information you want to document about each object.
You can specify all object properties by selecting Basic Properties,
Definition and Advanced Definition, or you can select individual
properties. Click Next.
374 Project Documentation 2014 MicroStrategy Inc.

Depending on which object category you select, you may not see
Definition or Advanced Definition options. For example, selecting
Folders will only give you the Basic Properties option.
7 Select the types of objects to document and click Next.
For example, if you chose to document Application objects, then you must
select whether to include metrics, filters, templates, Report Services
documents, and so forth. Similarly, if you chose Schema objects, you need
to select whether to include facts, attributes, hierarchies, and so forth.
8 Configure how the project documentation will display and click Next.
You can specify the location where the HTML files will be created. (By
default, the HTML files are saved to the users temporary folder.) You can
also specify the structure of the project documentation, how many objects
to display per page and how to sort those objects (by Name or by Type).
Additional settings include paper layout, margin settings, font size, and
image file location. Click Next.
9 On the Summary window, click Finish.
The wizard generates the project documentation HTML files.
This process may take several minutes, depending on the amount

of information to be documented.
10 After the HTML files are generated, click the Open project documentation
after closing the wizard check box and click Next.
2014 MicroStrategy Inc. Project Documentation 375

11 Click Print if you want to print the project documentation. Otherwise,

click Close to open the project documentation HTML file.
12 You navigate through the documentation with a Web browser by clicking

the hyperlinks.
376 Project Documentation 2014 MicroStrategy Inc.


Lesson Summary
With the Find and Replace tool, you can apply settings to multiple objects
simultaneously. The settings include:
Certain Report Data Options
Autostyles
Metric formats
Graph fonts
Graph preferences
Export Options
The Project Documentation Wizard enables you to create detailed

documentation about objects in your MicroStrategy projects by
translating metadata information into HTML files.


MicroStrategy Developer: Advanced Reporting Exercises: Report Developer Tools 9

Exercises: Report Developer Tools
Find and Replace

The Find and Replace tool gives you an efficient means for applying
autostyles to a collection of reports, changing metric formatting for a group of
metrics, changing fonts in several graph reports, and performing bulk
modifications of all of the graph preferences. In this exercise, you will use the
Find and Replace tool to perform some of these functions.
1 In Developer, in the MicroStrategy Tutorial project, run the Revenue,

Costs, and Units Sold by Call Center report in the Public
Objects/Reports/Subject Areas/Sales and Profitability
Analysis folder. Notice the current metric formatting for the Revenue
and Cost metrics.
2 Close the report.
3 From the Tools menu, select Find and Replace.
4 In the Find and Replace window, under Select the category of the
properties to modify, select Metric Formatting.
5 Under Find, select the Choose a set of metrics option and click the
Browse button.
6 In the Select Objects window, browse to the Public

Objects/Metrics/Sales Metrics folder.
7 Select the following metrics: Cost, Profit, and Revenue.
8 Click the right arrow button to add the metrics to the Selected objects list.
9 Click OK.
10 In the Find and Replace window, click Update Summary.
11 Under Replace with, select the Set the formatting properties

individually option because you want to specify the formatting manually.
2014 MicroStrategy Inc. Exercises: Report Developer Tools 379

9 Exercises: Report Developer Tools MicroStrategy Developer: Advanced Reporting
12 Select the Metric Values check box and click Formatting.
13 In the Format Cells window, on the Number tab, select the Currency
category.
14 Change Decimal places settings to 3 decimal places.
15 Click OK.
16 In the Find and Replace window, click Replace.
You cannot undo the Replace action.

17 In the confirmation window, click OK.
18 In the Find and Replace window, click Close.
19 In the MicroStrategy Tutorial project, run the Revenue, Costs, and

Units Sold by Call Center report again. Notice how the metric
formatting has changed to display the metric values as desired.
20 Close the report.
As a report developer, you want to document all of the application objects
that are created during the report development phase of the project. This
exercise is designed to give you hands-on practice with the Project
Documentation Wizard. You will use the following step-by-step instructions
to create HTML files that document most of the application objects that you
created in the MicroStrategy Tutorial project during this course.
1 In Developer, log in to the MicroStrategy Tutorial project.
2 On the Tools Menu, select Project Documentation.
3 In the Project Documentation Wizard, click Next.
4 Select MicroStrategy Tutorial and click Next.
5 Select Application objects and click Next.
380 Exercises: Report Developer Tools 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Exercises: Report Developer Tools 9
6 Select the Application Objects check box, which automatically selects

the Basic Properties, Definition, and Advanced Definition check boxes.
You can expand the Basic Properties check box to see the type of
information you will include in your project documentation. Click Next.
7 Select all applicaton objects except the following: Autostyle, Data Mart
Report, HTML document, Predictive metric, and Search. Expand Metrics
and notice that you can select Basic Properties, Definition and Advanced
Definition.
Certain object types may not show Definition or Advanced

Definition, such as Base Formula and Drill Map.
8 Click Next.
9 In the Settings window, keep all of the default settings and click Next.
10 The Summary window, click Finish, and the wizard generates the project
documentation files.
This process may take a few minutes.

11 After the project documentation has finished generating, close the wizard
and view the documentation in HTML format.
12 Answer the following questions, based on your project documentation

files:
Under the MicroStrategy Tutorial project, click to see the list

structure. What objects are listed? Do these match the objects you
selected in the wizard?
_______________________________________
_______________________________________
Click Electronics Revenue by Region. Based on the reports

description, what does this report contain in the columns? In the
rows? Does it use thresholds?
_______________________________________
_______________________________________
Scroll back up to the top of the page and click MicroStrategy

Analytics Modules.
Click Click here to see the list structure.
2014 MicroStrategy Inc. Exercises: Report Developer Tools 381

9 Exercises: Report Developer Tools MicroStrategy Developer: Advanced Reporting
Click Metric.
Click Length of Employment (Months). What type of metric is it?

Does it use any conditionality or transformations? Also, list its
formula below.
_______________________________________
Scroll back up to the top of the page and click MicroStrategy

Analytics Modules.
Click Click here to see the folder structure.
Click Schema Objects.
Click Facts.
Click Cost. Why is nothing available?
_______________________________________
Click MicroStrategy Tutorial.
Click My Personal Objects.
Click My Reports. Browse the objects you created during this course.
Good job!
382 Exercises: Report Developer Tools 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Report Developer Tools Exercise Solutions 9

Report Developer Tools Exercise Solutions
In step 11, the following objects are listed and match the objects you
selected in the wizard:
Base formulas
Consolidations
Custom Groups
Derived Elements
Documents
In the Electronics Revenue by Region report, the report contains the

following:
Columns: Year, Revenue
Rows: Region
Thresholds: Yes
The Length of Employment metric is a compound metric.
The Cost fact is not available because it is not an application object.
2014 MicroStrategy Inc. Report Developer Tools Exercise Solutions 383

9 Report Developer Tools Exercise Solutions MicroStrategy Developer: Advanced Reporting
384 Report Developer Tools Exercise Solutions 2014 MicroStrategy Inc.

A
MICROSTRATEGY TUTORIAL
Appendix Description
This appendix provides information on the MicroStrategy Tutorial project,

including the data model and physical warehouse schema.

A MicroStrategy Tutorial
The MicroStrategy Tutorial Data Model

A data model is a logical map that represents the inherent properties of
enterprise data, disregarding software, hardware, or machine performance
considerations. Data models show:
Data elements grouped into records
Relationships and association surrounding those records
For more detailed information about data modeling, refer to the Project
Design Guide.
Although the MicroStrategy Tutorial data model is included in this section

for your reference, you can also view it directly in the product.
To view the MicroStrategy Tutorial data model:
1 In Developer, log in to the project source that contains the MicroStrategy

Tutorial project and expand the MicroStrategy Tutorial project.
2 On the Schema menu, point to Graphical View and select Hierarchies.
3 In the Hierarchy Viewer, to view a different hierarchy, in the Hierarchy

drop-down box, select the hierarchy you want to view.
4 To focus on a different entry point, in the Entry Point drop-down box,

select the entry point you want to view.
5 To view the entire hierarchy in the window, on the View menu, select Fit
in window.
You can rearrange the attributes by dragging and dropping them.

Rearranging attributes does not affect the browse order, but it enables
you to view the hierarchy in a way that is meaningful to you.
6 To return to the default view, on the View menu, select Auto arrange.
7 To save the layout view of the hierarchy, on the File menu, select Save
layout. The next time you open the Hierarchy Viewer, it displays the
saved view.
386 The MicroStrategy Tutorial Data Model 2014 MicroStrategy Inc.

MicroStrategy Tutorial A
The MicroStrategy Tutorial data model consists of the following hierarchies:
Geography
Customers
Time
Products
Geography Hierarchy
2014 MicroStrategy Inc. The MicroStrategy Tutorial Data Model 387

Customers Hierarchy
388 The MicroStrategy Tutorial Data Model 2014 MicroStrategy Inc.

Time Hierarchy
2014 MicroStrategy Inc. The MicroStrategy Tutorial Data Model 389

Products Hierarchy
The MicroStrategy Tutorial Schema

A schema is a logical and physical definition of warehouse data elements,
physical characteristics, and interrelationships. This appendix shows the
physical schema with the data types displayed. You can also choose to view
the logical schema on your own. For instructions, refer to the procedure
below.
For more detailed information on the schema, refer to the Project Design
Guide.
Although the MicroStrategy Tutorial physical schema is listed below for your
reference, you can also view the physical or logical schema directly in the
product.
To view the MicroStrategy Tutorial schema:
1 In Developer, log in to the project source that contains the MicroStrategy

Tutorial project and expand the MicroStrategy Tutorial project.
390 The MicroStrategy Tutorial Schema 2014 MicroStrategy Inc.

2 On the Schema menu, point to Graphical View and select Tables.
3 In the Table Viewer, to change display preferences for the logical view, on
the Options menu, select any of the following options:
Show joinsEnables you to select whether to connect the tables to

represent the joins between the table columns
Use circular joinsEnables you to select whether to use circular joins
Show relationshipsEnables you to select whether to map the

relationships between the tables
Show relationship typesEnables you to select whether to

differentiate between one-to-one, one-to-many, many-to-one, and
many-to-many relationships
Show columnsEnables you to select whether to display the

warehouse columns that define each attribute as a link between the
logical and physical views
4 To switch to the physical view, on the View menu, select Physical view.
5 To change display preferences for the physical view, on the Options menu,
select any of the following options:
Show joinsEnables you to select whether to connect the tables to

represent the joins between the warehouse tables
Use circular joinsEnables you to select whether to use circular joins
Show column data typesEnables you to select whether to show the

data type and size for each column
Show table prefixesEnables you to select whether to display the
table prefix as part of the table name
6 To switch back to the logical view, on the View menu, select Logical view.
7 To view the entire schema in the window, on the View menu, select Fit in
window.
You can rearrange the tables by dragging and dropping them.

Rearranging the tables does not affect the relationships or joins, but it
enables you to view the tables in a way that is meaningful to you.
8 To return to the default view, on the View menu, select Auto arrange.
2014 MicroStrategy Inc. The MicroStrategy Tutorial Schema 391

9 To save the layout view of the tables, on the File menu, select Save
layout. The next time you open the Table Viewer, it displays the saved
view.
10 To copy the layout view, on the File menu, select Copy as Metafile
(.wmf).

The MicroStrategy Tutorial schema is divided into the following parts:
Geography
Customers
Time
Products
Fact tables
Partition mapping table

Geography Schema

Customers Schema

Time Schema

Products Schema

Fact Tables Schema
Partition Mapping Table

B
ADDITIONAL TOPICS
Appendix Description
This appendix describes some of the advanced settings that exist in various
Developer object editors:
Conditional Metrics: Embedding Method
Filter Editor: Merge Attribute Qualifications
Dynamic Aggregation
Prompt Editor: Level Prompts
Prompted Custom Group
Prompt-in-prompt Functionality
Value-prompted Metrics

B Additional Topics MicroStrategy Developer: Advanced Reporting
Advanced Settings
Conditional Metrics: Embedding Method

Three embedding methods are available to you through the Advanced button
in the Condition pane of the Metric Editor:
Merge into new
Merge report filter into metric (default)
Merge metric condition into report
These settings enable you to control how a report filter interacts with the
filter of a conditional metric. These settings are most relevant when you have
metric qualifications in both the report filter and the filter being applied to
the metric.
Business Scenario
You want to identify the top 10 items in terms of Revenue, but you have an
additional qualification for the top 5 customers in terms of Revenue. The first
qualification is defined in the report filter and the second is in a filter applied
to a metric.
You can change the following embedding methods to control the interaction
of these two filters and affect the report result set.
400 Advanced Settings 2014 MicroStrategy Inc.

MicroStrategy Developer: Advanced Reporting Additional Topics B
Merge Report Filter into Metric
This is the default setting. First, the report filter qualification is evaluated and
based on the intermediate result set, the filter on the metric is evaluated.
Using the above business scenario, the top 10 items based on Revenue are
identified. Then, the top 5 customers who purchased any of the top 10 items
is identified.
The circles shown in the diagram above do not represent the

customers nor the items but rather the Revenue that was spent by the
top 5 customers and the Revenue that was spent on the top 10 items.
Also, notice that each patterned section of the circles can have a
completely different data set.
2014 MicroStrategy Inc. Advanced Settings 401

Merge Metric Condition into Report
In this case, the filter in the metric is evaluated first and based on this
intermediate result set, the report filter is evaluated. For example, first, the
Revenue spent by the top 5 customers is identified, and then the top 10 items
based on Revenue that was purchased by those customers is identified.
Merge into New

Merge into new intersects the report filter with the filter in the metric. If you
choose this option, the result set returns the Revenue for the top 10 items and
the Revenue for the top 5 customers, whether or not the top 5 customers
purchased any of the top 10 items. Since the qualifications retrieve
completely different data sets, it is possible that the intersection of the two
qualifications produces an empty result set.
Filter Editor: Set Qualification Advanced Button

The Advanced button within the Set Qualification window of the Filter Editor
enables you to specify whether or not existing attribute qualifications should
be merged into the calculation of the metric qualification. By default, this
option is selected, combining the qualifications.
A metric qualification is contained in a separate pass of SQL, creating a

temporary table. If the qualifications are merged, attribute qualifications are
added to that pass of SQL. If they are not merged, the attribute qualifications
are not included in the metric qualification. They instead appear in the main
SQL pass.
For example, the report below shows Revenue by Region. The report filter
contains the attribute qualification on Region = Northeast and Mid-Atlantic,
and the metric qualification on Revenue > $4,000,000. If the default option
is kept, the qualifications are merged, and both are included in the SQL pass
that resolves the metric qualification. Only Revenue for Northeast and
Mid-Atlantic is considered when evaluating the metric qualification, Revenue
> $4,000,000. The report results are:
Example - Set Qualification Advanced Button

Now, modify the report filter in design view and toggle the operator between
the two qualification from AND to OR. Run the report and notice that you get
the same result set rather than seeing all regions that have Revenue >
$4,000,000 other than Northeast and Mid-Atlantic. You get this incorrect
result set since the qualifications are merged, and both are included in the
SQL pass that resolves the metric qualification.
In contrast, if the qualifications are not merged, the attribute qualifications

are not included in the metric qualification. They instead appear in the main
SQL pass to give the right result set. As shown in the following example,
regions other than Northeast and Mid-Atlantic are included in the report
results since they have Revenue > $4,000,000.
Besides affecting the report results, merging the qualifications reduces the
amount of data that is processed.

Dynamic Aggregation
Dynamic aggregation is a function with OLAP Services which dictates the
behavior of reports that are aggregated within the Intelligent Cube. To
understand the purpose of the dynamic aggregation function, consider the
report below, which contains the attributes Region and Call Center, as well as
a metric, Average Revenue:
Example - Dynamic Aggregation
Consider what happens if you remove Call Center from the Report Objects
window. This type of modification is known as aggregation because Average
Revenue data aggregates to the Region level. This action requires new SQL to
be generated and results to be retrieved from the data warehouse.
Aggregations requiring SQL are normal aggregations.

Notice
window.
that Call Center has been removed from the Report Objects
Example - New SQL Without Call Center on the Template
Now consider what happens to the metric values of Average Revenue when
you move Call Center to the Report Objects window (note that Call Center is
still a part of the report, though it is not displayed in the report view). This
action aggregates the metric values to the Region level without generating
new SQL; this is called dynamic aggregation.
When reports are dynamically aggregated, values for most metrics are simply
summed. However, in the report below, the MicroStrategy Engine is aware
that simply summing a metric that calculates averages produces erroneous
results (summing the average sales of call centers does not produce an
accurate average for its given region). Therefore, the MicroStrategy Engine
produces temporary null values for Average Revenue when it is dynamically
aggregated. By default, the temporary null values for such metrics are
replaced by -- on the report, as shown below:
Note that Call Center is still in the Report Objects window.

Example - Dynamic Aggregation on Average Revenue

You can change the default display of -- for temporary null values via
the Aggregation null values setting of Report Data Options.
The example uses a metric defined with the Average (Avg) function. The
MicroStrategy Engine produces temporary null values for dynamic
aggregation for many functions, including:
Average (Avg)
Standard Deviation (Stdev)
Count (Distinct=True)
Geometric Mean (GeoMean)
Variance (Var)
Mode (Mode)
Dynamic aggregation also occurs in metrics defined with multiple

aggregate operators (for example, SUM(Fact 1) + SUM(Fact 2)).
It occurs in these types of metrics because two facts, each with their
own aggregate operator, could possibly aggregate to different levels
that do not match the report level or each other. When the facts are
calculated at different levels, you might see erroneous results for the
metric when you move an attribute to the Report Objects window.
Hence the display of temporary null values.

When you create a metric, the dynamic aggregation function in the

Subtotals/Aggregation tab of the Metric Editor is set to Default. The
definition of Default for most metrics is SUM, but for a metric defined with
AVG or any of the other aforementioned functions, Default produces
temporary null values. However, the Dynamic aggregation function setting
enables you to override behavior, as shown below:
Change Dynamic Aggregation Function
You can change the dynamic aggregation function to any of the other
functions available in the Metric Editor. For example, you could change the
dynamic aggregation function for the Average Revenue metric to Average,
thus overriding the temporary null values.
To change the dynamic aggregation function for a metric:
1 In the Metric Editor, click the Subtotals /Aggregation tab
2 For Dynamic aggregation function, select the appropriate function.

Reason for Temporary Null Values
By default, the aforementioned functions produce temporary null values

when dynamically aggregated because there are situations when such
calculations may produce erroneous results. This does not mean, however,
that a metric defined with AVG, STDEV, MEAN, and so forth will always
produce erroneous results. It simply means that caution should be taken
when changing the dynamic aggregation function from Default to another
function. To illustrate this, assume the following list contains all of your sales
data:
Now consider a scenario in which you create Report 1 to display Category,

Subcategory, Revenue, and Average Revenue.
You then remove Subcategory from the Report Objects window, generating
new SQL and a new report. Because new SQL was generated and executed
against the data warehouse, the new figures for Average Revenue are
calculated using the fact table.

Now consider the same scenario, except this time, the same result is achieved
through dynamic aggregation. Notice that in the illustration that follows, you
remove Subcategory from the report view (meaning, Subcategory remains in
the Report Objects window). No SQL is generated for the new report view.
Notice, however, that the values for Average Revenue for Books are incorrect.
The reason is that with dynamic aggregation, metric values are calculated
with information in the report cache (or Intelligent Cube), not against the
fact table in the data warehouse. Looking at the sales data, you know that the
correct figure for the Average Revenue for Books should be $30.00, not
$25.00.
The dynamic aggregation function has been changed from Default to

Average for the Average Revenue metric below:
For the reasons stated above, you should use caution when changing the
dynamic aggregation function from Default.

Prompt Editor: Level Prompt

Level prompts are used to specify the level of calculation for a level metric.
To create a level prompt:
1 In the Prompt Generation Wizard, select Level prompt and click Next.
2 Click Add.
3 In the Select Objects window, add attributes to the Selected window and
click OK.
4 Click Next.
5 Enter a title and description, if desired.
6 Click Finish.
7 Save the prompt.
Using Level Prompts in a Report
Level prompts are used in metrics to enable users to specify the level of
aggregation for a metric at report run-time.
To use a level prompt in a metric:
1 With the Metric Editor, create a new metric.
2 Click Level (Dimensionality).
3 In the Object Browser, drag a level prompt and drop it into the Level
(Dimensionality) pane.
4 Save the metric.
When you run a report with a prompted level metric, a prompt resolution
window opens enabling you to choose the level of calculation for the metric.

To answer a level prompt:
1 In Developer, run a report containing a prompted level metric.
2 In the prompt resolution window, from the list of available attributes,

select an attribute level and click > to add it to the metric.
3 Specify the appropriate values for Filtering and Grouping.
4 Click Finish.
The report runs and the metric calculates at the level that you chose.
Prompted Custom Groups

You can make custom groups more dynamic by incorporating prompts into
their definitions. For example, imagine a business scenario where you want
the user to be able to specify a time period at run-time, and you want the
report to display the dates within this time period in the rows of the template,
as shown below:
Example - Prompted Custom Groups

When you run the report, you select a start date and an end date for the time
period. These selections are captured with value prompts embedded within a
custom group element, as shown below:
Custom Group Definition Editor
The custom group elements display option is set to expand to show the
individual dates within the time period. This way, you see the overall Profit
and Profit Margin for the seven days as well as the Profit and Profit Margin
for each day. Every time you run or reprompt the report, you can select a
different time period.
Prompt-in-prompt Functionality
Prompt-in-prompt enables you to use the answer to one prompt to define
another prompt. To enable prompt-in-prompt, you must use attribute
element list prompts.
For example, you can create a prompt that filters on a certain list of
categories. This Category prompt is the inner prompt. You must answer it
before its dependent prompt, sometimes called the incomplete prompt, is
loaded. The dependent prompt presents a list only of the subcategories that
are in the categories you selected for the inner prompt. You create the
dependent, Subcategory prompt by embedding the Category prompt within
it.

At report run-time, suppose you answer the inner prompt on Category with
Movies. The dependent, Subcategory prompt then loads with its attribute
elements filtered by Movies.
The inner prompt always loads before the dependent prompt.

At a high-level, to create a prompt-in-prompt:
1 To create the inner prompt, open the Filter Editor and choose Add an
attribute qualification.
2 Select the attribute on which you want to qualify. For the example above,
choose Category.
3 In the Qualify on drop-down list, choose Elements.
4 In the Operator drop-down list, choose In list.
5 Click Prompt.
6 In the Prompt Generation Wizard, accept the defaults and click Finish.
7 Click OK.
9 To create the dependent prompt, open the Prompt Generation Wizard

and under Filter definition prompt, select the Choose from an attribute
element list option.
10 Click Next.
11 Select the attribute on which you want to base the list of attribute
elements for the dependent prompt. For the example above, choose
Subcategory, and click Next.
12 Select the Use a filter to reduce the number of elements option and
select the inner prompted filter you created earlier.
13 Click Finish and save the prompt.
14 In the Report Editor, add the prompt to the filter definition pane.

15 Run the report and observe how the inner prompt loads first. After you
answer the inner prompt, the dependent prompt loads, and its attribute
elements are filtered based on the answers provided for the inner prompt.
The above example assumes that the inner prompt and dependent
prompt are based on attributes that have a direct relationship with
each other. Both, Category and Subcategory attributes belong to
the Product hierarchy. However, you may want to use
prompt-in-prompt with attributes that are not directly related. To
achieve this type of analysis, you need to use either a relationship
filter or a shortcut-to-a-report qualification filter.
Value-Prompted Metrics
You can add a value prompt to a metric expression to enable end-users to
determine how it will calculate at run-time. With value-prompted metrics,
you can answer a question like, What if I increased my sales by 15%? You
would enter 1.15 as your answer to the value prompt.

You define a value-prompted metric in the Metric Editor as follows:

Example - Value Prompted Metrics
If you want to see the price of items if they all increased by 15%, a report with
this value-prompted metric might look like the following:
Result - Value-Prompted Metric

This simple example should give you an idea of the types of complex
questions you can ask and answer with multiple prompts embedded in
multiple metrics.


INDEX
A business intelligence 25
metadata database 27
Advanced Functions 83 source system 26
advanced functions four-tier 31
date and time functions 91 three-tier 30
N-tile 90 attribute join type 311
rank 89 attributes
round 89 relationships 144
RunningSum, RunningAvg, Moving- attribute-to-attribute filtering 125
Sum, MovingAvg 89
select examples 88
advanced subtotal B
custom subtotals 94 band count 190
advanced subtotals 94 band for each distinct metric value 191
user-defined subtotals 97 band size 190
alias, metric banding points 190
about 322 banding, custom group 189
set 323 band count 190
Analytical Engine 26 band for each distinct metric value 191
analytical functions 84 band size 190
application objects 28 banding Points 190
Apply functions base formulas
about 135 define 76
use 136 break by 141
architecture business intelligence systems 25

Index MicroStrategy Developer: Advanced Reporting
C banding
about 189
caches band count 190
overview of 33 band size 190
calculation derived elements 257 banding points 190
conditional metrics 77 create 192
creating 77 create 186
embedding 400 Custom Group Editor
filter elements 78 about 184
configuration objects 28 Definition area 185
elements 184
consolidation
defining a 185
Consolidation Editor 179
elements 184
create 179-180
SQL query 183
element
import 180, 181
evaluation order D
about 313
data warehouse 26
set 315
decision support system
functions 177
architecture 25-28
multiple 316
data warehouse 26
row level math 177
four-tier 31
count metric 86
source system 26
create
three-tier 30
conditional metrics 77
derived elements
count metric 86
about 251
custom subtotal 96
all other 259
derived elements using editor 267
calculation 257
intelligent cubes 209
derived elements editor 265
intelligent cubes using reports 212
filter 255
level metrics 55
format 274
nested metric 82
group 253
quick calculations derived
elements 264 interaction
derived metrics 277
quick group derived elements 262
drilling 280
stand-alone derived elements 270 page-by 277
transformation metric 74 prompts 282
user-defined subtotal 99 thresholds 279
custom group view filters 275

MicroStrategy Developer: Advanced Reporting Index
standalone joint element

create, share 270 create 128
type 253 joint element list
derived elements editor 265 about 127
drill map metric qualification 139
customizing 356 merge attribute qualifications 403
Editor 357 metric qualifiers
break by 141
template unit 353
metric-to-metric 142
drill path
pass-through expression
types 357
create 136
drilling
relationship
drill map
about 145
customizing 356
create 148
drill path
report as filter 151
types 357
set qualification 144
options
about 331 filter derived elements 255
set 333 filtering 45
dynamic aggregation 230, 405 Filtering Set to None 54
dynamic dates filters
about 130 view filters 230
create 131 find and replace 369
evaluation order 132 export options 370
four-tier 31
four-tier architecture 31
E functions
evaluation order N-tile 90
about 313 overview 83
set 315 Rank 89
RunningSum 89
F using 84
filter
attribute-to-attribute 125 G
dynamic dates group derived elements 253
about 130 grouping 44
create 131
Grouping Set to None 50
evaluation order 132
Filtering Set to Absolute 51
import filter elements 133
Filtering Set to Ignore 53

Filtering Set to Standard 50 M

Grouping Set to Standard
metadata database 27
Filtering Set to Absolute 47
application objects 28
Filtering Set to Ignore 48
configuration objects 28
Filtering Set to Standard 46
data types 28
schema objects 28
I metric
import filter elements 133 alias
Intelligent Cube 207 about 322
set 323
intelligent cubes 207
base formulas 76
concept 207
conditional
convert report into intelligent
cubes 212 embedding 400
filter elements 78
create 209
conditional metrics 77
publish 211
create 77
refresh using a schedule 213 remove related report filter
sharing 208 elements 78
count 86
J embedding methods 400
evaluation order
join types
about 313
about 307 set 315
set, metric level 311, 320 functions
set, report level 309 N-tile 90
joint element filter overview 83
create 128 Rank 89
joint element list RunningSum 89
about 127 using 84
grouping 44
join types
L about 307
level metrics set, metric level 311, 320
about 43-58 set, report level 309
create 55 level
examples 45-55 create 55
examples 45-55
review 59
filtering options 45
target 44 grouping 44
target 44

MicroStrategy Developer: Advanced Reporting Index
nested metric 80 Q
non-aggregatable
Query Engine 26
about 66
query flow, report 32
qualifier
about 139
break by 141 R
target 44
relationship filter
transformation
about 145
components 73
create 148
create 74
expression-based 71 report
table-based 72 caches 33
types of 71 limits
metric-to-metric filter 142 define 305
MicroStrategy Products 27 set 307
row level math 177
security 35
N report as filter 151
nested metric Report cache sharing 208
about 80 report data options 303
create 82 alias 322
non-aggregatable attribute join type 311
about 66 drilling 331
null values 326 find and replace 369
metric join type 307
O null values 326
object display 324
object display 324
page by 335
subtotals 320
P report execution
page by 335 query flow 32
permissions, security report level 43
change 37
process 26 S
project documentation wizard 373
schema objects 28
prompt
security
level prompt 411
permissions, change 37
value 415
report 35
server components 26

set qualification 144

source system 26
SQL Engine 26
SQL query
custom groups 183
subtotals 320
T
target 44
template unit 353
three-tier architecture 30
Transformation 70
transformations
components 73
create 74
expression-based 71
table-based 72
types of 71
V
value prompt 415
view filter 230
view filters 230
derived elements 275
Metric Qualification Level 230

Microstrategy Advanced Reporting

Uploaded by

Copyright:

Available Formats

Microstrategy Advanced Reporting

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Microstrategy Advanced Reporting

Uploaded by

Copyright:

Available Formats

MICROSTRATEGY DEVELOPER: ADVANCED REPORTING

All Contents Copyright 2014 MicroStrategy Incorporated. All Rights Reserved.

MicroStrategy, MicroStrategy 6, MicroStrategy 7, MicroStrategy 7i, MicroStrategy 7i Evaluation Edition,

MicroStrategy University MicroStrategy Incorporated

Preface Course Description.................................................................... 13

1. Introduction to Lesson Description ................................................................... 23

2014 MicroStrategy Inc. 5

2. Advanced Metrics Lesson Description ................................................................... 41

6 2014 MicroStrategy Inc.

Use an Advanced FunctionRunning Revenue ................. 116

3. Advanced Filters Lesson Description ................................................................. 123

2014 MicroStrategy Inc. 7

Market Basket Analysis........................................................ 169

4. Consolidations and Lesson Description ................................................................. 173

5. Advanced OLAP Lesson Description ................................................................. 205

8 2014 MicroStrategy Inc.

View Filters and Dynamic Aggregation...................................... 230

6. Derived Elements Lesson Description ................................................................. 249

7. Report Data Options Lesson Description ................................................................. 301

2014 MicroStrategy Inc. 9

Attribute Join Type ............................................................... 311

8. Drill Maps Lesson Description ................................................................. 351

9. Report Developer Lesson Description ................................................................. 367

10 2014 MicroStrategy Inc.

Find and Replace................................................................. 379

A. MicroStrategy Tutorial The MicroStrategy Tutorial Data Model ..................................... 386

B. Additional Topics Appendix Description.............................................................. 399

Index ......................................................................................... 419

2014 MicroStrategy Inc. 11

12 2014 MicroStrategy Inc.

This three day course explores advanced topics in MicroStrategy Developer.

2014 MicroStrategy Inc. 13

Who Should Take This Course

MicroStrategy Developer: Reporting Essentials

Basic knowledge of SQL

MicroStrategy Visual Insight Essentials

MicroStrategy Report Services: Documents and Dashboards

Certified Report Developer

14 Who Should Take This Course 2014 MicroStrategy Inc.

Describe the MicroStrategy business intelligence architecture that serves as

Create level, non-aggregatable, transformation, conditional, and nested

Create several types of advanced filters. (Page 124)

Explain the concept of Intelligent Cubes. Create Intelligent Cube reports

Explain the derived elements feature, the different types of derived

Use Find and Replace to change multiple objects simultaneously. Create

2014 MicroStrategy Inc. Course Objectives 15

About the Course Materials

CourseYou achieve these overall objectives by successfully completing all

Main TopicYou achieve this secondary objective by successfully

16 About the Course Materials 2014 MicroStrategy Inc.

Opportunities for Practice

The following sections within lessons provide you with opportunities to

Click Select Warehouse.

References to code, formulas, or calculations within paragraphs are formatted

2014 MicroStrategy Inc. About the Course Materials 17

Type copy c:\filename d:\foldername\filename.

References to a keyboard key or shortcut keys are in uppercase letters in bold

The aggregation level is the level of calculation for the metric.

Notes and Warnings

A note icon indicates helpful information.