Mar 30 2012
Drilling to Details
One of the frequent requests that I see goes something like this:
I have a hierarchy set up. When I drill to the bottom of the hierarchy, I want extra detail objects to show up.
This seems like it should be the default behavior, right? If I take the time as a universe designer to properly classify objects as dimensions or details, and also take the time to set up hierarchies, then it would seem that reports would recognize and utilize that information.
Unfortunately they don’t. That means I have to use some report functions and set up some variables and do some creative formatting to make it work the way I think it should. I first showed how to do this with Desktop Intelligence way back in 2000 at the Business Objects conference in Washington D.C. Today I will update the technique and show how it can be done in Web Intelligence.
The Problem Defined
In the eFashion universe I have a dimensional hierarchy from State to City and then to Store Name. Once I get to the store name I see two additional details for Zip Code and Address. In this particular scenario I am going to set up a report that starts at the state level and allows me to drill down through the city and then to the store name. When I get to the store name, the address is magically going to appear. As with most magic, the solution requires some misdirection.
Building The Query
Building the query is very straight forward. I will include the dimensions I need, the Address, and a measure (Sales revenue) just to make the report more interesting. I am not going to invoke drill mode from the query panel as I will do that later on the report. When I run the initial query I see a standard report block.
To prepare of the next step I am going to remove all of the objects except for State and Sales revenue.
Address Variable
The misdirection that makes the magic work for this trick is a special variable for the store address. It turns out that the address will be on the block the entire time, but it will only be visible after a user has drilled down to the store level. Here’s the formula that makes that work:
=If DrillFilters([City])<>"" Then [Address]
Remember that the hierarchy is from State to City and then to Store Name. Until I have selected a City value, the DrillFilters() function as coded above will show the empty string. Once the user has drilled down to the City, this formula will return a non-empty result and the Address will be displayed. Until then the return value from this formula is null.
I am going to first show what the results look like, and then explain the formatting choices I made to help complete the illusion.
Top level block after invoking drill mode:
After clicking on Texas:
After clicking on Houston:
See how the address magically appears? 🙂 As I said earlier, the address was there all the time. Here is how that trick is managed behind the scenes.
Hide In Plain Sight
The address variable (called “Show Address”) uses the formula posted earlier to show or hide the value of the address based on where the user is in the hierarchy. The variable exists on the report all the time, and is positioned as the second column in the report. There are two ways to approach the next step. I could create an alerter that is driven by the same logic that dynamically updates the cell format, or I can apply the format changes permanently on the report. I am going to show the second solution. The first adjustment is to set the address column so that it autofits the width.
When the cell is empty it will shrink down to about 4 pixels, which seems to be the minimum setting allowed. But the cell still has a border so I need to address that as well. There are two parts to this step. First I remove the left border on the address column (both the data and the header). Next I remove the right border from the first column in the report block. That ensures that there is no border at all between the first two columns. Again this is done on the header cell as well as the report body.
In my sample report I don’t have a header on the address. I will leave it as an exercise for the reader to create a variable that will show or hide the column header based on the same logic. 😎
Great use of the drillfilters function. Very sneaky. Could you use the same approach with the block visibility options in 4.0?
I haven’t had a chance to play with 4.0 as much. What little I have done has been … disappointing, to say the least. I have not had time to see if I could recreate this technique yet.
Hi Dave,
In BO 4.0, Webi has an option in Format Chart, something like Hide block that Deski had. I think we can use the formula with drillfilters and do it easily.
Thanks
M Mohammed
Hi Dave,
I had a similar req in my project,Used your technique,it works perfectly fine.
Thanks 🙂
Regards,
Deepa
Dave,
If I have four queries, can I have drill down from one query to the next, next next etc…
That’s not how drilling works in Web Intelligence. If you need to link from one data provider to the next, you should look at the OpenDocument() function, or perhaps the new element linking feature.
I have a doubt here..I could implement the steps given above however i did not understand how storename is replaced by address because in my report address displays as a third column.In short storename,address,sales revenue.
Am I missing anything over here?
The address column is always present as the third column in the block. But it only has a value when the drill filter function returns the appropriate level. At any other time the column is blank.
Good usage of drillflter function
Nice explanation using Drill Filters Function…
Thanks,
Dave
Dave,
You Rock! I was able to build a drill down report only after reading your web page, minutely.
But where the hell do you find such ideas ? 😉 Thanks for your GREATS tips and tricks, help a lot in a BI analyst/developper daylife !
great tip for drilling functionality in webi. Thank you for your insights, always helpful.
Any ideas on how this could work when the column introduces multiple values to get it to add rows to the table to handle those multiple values?
If there are multiple values, then they’re not truly details. 🙂 One of the defining elements of a detail is that it has only one value. It gets confusing sometimes because the restriction is not applied from detail to dimension, but only from dimension to detail. Let me explain with an example.
A clothing store, let’s call it eFashion :), might offer a line of sweaters. One of the attributes of a sweater is a color, and each SKU (unique identifier) has exactly one color. In this case, color is a detail of SKU. But at the same time there is nothing unique about the color red. I can have red sweaters, red skirts, and red shirts. So “red” as a detail value belongs to multiple parent dimensions, but each parent item (SKU) has exactly one color.
Bottom line: you should not have multiple rows of details for a parent dimension value, and if you do, they’re not proper details.
Hi Dave,
Thanks for your useful posts and clear explainations on Business Objects.
I have few questions on Drill of BO.
1) I have built Hierarchy Country-> State-> City at the universe level. When I am creating a report i select objects – state, product_name, sales_amt
When the report is generated data is fetched from DATABASE and report is displayed. Now when I apply a drill on State and move to Country or City
from where the data for Country OR City is fetched? IS it from microcube or database?
2) Is there any disadvantage of Drill feature?
3) When I build a hierarchy for a class say Time how the other classes and objects will act? i.e. When a custom hierarchy is built is it applicable for whole universe classes and objects or only for that class?
4)When a dimension belongs to a hierarchy used in the query is the data of all hierarchy dimensions come and resides in the microcube or only the data for that dimension comes into microcube?
Please send your answers for the above queries.
Thanks,
Ramanathan S
Hi Dave,
I have two blocks in my report each containing year dimension.
When I apply Drill on year dimension on one block the other block should also drill to the same level.
Is this possible in BOXIR3.1 version?
Regards,
Ramanathan S
There is a “synchronized drill” feature that was introduced some time back, but if I remember it was fairly useless as it was a user profile feature rather than something that could be set document by document.
This is working as expected when I drill down from State to City to Store name but when I drill up from Store name to City it doesn’t seem to work.
Hi Dave,
Many thanks for this article. However, there is a significant flaw in that it will not work if you skip any levels in the hierarchy. For example – if you want to drill from the top level to the bottom level of a hierarchy and then show the corresponding attribute value, it will not work because there is no value in the filter for the preceding (parent) hierarchy level. This results in the wrong attribute being shown for a dimension.
The only way to get around this with the current functionality is to concatenate the dimension and attribute values in the unx. But this defeats the purpose of having attributes, so SAP need to address this design flaw.
Thanks
tomocb
Hi, Tom, your comment was something to think about. I haven’t tested it, but it would seem that it should still work? 😕 In my example, the city is the bottom level of the hierarchy. I would not think that it would matter if you drill through the standard hierarchy, or drill through directly to the bottom, the drill filter value for “City” should still have a value at the bottom level of the hierarchy. But it sounds like you have tested it (and I have not) so for folks using this technique with skip-level drilling, be advised that it may not work for you. Thanks for sharing your experience.
Hi Dave,
Many thanks for this article.
We have the same requirement to show details, but because the data is very huge we cannot use Scope of Analysis and instead have to use “Query Drill” on report. The solution does not seem to work there
Please advice if there any other sneaky way for showing details using query drill?
Thanks
Gurbachan
Hi Dave,
I have an issue with the drill function in Webi report. We have a Geographical Hierarchy from Region>County>Admin>Unit>Worker. This seems to work fine when we drill down all the way to Worker level but when we drill up, we experience “#multivalue” and “N/A” error in the report. This happens only with some users. For other user it works fine both the direction. Initially thought of access issue but everyone are in the same group with same access level. If it had any data issue, then it would be the problem while drilling down itself.
Any Idea, why this report is behaving such just for some users?
Thanks,
Chan
Hi Dave,
I am facing a strange issue. I hope i can find the solution in this forum.
I have a report with three tables. table A is horizontal table and table B and table C are vertical tables.
My requirement is when you clikc on the values of the table A(values are 1,2,3,4,5), we should see relevant data in table B.
I achieved it by using the element linking and it’s working fine.
Now table C is also added in the element linking.
so far so good.
But I have drill down in table C. So I need to enable drill option in report. Then the element link is gone. Once you disable the drill option in the report, then the element linking is coming back. crazy.gif crazy.gif
I need both to be there in the report.
How can I achieve this?
Regards
Mahesh Gadde
Hi, Mahesh, I’m guessing (without actually experimenting) that the two features are simply incompatible. Both require capturing the “on-click” event, one to drill and the other to link. They probably won’t work together.
Hi Dave,
Thanks for your reply. looking forward to find a solution.
Regards
Mahesh Gadde