Drill Through to New Report from existing Drill Down/Up Crosstab

[adam_mc]

My question is about the source Crosstab report.

I have a drillable Store Hierarchy (Region, District, Store) that I have drill down/up working as expected.
My requirement is to now add drill through functionality to a second report.

If I have not drilled down at all (ie. [Store Hierarchy] Level) I would want to pass no parameters, if I am at Region Level I need to pass the current Region number that I "click" on, and so on with District & Store.

What is the best way to do this?
Do I need a new element/column to perform this "click" on (to distinguish between drill down and drill through)?
And how do I best identify the level at which I am already drilled down to?
I have tried adding an additional calculation similar to RoleValue('_levelNumber',  [Store Hierarchy]) but this does not appear to be working.

Any help would be greatly appreciated.
Thanks in advance,
Adam.

[pricter]

I do not have a report studio in front of me to test it but I imagine the following would work

1) Unlock the row
2) Place an image
3) Define a drill through on the image

In such a way the user would be confused if there are drilling down or drill through

[bdbits]

A drill-through will override drill-down on a left-click, I just tried that, but they can still right-click the column and choose up/down. It may be a better user experience to have a separate column for displaying an image to indicate going to the drill-through report, and still use clicking on the value to drill down.

Alternatively, for cubes (not sure on DMR), you can create a package-level drill-through definition. This is very flexible in that you can create one or a few detail reports to handle drill-through from multiple tuples on crosstabs, but your target report must be prepared to handle every combination that might get passed to it. This can be non-trivial to build, and is very detailed work (been there done that). However, once built and tested, it does work rather nicely.

You could use a similar concept without a package-level drill-through definition, but I've not actually tried that with drill up/down enabled. What I did instead was create separate reports for each level, then use drill-through to simulate drill-down. If you add multiple drill-throughs, one to simulate drill-down and one for drill-through, the user can right-click and choose the target from context menus, or they will get a list of reports to choose from.

If it was me and I only needed a few drill-through targets but had multiple levels for sources, I would lean toward package-level drill-throughs. This gives the user the most flexibility, in my opinion, and leverages more built-in Cognos features. But effectiveness is going to depend a lot on the details of your scenario.

[MFGF]

Take a look at the thread below:

http://www.cognoise.com/community/index.php/topic,19458.msg57881.html#msg57881

Is this technique relevant to you here?

MF.

[adam_mc]

Thanks MF..

I'd seen this thread and tried it, but it didn't seem to work as I expected (but, this may well be due to my lack of experience).

However, I was able to resolve the problem by adding a text field to my row headers in the crosstab (this didn't seem to affect my measure totaling or my drill-down functionality).
I then used the text field as my drill-through source, and set up the drill-through to pass the whole Store Hierarchy (previously I was attempting to specifically determine what level I was drilled-down to and then pass that as a specific parameter)
In testing, it now passes the value of the hierarchy that I am drilled-down to (ie. The Region, District, or Store number on that particular row).

That just left me having to modify my target report to receive on one parameter instead of three (Region, District, and Store).
I also had to do a little extra modification to my filters to handle when I was at the [Store Hierarchy] level and to treat this as if I want all Regions, District, Stores.

I am sure there are likely better or more sophisticated ways, but this is manageable and relatively easy to maintain.

Thanks again,
Adam.