Effective data analysis involves manipulating report data so that you can see patterns in the broader data or subsets of data that are meaningful to
answering your business questions. You can filter, sort, and rank data in a MicroStrategy report in Excel using Excel’s features. Several examples are provided below using sample report data to show you Excel features that can be useful when analyzing data in a MicroStrategy report in Excel.
The Microsoft Excel examples in this section may not reflect the functionality of the most current release of Microsoft Excel. For detailed steps to use Excel features, see your Microsoft Excel documentation.
© 2013 MicroStrategy, Inc. Analyzing business data and working with Excel workbooks 117
Example: Creating reports and slicing-and-dicing using Excel PivotTables and PivotCharts
Excel PivotTables and PivotCharts let you perform a wide variety of
analytical tasks such as sorting and filtering data. You can slice-and-dice the reports and data to uncover and highlight specific report data. You can take advantage of Excel’s PivotTable and PivotChart functionality to create your own custom reports from the MicroStrategy data in the workbook.
A MicroStrategy report must be executed as a PivotTable or PivotChart before you can analyze and work with the PivotTable or PivotChart in Excel.
For steps to run MicroStrategy reports in different formats, see Specifying a display type in which to view reports, page 63.
118 Analyzing business data and working with Excel workbooks © 2013 MicroStrategy, Inc.
For example, the Customer Sales by Age report below was run in an Excel workbook as a PivotTable. Notice that the column headings have drop-down lists and the PivotTable menu opens by default once the report is executed.
© 2013 MicroStrategy, Inc. Analyzing business data and working with Excel workbooks 119 In the PivotTable, you can add individual elements to the table. The Quarter and Age 40-59 columns were dragged from the PivotTable menu to the PivotTable, adding data to the report.
120 Analyzing business data and working with Excel workbooks © 2013 MicroStrategy, Inc.
You can format the look of your report data by selecting a new Excel report format, as shown in the image below.
You can also create a PivotChart from the PivotTable. The PivotChart is placed on a separate sheet in the workbook, as shown in the image below.
© 2013 MicroStrategy, Inc. Analyzing business data and working with Excel workbooks 121 Suppose you want to see only bar graph data for Sum of Age 19 and Under and Sum of Age 20-39. You can hide specific data from view, as shown in the following image.
In the image below, the bar graph PivotChart displays only the data specified.
Example: Ranking and reducing result sets using Excel’s auto-filtering functionality
You can filter and rank your report data in a number of ways using Excel’s auto-filtering. This is particularly beneficial with large datasets.
Use the following steps to auto-filter and analyze MicroStrategy report data.
122 Analyzing business data and working with Excel workbooks © 2013 MicroStrategy, Inc.
To rank and reduce result sets using Excel’s auto-filtering with MicroStrategy report data
1 Open a Microsoft Excel workbook containing a MicroStrategy report. The New Hires by Division human resources report is used in these steps as an example.
2 Highlight the entire report result set. Then, from the Data menu, select Filter. Drop-down list arrows appear next to each column in the report, as shown below. Click a drop-down arrow to filter and rank that column’s data.
3 Choose the filtering criteria. You can filter data based on specific elements, rank data, or use filtering comparisons such as greater than,
© 2013 MicroStrategy, Inc. Analyzing business data and working with Excel workbooks 123 less than, and between. In this example, the Employee column is ranked so that only the top five values are visible, as shown below.
The resulting report shows a concise subset of data that can provide a more meaningful picture of your department or organization than the full report showed.
You can ensure that the AutoFilter dialog box opens automatically when reports are executed in a Flattened display. To do so, on the MicroStrategy tab, click Options. Under the General folder, expand the Grid folder, and select the AutoFilter flattened reports check box.
Example: Adding new metrics and calculations to report data While analyzing report data, you may wish to add new metrics, calculations, and data based on the existing MicroStrategy report in the workbook. You can use Microsoft Excel to add new metrics and calculations to existing MicroStrategy reports in Excel.
124 Analyzing business data and working with Excel workbooks © 2013 MicroStrategy, Inc.
For example, based on the data in the report, you may wish to add a standard deviation calculation to gain further insight into the data. Or, you may be interested in seeing a Percent to Total calculation.
After adding new calculations and metrics, you can save the workbook, refresh it to see the latest data from your data source, and the additional calculations outside of the report cells are retained.
Any calculations you add to MicroStrategy reports using Excel are not saved in MicroStrategy. They are saved on your hard drive, within the workbook itself.Refer to your Microsoft Excel documentation to add new calculations to workbooks and the proper formulas to use for each.
© 2013 MicroStrategy, Inc. 125
4
4.