When you are creating a custom drill through report, there are some pretty cool things that are possible. One of the things I didn't think was possible but can be done is to use attribute dimensions instead of the root dimension for setting the context for a drill through.
It gives rise to a cool idea that almost was.
Imagine that you have three attribute dimensions on product. Color, Size, Type. It's a paint inventory, so color is the obvious biggie, but you have multiple sizes and spray, tube, can, and pail for your types. You want to select a bunch of paints without getting into shades of burnt sienna, so your high level attribute of color is brown, then you have light brown, medium brown and dark brown. Cool. But to drill down into inventory you don't want to have to pick the product sku. The answer is that you can use one or more attributes to narrow that selection in Essbase, but what about drill through?
We considered the problem for a couple hours with some previous work we had done. One of the cool things we did was design custom parameterized stored procedures that fit nicely into our design for drill through sql templates. For example:
select * from getMonths("Apr","YTD") -> {"Jan","Feb","Mar","Apr"}
With functions like this in mind we figured we could do the same thing with attributes against a target table that didn't actually have the attributes as keys (saving us some time). So the function imagined went something like this:
select * from getProducts("Dark Brown","8gal","Pail") -> {..long list of products..}
So here's the trick. With attributes, users don't necessarily have to bring all of them into the spreadsheet query context. In Smartview, I can bring in only Color, or Color and Type. Of course I could leave attritbutes out. So we figured how about extending the function to handle all possibilities, including those where you select the product outright?
getProducts2($$Product-VALUE$$,$$Color-VALUE$$,$$Size-VALUE$$,$$Type-VALUE$$)
would be the syntax within the SQL template.
Wouldn't it be cool if we could have smart iterations of the combinations drive context legality? So we could build drill through reports that allows you to select any attribute, any two attributes, or any three attributes or any four attributes, or just the product.
Sounds good in theory, except that you absolutely *do* have to know which parameter(s) you are passing from Smartview over to Studio. And that parameter set has to match the context definitions of the drill through. So even though we were thinking SQL NVL so that we could accept nullable parameters, we were constrained.
The other idea that sounded great at the time was that if we made a matrix of possibilities and then designed multiple DT reports with slightly different context definitions, then depending upon which context the user selected, the right report would show up. So for example, Report 3 would be binary 011 which represents notColor, Size, Type. Report 4 would be binary 100 which represents Color, notSize, notType. The problem is that any time you selected more than one parameter you would create legal contexts for several reports instead of just one report. (I leave that proof to you as an exercise).
So right now it seems to be an all or nothing deal, but there may have been something else we overlooked. Still, it did sound like a cool idea, neh?