In our "Hello World" document we used a string constant to greet our users. While some of them may find this polite, it is bluntly useless; now it is time to give some more information, and we will add a report to the document to display real numbers.
When creating documents we speak about datasets; a report is a dataset, a graph is also a dataset, and we said that a document can be based on more than one dataset.
Before going further, I would like to clarify a point and give my own point of view: the document datasets do not have to be pre-existing reports. We can build them specifically for the sole purpose of document creation. And it is perfectly acceptable: let me explain.
There is something that happens every now and then in MicroStrategy projects. First, we create reports (many of them, with different levels of aggregation and filters); then we try to fit as much information as possible into a document. In the case of small sets of data this is usually not a problem, but with millions of rows, the result can be suboptimal performance. By suboptimal I mean that your users would not receive data in a "Go grab a cup of coffee" timeframe.
Putting many datasets into the same document causes extra work to the Intelligence Server, because it has to join every single one using common attributes and produce a resulting ultimate mega dataset (called virtual dataset). If there are no attributes in common between two or more reports, the ultimate mega dataset would contain a Cartesian product, which we want to avoid.
Speaking about the Cartesian product, I always remember a scene from the 1984 movie Ghostbusters:
Dr. Spengler: "There's something very important I forgot to tell you."
Dr. Venkman: "What?"
Dr. Spengler: "Don't cross the streams."
Dr. Venkman: "Why?"
Dr. Spengler: "It would be bad. […] Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light."
Dr. Stantz: "Total protonic reversal."
Dr. Venkman: "Right. That's bad. Okay. All right. Important safety tip."
So, in order to avoid the total protonic reversal in our document, we may consider creating a single dataset with only the columns that we will effectively use, not more not less, Dr. Spengler docuit.
We start by creating an ad hoc report for the next document. This report has Product Category
, Product Subcategory
, and Sum SalesAmount from FactInternetSales
. Save the report as 26 InternetSales by Product and Subcategory
.
Once you have the report created (no totals needed) you can follow these steps:
Total
. Make it Bold too.27 InternetSales by Product and Subcategory
, then click on Run newly saved document.If you scroll through the results, you'll see that there are 17 rows plus the total. The report granularity is at subcategory level, this means that the Detail section is repeated for each row of the dataset and the Detail Header only appears once. Just as expected.
Another behavior is worth noting: the row in the Detail Header shows a grand total. This is how metrics work: if placed in the detail section, they show a single row value; put them in a header and you'll get an aggregate. This is very important to remember.
You can edit documents in both the Web and the Desktop application. I am used to doing it in Web because I can see immediately how it looks like; but if you feel more comfortable you can create and modify the documents just as well with the Windows client. Try it yourself: