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:
Now we start to pivot the data:
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.