Drillthrough with multiple tables

Hi,

I'm having a bit of trouble with drillthrough, hope someone can help.

When the user drills through, I would like them to retrieve some records that are located in another table - not in the fact table.

E.g. if they drill down on one record of with a data value of 100, they should see 4 related source records that reside in another table.

At the moment, when the user drills down, they always see the entire contents of the other table. Of course it should only show you the records that apply to the value you're drilling on.

I've tried several things, setting up a relationship in the cube schema, and setting up a relationship in SQL Server between the two tables. Either way I always get the whole table back.

Can anyone help

Thanks
Jeremy

If you're talking about SQL Server 2005, then see the MSDN whitepaper http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp. It has an example of drillthrough to an alternate fact table.

If this is SQL Server 2000, you can troubleshoot by running Profiler and capturing the drillthrough SQL query issued by Analysis Services. Maybe its not joining the alternate table with the fact table. Try including a drillthrough column from the fact table as well.