MicroStrategy Report Features Training: Graduate Admissions Data/Reports ITS Business Intelligence Group Revised: June 10, 2015 Welcome to MicroStrategy Report Manipulation Training. This training will teach you some additional functionality of MicroStrategy and by the end of the class you will be able to transform existing MicroStrategy reports.

Contents Sample Report........................................................................................................................................... 2 Types of Reports ....................................................................................................................................... 2 Run Report ................................................................................................................................................ 2 Re-prompt ................................................................................................................................................. 3 Switching Views ........................................................................................................................................ 4 Grid header: .......................................................................................................................................... 4 Graph view: ........................................................................................................................................... 5 View Filter ................................................................................................................................................. 5 Prompt Details .......................................................................................................................................... 7 Page By ...................................................................................................................................................... 7 Sort .......................................................................................................................................................... 10 Subtotals and Totals................................................................................................................................ 10 Exporting ................................................................................................................................................. 13 PDF ...................................................................................................................................................... 13 Excel .................................................................................................................................................... 14 Report Objects v All Objects ................................................................................................................... 15 My Reports .............................................................................................................................................. 18 Setting Preferences ................................................................................................................................. 21

1

Sample Report In order to learn how to manipulate a report, first we need to run a report. Login to MicroStrategy, access the Academic project, then click on Shared Reports. We will navigate to the Term Comparison – by Location report:

Types of Reports In this folder there are various reports and subfolders.

This symbol denotes a grid report:

- Denotes a GRID report This symbol denors a dashboard:

- Denotes a dashboard

Run Report Click on ‘Term Comparison – by Location’ and once you see the prompts for this report, run it for:    

1. Term: ‘201570:Fall 2015.’ 2. Comparison Date: ‘9/1/2014’ 3. Application Status: Leave as is (C, D, I) 4. Graduate or Undergraduate: check ‘Graduate Admissions’

2



 

5. College 1: If you are Master Fund/Master Org or have access to multiple Orgs, choose one college from your list of potential colleges. If you only have access to one college ensure that it is displayed Leave all other prompts blank Run the report

Re-prompt If NO DATA are returned (or if you want to change your prompt answers): On the top menu, click Data, then click Re-prompt (highlighted below). Choose a different Comparison Date and rerun the report

3

Switching Views This report is a grid report. To change the display from graph to grid, click on the grid symbol (highlighted in yellow below).

Obviously it depends on your data and its organization on the grid as to whether or not a graph view would provide any usefulness. Grid header:

4

Graph view:

Switch the report back to a Grid view by clicking the Grid symbol.

View Filter Prompts and filters limit the data returned as the report runs. The View Filter allows the user to further filter the data after the data are returned. The View Filter does not rerun the report query and does not go back to the database; that means oftentimes adding a View Filter is faster than re-prompting and rerunning a report.

Oftentimes the View Filter is not automatically shown when you run a report. To turn the View Filter on or off, click on Tools, then check View Filter.

To add a View Filter, click on Add Condition.

5

Now click on the down arrow next to Filter On. You will now see report-specific elements such as App Dept, App Level, App Status, College, etc. Select ‘Degree’. You will now see a list of all Degree values. You can either select individual items (in this case, such as DA: Doctor of Arts, MA: Master of Arts, MAIS: Master of Interdisciplinary Studies, etc.) or you can Qualify on Degree.

Click the Qualify radio button. Where you see ID, click the word ID and change this to DESC. Where you see Equals, change this to Like. In the empty box next to ‘Like’ type ‘%Master%.’ The % symbol is the wild card for MicroStrategy and the View Filter IS case sensitive. (E.g. if you type in ‘master’ with a lower case ‘m’ this will not work). Your View Filter should look like below.

Click Apply. Your data should now be filtered to just Degree data with including the word ‘Master.’

6

Report Details Let’s look Report Details. This may or may not show automatically when you run your report. To turn Report Details on/off, go to Tools, then click Report Details.

Report Details shows: prompts and prompt answers, report filters, and the view filter we just created.

Page By Page-By allows you to group your grid data by dragging report attributes into the Page-By section. Click on Tools, then click Page-by Axis.

7

You will then see a small, new section below Prompt Details.

Look at the left hand pane: It may show you All Objects or Report Objects. If it’s showing All Objects, select Report Objects from the bottom left hand corner:

Now you should see all Report Objects (attributes, metrics, hierarchies, etc.) available on this report. This report contains ten attributes (App Conc 1, App Deg 1 Lvl, App Dept, etc.), and a variety of metrics and functions.

8

You can either drag an element from the grid or from the Report Objects section onto Page-By. Let’s drag Degree from the grid up onto Page-By. Click on the blue and white Degree title and drag it into Page-By. You have found the right placement when a bright yellow band appears around the Page By

Now you see that Degree has disappeared from the grid and is located in the Page-By instead.

Select Master of Arts (or whatever degree is shown) to change the Degree on display to a different Degree. Select Master of Education. Notice at the bottom of the Page By list there is ‘Total.’ Click on Total to see all Degrees at once. Let’s drag an element from the Report Objects pane into the Page By area. Select ‘State’ and drag it into the Page By area. Since we have Total selected for Degrees, State is automatically set to Total as well.

Try to select a State while Degree still reads Total. You can’t do this: you have to select Page By elements from left to right. So select Master of Science (or any value in Degree). Notice that State now reads VA or OOS. Total is not available for State – we can change this by editing Subtotals, which we’ll do later.

Drag State and Degree back onto the Grid like shown to clear the Page By:

9

Sort On the top Toolbar click Data, then Sort. MicroStrategy automatically sorts left to right within the columns of data. On this screen you can add or subtract different levels of sorting to your report. This report already has four sort levels:

Let’s remove the sort on State. Click the down arrow next to State. Then at the top of the dialog box click into the blank row. Let’s change the sort on Degree to be Descending.

Then click Apply, then click OK.

Subtotals and Totals Many of our Mason reports use a standard color scheme and subtotal/total layout. But say there is a report you need to add subtotals and totals to. Click on Data, then Show Totals. This particular report already has Show Totals turned on. But we can further edit the totals displayed. Click Data, then Edit Totals.

10

The first tab, Definitions, lists general Total attributes. Here you could add algebraic functions to your totals.

Click on Advanced tab. Remember when we added attributes to the Page By and we automatically saw a Total option for Degree and State? The only way that Total is an option is because of this Advanced screen: the Across Level checkboxes for College, Degree, App Dept are all checked. When we had Degree (with Totals turned on) in the Page By, State automatically had totals, too. Currently State is not checked: check State, then click Apply, then OK. 11

You now see an additional Total row on the grid in the State column of data:

Drag State into the Page By. You will see VA, OOS, and Total as selection options.

Now click Data, Edit Totals, Advanced tab. Uncheck State, then click Apply, then OK. Now try the Page By again: you will only see VA and OOS as options as we turned off totals on State. Leave State in the Page By. If you want to turn off ALL totals, click Data, then uncheck Show Totals.

12

Exporting Data from MicroStrategy reports can also be exported in Excel or .pdf format. Click Home, then Export.

You will then see an Export menu. You can choose PDF, Excel with plain text (no formatting), CSV file format, Excel with formatting, etc. PDF Choose PDF. You will then see a menu of options:

Let’s select to Print Cover Page with Report Details and Orientation to be Landscape. Click Export. In Chrome the PDF displays right in a new browser window. The first page shown is the Report Details, and the data start on the second page. You can then Save or Print this PDF per your browser menu options.

13

Close this tab.

Excel You should now see the Term Comparison – by Location report again. This time, choose Home, Export, Excel with formatting.

You can choose to include or exclude filter details as well as a few other options. Because we have an element (State) in the Page-by there is an additional neat feature we see: if it is not already checked, click Export Page-By information and expand all Page-by fields. This will place each Page-by element (State, in this case) on a different Tab in Excel.

- Sample of States on separate tabs in Excel 14

Click Export. Depending on your browser the Excel file may open differently. In Chrome you’ll see a Report Ready message in the main browser pane and a small Excel icon in the lower left hand corner. Click ‘Close window’ to close the Report Ready message and click on the Excel icon to open your data. Report Ready message:

Click Close Window to see the Term Comparison – by Location grid report.

Report Objects v All Objects On the left hand side of the report is the Report Objects pane with all attributes, metrics, derived elements, functions, etc. included in your report. But there are many, many other elements in one MicroStrategy project than can fit (or should be included) on one report. If you need to add an element to a report, you need to access All Objects in the lower left hand corner of your screen.

Click All Objects and it will replace Report Objects:

15

All Objects are organized into folders by element type:     

Public Objects – contain elements such as metrics, prompts, and filters Schema Objects – contain elements such as attributes and facts My Personal Objects – if you build custom metrics (taught in further, specialized MicroStrategy training) they will be located here Attributes – this is a short cut direct to the Attributes folder. Attributes are typically descriptive elements on your report (such as Name, College, Address, Degree, etc.) Metrics – this is a short cut direct to the Metrics folder. Metrics are typically numeric, counts, sums, and so on

Click on Attributes. Attributes are further organized into ‘Applicant’ and ‘Application’ element folders.

Click on Applicant. Now you see many subfolders of Applicant attributes.

16

Click on the App Prior Institution, then Applicant Prior Institution folders, then drag PC Degree onto the Page-by.

You can now use the Page By to filter the resulting data by State and PC Degree. Click the small green up arrow to move up folder levels, from the App High School folder to Application.

Click the drop down arrow next to Application and select Metrics

17

Academic project Metrics are organized by Applicant or Application.

You can click on a folder to see and add additional metrics to this report.

My Reports Oftentimes you will manipulate a report but won’t need to save it. Other times, you will want to save a copy of your report. Click on Home, then Save As.

If the Save in: box does not read My Reports select and navigate to My Reports.

18

Give the report a new name. Leave the checkbox ‘Keep report prompted’ checked so that you will be prompted to answer the prompts each time. Also, leave the ‘Save report as prompted’ checked and make sure the radio button ‘Filter and template will be prompted’ is checked. This will ensure your current prompt answers will be discarded so you can run the report fresh next time.

19

You will then see two choices: ‘Return to original report’ or ‘Run newly saved report.’

Cilck ‘Run newly saved report.’ You will be prompted to answer the prompts. Once the report has finished, click on the red MicroStrategy starburst, then select My Reports.

20

You should see your newly created report in My Reports.

Setting Preferences Users can also define various project-level settings through preferences. These options include:       

General Folder browsing Grid display Graph display Export Reports Print Reports (PDF) Etc.

21

To access Preferences, click on the MicroStrategy starburst, then click on Preferences

As an example, click on Export Reports to see all of the options available when exporting data to Excel.

22