Rolling-Up and/or Printing Annotations
Posted by Alan on November 27, 2008
In an earlier post I talked about the ins and outs of Annotations. Although I love the concept of Annotations, there are two problems that arise when using them. First, is that there is no way to print Annotations to paper. Second, is that to see an Annotation (or to even know one exists) you have to be at the leaf level of your matrix as there is no roll-up notification that Annotations exist further down your tree.
So how do you get around this? Well it is the answer. Unfortunately I don’t have time right now to do a complete set of screen-shots to describe how to do this and there are a lot of different ways you might want to actually implement this, so you will have to work with my basic descriptions and modify them to fit your unique needs.
Remember that Annotations are text and therefore cannot be stored in the Analysis Services cubes for your model as cubes are numbers only. Therefore, they reside in a straight SQL table in your Application database. The table you are looking for is entitled “dbo.A_YourModelNameHere_MeasureGroup”. Within that table, you will find all your Annotations for that particular Model. Once you get data from this table into Excel you can then print it or tweak how it is displayed in order to let users see Annotations that are buried deeper within your hierarchies.
So how do you get this into Excel? Well go up to the Ribbon bar and select the “Data” tab and then select “From Other Source” from the “Get External Data” section. At that point you can use the wizard that walks you through connecting to SQL Server and when you are done linking to the database table I mentioned above, a grid with your Annotations will be returned to Excel.
What I described above is the “ugly” and “down and dirty” way to do this. For a more elegant solution, you would might look at using a View (instead of hitting the table directly) to limit the fields from the SQL table that are returned. You might also investigate the use of the concept of Parameter Queries in Excel to filter which rows are returned.