Building a Quick Query For Analysis

How to Build Quick Query for Analysis in Microstrategy?

Creating a report by combining a template and a filter: Report Wizard:

A report is a combination of a template and a filter:

• A report template is the structure that underlies any report. It specifies the set of information that the report should retrieve from your data source, and the way that you want the data to be displayed.
• A filter screens data in your data source to determine whether the data should be included in or excluded from the calculations of the report results.

To build a report using the Report Wizard:

To start the Report Wizard

1. In MicroStrategy Desktop, log in to a project. To log in to a specific project, see Starting MicroStrategy, page 2.
2. Expand the My Personal Objects folder, and then open the My Reports folder.
3. Right-click on the right-hand side of Desktop, select New, and then select Report. The New Grid dialog box opens.
4. Select Report Wizard and click OK. The Report Wizard opens.

To select the template

A report template is the structure that underlies any report, containing what is to be displayed on the report and the way to display it.
5. From the list of templates on the left, select a template, then click the > arrow to move it to the right.
6. Click Next.

To select the filter

A filter specifies the conditions that the data must meet to be included in the report results.
7. From the list of filters on the left, select a filter, then click the > arrow to move it to the right.
8. Click Next.

To review your selections

9. Review the summary of your selections. If you want to make any changes, click Back. Otherwise, click Finish.
10. To save your new report, from the File menu, select Save As. The Save Report As dialog box opens.
11. Navigate to a project folder in which to save the report, type a name for the report, and click Save.

To create a shortcut object from the original object:

1. Right-click the object for which you want to create a shortcut and select Create Shortcut. The Browse for Folder dialog box opens.
2. Navigate to the folder in which you want to save the shortcut and click OK. For example, you may save shortcuts in your My Objects folder within your project.

MicroStrategy objects

All reports have specific business objects placed on them. Those objects determine what data is gathered from your data source, how that data is calculated, and how the results are displayed when each report is run.
Objects used on reports include such things as:

• Attributes: An attribute is a business concept, such as Product, Employee, Month, and so on. Attributes provide a context for metrics
• Metrics: A metric is a business measure or key performance indicator, such as Revenue, Profit, Employee Headcount, or Probability of Purchase.
• Filters: A filter sifts the data in your data source to bring back the information that answers exactly what you require. To create filters and use them on reports,
• Prompts: A prompt is a question the system presents to a user during report execution.

Check these MicroStrategy interview questions to learn how you can grab the high-paying jobs!

Viewing and working with metrics: The Metric Editor

You use the Metric Editor to create and save metrics, and to edit existing metrics. The Metric Editor is accessible from MicroStrategy Desktop and is shown in the image below:

To access the Metric Editor:

1. In MicroStrategy Desktop, from the File menu, select New, and then select Metric. The Metric Editor opens.
If the New Metric dialog box is displayed, click the Empty Metric icon. If you do not want this dialog box to be shown in the future, select Don’t show this dialog in the future. Click OK.

Metrics terminology:

The following terms are used throughout this guide and the Advanced Reporting Guide to describe aspects of metrics.

• Metric formula: A metric’s formula is made up of a mathematical function and the business facts stored in your data source.
• Metric expression: A metric’s expression is made up of all the metric components displayed when a metric is opened in the Metric Editor
• Metric definition: A metric’s definition includes the metric’s expression, plus any software settings applied to the metric.

Creating a metric

You create a metric using the Metric Editor. You can also use the Metric Editor to modify an existing metric. The Metric Editor is accessible from Desktop.
To create a metric:

To access the Metric Editor

1. In MicroStrategy Desktop, from the File menu, select New, and then select Metric. The Metric Editor opens.
If the New Metric dialog box is displayed, click the Empty Metric icon. If you do not want this dialog box to be shown in the future, select Don’t show this dialog in the future. Click OK.

To define the metric’s formula

2. A metric definition must contain a formula. Use the Object Browser area on the left side of the Metric Editor to locate attributes, facts, or other metrics to use in the metric’s definition.

• If the Object Browser is not visible, from the View menu select Object Browser.
• To use sample Tutorial data to create a sample metric, in the Object Browser navigate to the Facts folder and locate the Revenue fact.

3. Drag objects from the Object Browser and drop them in the Definition pane on the lower right side of the Metric Editor. As you create the metric, this Definition pane displays the entire metric definition, including its formula and calculation level.
4. When you drop the object into the Definition pane, the default mathematical function is applied automatically to your formula.

• To use sample Tutorial data, drag the Revenue fact into the Definition pane. The metric formula appears as follows:

Sum(Revenue) {~}
5. You can modify the function by using the Object Browser on the left to navigate to Functions and Operators:
a. Open the folders to see all functions available in MicroStrategy.
b. Double-click on a function in the Object Browser to use it as a replacement for the function in the Definition pane on the right.
6. You can add arithmetic operators by positioning your cursor in the appropriate place within the formula and clicking an operator on the toolbar above the Definition pane.
7. You can validate your new formula by clicking Validate.
8. You apply totals and subtotals on the Subtotals/Aggregation tab in the Metric Editor, shown in the image below:

9. If you have the MicroStrategy OLAP Services product, you can define the function to aggregate values by when users move objects off the report grid and into the Report Objects pane, and vice versa. Select the function from the Dynamic aggregation function drop-down list.
10. On the Metric Editor’s toolbar, click Save and Close.

To create a function within a metric using the Insert Function Wizard:

1. In Desktop, open the Metric Editor. To do this, from the File menu, select New, and select Metric. The Metric Editor opens.
2. In the toolbar above the metric definition pane, click F(x). The Insert Function Wizard opens, as shown in the image below:

3. Browse through the folders displayed in the left and center panes to locate the function you want to use. When you select a function, example syntax and a description are displayed at the bottom of the wizard.
4. Click Next.
5. Depending on the function you selected, you may need to determine arguments for the function.

• An argument is the input value of a function. For example, you can select the Profit fact as the argument of the Average function, to calculate the average profit.

Required arguments for your chosen function are listed at the bottom of the wizard. For each argument listed, type a value or Browse to find an object to use.
6. Click Next.
7. Depending on the function you selected, you may need to define parameters.

• Parameters determine the behavior of the function. For example, the NTile function requires two parameters, Ascending and Tiles. Ascending controls whether the NTiles are ordered in ascending or descending order, while Tiles determines the number of splits

8. Click Next.
9. Depending on the function you selected, you may need to determine a BreakBy parameter for your function.

• The BreakBy parameter restarts the calculation for each attribute or hierarchy designated as a BreakBy parameter. Relative functions require a BreakBy parameter. Examples of relative functions include RunningStdevP, Rank, and NTile.

Click Add to select objects to use as BreakBy parameters for your function.
10. Click Next.
11. Depending on the function you selected, you may need to determine a Sortby parameter for your function.

• The SortBy parameter determines how objects are sorted for the calculation, and is required for various functions such as First, Last, RunningSum, and FirstInRange.

Click Add to select the objects to include as SortBy parameters for your function. Then use the sort order arrows to modify the order in which the objects should be used in the calculation.
12. Click Finish to enter your new function into the metric’s expression

To enable totals or subtotals for a metric:

1. In Desktop, double-click the metric for which you want to enable a grand total or subtotals. The metric opens for editing in the Metric Editor.
2. Click the Subtotals / Aggregation tab.
3. To enable a grand total, from the Total subtotal function drop-down list, select the function to use when the metric displays a grand total on a report.
4. To enable one or more subtotals, from the Available project subtotals list, select the functions to be available for the subtotal calculations. Click > to move your selections to the Available subtotals for metric list.
5. Click Save and Close to save the metric with its newly enabled total and/or subtotals.

To remove a grand total or subtotals from a metric:

You may want to remove, or disable, a grand total or subtotals for a particular metric. For example, if the metric counts inventory numbers, subtotals may be irrelevant and should therefore not be displayed on a report.
1. In Desktop, double-click the metric that contains the subtotals you want to remove. The metric opens for editing in the Metric Editor.
2. Select the Subtotals/Aggregation tab.
3. To prevent a grand total from appearing when the metric is used on a report, from the Total subtotal function drop-down list select None.
4. To prevent any subtotals from appearing when the metric is used on a report, select any subtotals in the Available subtotals for metric list and move them to the Available project subtotals list.
5. Click Save and Close. When this metric is used on a report in the future, the removed totals or subtotals cannot be displayed as part of the report.

To format metric values or headers in Desktop:

1. In Desktop, double-click the metric you want to format. The metric opens for editing in the Metric Editor.
2. On the Tools menu, point to Formatting, and then choose either Headers or Values. The Format Cells dialog box opens.
3. Format the metric header or values as desired. Click Help to see details on all the options you can use to format report headers and values as you desire.
4. Set the number formatting for the metric. Click Help to see details on all available options.
5. Format the alignment, font, border, background, or chart options as you desire. Click Help to see details on all the available options.
6. Click OK to save your selections and close the Format Cells dialog box.
7. Click Save to save the metric with your new formatting.

To create a custom metric format in Desktop:

1. In Desktop, double-click the metric you want to format. The metric opens for editing in the Metric Editor.
2. On the Tools menu, point to Formatting, and then choose Values. The Format Cells dialog box opens.
3. On the Number tab, select Custom.
4. Enter your custom format syntax in the drop-down field. Click Help to see a table of formatting symbols you can use and examples you can replicate.
5. Click OK to save your settings and close the Format Cells dialog box.
6. Click Save to save the metric with your custom format.

To filter data based on business attribute elements:

1. In Desktop, on the File menu, point to New, and then choose Filter. The Filter Editor opens.
If the New Filter dialog box is displayed, click the Empty Filter icon. If you do not want this dialog box to be shown in the future, select Don’t show this dialog in the future. Click OK. For more information about Object templates, see the Designing Reports chapter of the MicroStrategy Advanced Reporting Guide.
2. Double-click in the Filter definition pane at the top right. The Filtering Options pane opens.
3. Select Add an Attribute Qualification and click OK. The Attribute Qualification pane opens.
4. Select the attribute that has the elements on which you want to qualify, in other words, the elements that you want to filter the report’s data for. You can do this in one of the following ways:

• Type the name of the attribute in the Attribute field and click OK.
• Browse by clicking … (the browse button) to locate the attribute. In the Open dialog box, navigate to your project’s attributes folder, select the attribute, and click OK.
• Drag the attribute from the Object Browser into the Attribute field.

The Attribute Qualification pane expands, similar to the image below:

5. Create the list of elements which the filter will use to filter data. To do this, from the Qualify On drop-down list, select Elements.
6. From the Operator drop-down list, you can select either In list or Not in list:

• In list lets you define what attribute elements the filter should include data for.
• Not in list lets you define what attribute elements the filter should exclude data for.

For this example, select Not in List.
7. To create the list of attribute elements, click Add. The Select Objects dialog box opens.
8. In the Available objects list, select attribute elements, and then click > to add them to the Selected objects list.
For this Tutorial example, select April, May, and December.
9. Click OK to close the Select Objects dialog box.
10. Click OK to close the Attribute Qualification pane.
11. Click Save and Close on the Filter Editor toolbar. The Save Filter As dialog box opens.
12. Navigate to a project folder in which to save the filter, and give the filter a descriptive name so you or other users will be able to use the filter on other reports in the future.
For this Tutorial example, name the filter Month and save it in the My Objects folder.
13. Click Save.

Creating a filter based on another filter

This procedure provides instructions to create a simple shortcut-to-a-filter qualification. For more details on any of the options within the procedure, click Help for a detailed description of each option.

To create a filter based on another filter:

1. In Desktop, from the File menu, point to New, and then choose Filter. The Filter Editor opens.
2. Double-click in the Filter definition pane at the top right. The Filtering Options pane opens.
3. Select Add a Shortcut to a Filter and click OK. The Shortcut to a Filter pane opens. The image below shows the pane after a filter is selected.

4. Select a filter using one of the following methods:

• Type the name of the filter in the Filter field.
• Browse by clicking … (the browse button) to search for the filter. In the Open dialog box, navigate to the folder that contains the filter, select the filter, and click OK.
• Drag a filter from the Object Browser into the Filter field.

5. Click OK to close the Shortcut to a Filter pane.
6. Click Save and Close on the Filter Editor toolbar. The Save Filter As dialog box opens.
7. Navigate to a project folder in which to save the filter, and give the filter a descriptive name so you or other users will be able to use the filter on other reports in the future.
8. Click Save.

Joining filter qualifications with operators

When a filter has multiple qualifications, they are always joined by operators. When qualifications are joined, operators govern the interaction between different filtering conditions and thus affect the evaluation order of qualifications in a filter.
Whenever you have more than one qualification in a report filter, you can define the operator as any of the following:

• AND
• OR
• OR NOT
• AND NOT

Creating a shortcut group

Shortcut icons are displayed within groups, called shortcut groups. By default, the Shortcut Bar contains two shortcut groups, namely Tutorial Shortcuts and Other Shortcuts.
To create a shortcut group:
1. Right-click an empty area of the Shortcut Bar and select Add Group.
2. In the Create a new shortcut group dialog box, type a name for the new group and click OK. Your new shortcut group appears in the Shortcut Bar.

Adding a shortcut icon to a group

When you want to create a new shortcut to a commonly used folder or project, you must create the shortcut icon within an existing shortcut group.
To add a shortcut icon to a group in the Shortcut Bar:
1. On the Shortcut Bar, click the shortcut group to which you want to add your new shortcut. Any shortcuts which are currently in the group appear in the Shortcut Bar.
2. In the Folder List, right-click the project or folder for which you want to create a shortcut, and select Add to Shortcut Bar. A new shortcut icon appears in the Shortcut Bar.

Removing a shortcut icon from the Shortcut Bar

To remove a shortcut icon from the Shortcut Bar:
1. Right-click the shortcut and select Remove from Shortcut Bar.
2. In the confirmation dialog box, click Yes.

Removing a shortcut group from the Shortcut Bar

If you remove a group that has existing shortcut icons within it, the shortcut icons are deleted also.
To remove a shortcut group:
1. Click the shortcut group you want to remove. The shortcut icons which are currently in the group appear in the Shortcut Bar.
2. Right-click an empty area of the Shortcut Bar and select Remove Group.
3. In the confirmation dialog box, click Yes to remove the shortcut group. All shortcut icons within this group are also removed.

Renaming a shortcut icon or shortcut group

By default, the name of a shortcut icon is the same as the name of the folder or project for which you created the shortcut. You can rename any shortcut icon by right-clicking it and selecting Rename Shortcut.
To rename a shortcut group:
1. Click the shortcut group you want to rename.
2. Right-click an empty area of the Shortcut Bar and select Rename Group.
3. In the Rename a shortcut group dialog box, type a name for the shortcut group and click OK. The shortcut group is renamed.

MicroStrategy Desktop interface

The MicroStrategy Desktop interface has three panes:

• Folder List: Where all the project folders that hold your reports and report-related objects are accessible.
• Object Viewer: Where the contents of each folder, such as reports or report objects, are displayed as you browse through folders in the Folder List. The right pane of the MicroStrategy Desktop interface is the Object Viewer.
• Shortcut Bar: This pane contains icons that allow you instant access to your favorite or most frequently used folders. Simply click on a shortcut icon to jump immediately to the folder to which it is linked.