Drill Through On Measure Between Dimensions

[tsimkins]

Greetings!

I'm working with Report Studio in Cognos 8 BI.  We have some project cost/revenue data that we would like to present as a drill down/drill through report.  I've modeled the data dimensionally in Framework Manager, and created three dimensions with the following structures:

Project Dimension:  [Organization].[Project Name].[Fiscal Month]
Revenue Dimension:  [Organization].[Project Name].[Fiscal Month].[Customer]
Cost Dimension:  [Organization].[Project Name].[Fiscal Month].[Cost Category].[Cost Type]


There are two measures, Revenues and Costs.  Revenues and Costs are tied to the Organization, Project Name and Fiscal Month.  After that point, they diverge, because revenues can be broken down by customer, and costs can be broken down by category and type.


I have created a report using the Project Dimension, and the Revenue and Cost Measures, as such:

ORGANIZATION	REVENUE	COST
Org1	Org1 Revenue $	Org1 Cost $
Org2	Org2 Revenue $	Org2 Cost $

which gives me the ability to drill down through levels in the Project Dimension.  I would like to be able to create a Drill Through on the Cost column that will take me to the same level in the Costs Dimension, so I can drill down further into the Cost Dimension-specific data (i.e. Category and Type).


In other words, if I were Management, I would be looking at the report, saying "Wow, the costs for this Organization look too high." (click on Organization to drill down) "It's because of this Project" (click on Cost measure column for that Project) which would then take you from the "Project" level of the Project Dimension to the "Fiscal Month" level of the Cost Dimension for that "Project" member.

The closest I've been able to get is drill through at the top level (i.e. Project Dimension Organization to Cost Dimension Organization).  However, I was unable to get the drill throughs working on the lower levels. 

I think what I need to do is somehow pass my position (as in the Member Unique Name) in the Project Dimension hierarchy to the Cost Dimension.  In working with the filters, it looks like it only passes the value for the Organization, no matter what level I drill through from.

I was able to simulate the effect by making one sub-report for each level of each hierarchy, but that is nine reports to maintain, which seems like a messy solution.

I must be missing something, because this seems like something that Cognos should be able to easily handle.


I'd appreciate any thoughts you have.



Thanks in advance!


--Tim