Answers: Scenario2: Change prompted column on drill - 10G

So the requirement is like:

You have a column A, Column B both belonging to same Table and are shown in the same report. 

NOTE: This has to be done in 10g, in 11g it can be done in Action links.

Level1 Report:

Dim1 - ColA - ColB

On Clicking ColB, I have to navigate to Level2 Report, however, normal behaviour is, Level2 Report is filtered on ColB based on the value clicked.

Level2 Report

Dim2 - ColA - ColB

However current requirement is.. Level2 Report to be generated with a filter on ColA = value of ColB clicked in the above Report.

Solution:

Create a Dummy report with just ColB.

Dummy Report:

ColB

Filters-

ColB is Prompted

Now for Level2 Report:

Filters-

ColA -> Advanced Filter -> Equal to Results of Dummy Report (ColB).

Place Dummy report and Level2 Report on the same Dashboard page(in different sections) and navigate Level1 Report to this dashboard page.

Hide the Dummy report using Guided Navigation. 

There might be other ways of doing it, please feel free to post in the comments.