A little time ago someone posted a question on a MicroStrategy forum; it was something along the lines of: "Do you think that Excel is the ultimate BI analysis tool?".
The topic did not get a lot of attention, but the question was really legitimate and caught my eye. No matter how fast, interactive, user-friendly, eye-candy, and flash-powered your report is, there will always be a manager asking you to e-mail it in an Excel sheet. We cannot win this battle, I have assumed this, and resigned to the workbook vision of the world.
But, what does a user do with a report when he/she finally gets to squeeze it into a spreadsheet?
Nine times out of ten: a Pivot table!
In this recipe, we will learn how we can use rows, columns, and pages to give our report different shapes. Before going on, we need to add the OrderDateKey FK in the FactInternetSales table to the Date attribute (see the screen capture and update the schema):
Next, create a report containing the following objects:
- The Year attribute
- The Product Category attribute
- The Country attribute
- The Sum SalesAmount from FactInternetSales metric
Now we start to pivot the data:
- Hover the cursor on the Year header, when it changes to a cross-arrow, right-click and, from the context menu, select Move | To Page-by.
- MicroStrategy creates a button with the Year attribute that shows 2005.
- Click on the Year: 2005 button and select 2007, the grid now shows 18 rows.
- Hover on the Country header and right-click on it, from the menu select Move | To Columns.
- The countries now appear as columns and the grid shows three rows.
- Go to View | Toolbar | Move to enable the Pivot buttons.
- On the new toolbar that appears, click on the first button Swap Rows and Columns; the grid now shows six rows and the position of the attributes is inverted.
- Save the report as
17 Internet Sales by Category, Country and Year.
Once the data is retrieved from the database, we can move rows and columns and change their order and position; no additional SQL is generated because these manipulations are handled by the Intelligence Server. The page-by feature is useful in case of very long reports to quickly slice data on one or more attributes. Try the different options yourself by moving columns and rows with the toolbar buttons or by dragging the headers.
