We all know how to build cascade prompt in prompt page, and then pass parameter into main page. However, when you want to make the cascade prompt in main report and allow user to select, it does present some challenges. (By the way, this requirement comes from Security price in Sales credit) If you simple put it into page and the lowest level parameter as required (should be), then you’ll be asked to get values first. What you can do is to make it as optional, and then your report will be either huge or very slow. In addition, as you set auto submit as true, you’ll get even more issues, such as page refresh.
The one solution is use portal page (or dashboard), where you can put you prompt as the first Cognos view and the real page as second cognos view, then lowest level parameter will be communicated via portal channel. This idea has been demonstrated in many places, which is not discussed in this document.
The second solution is implemented by using conditional block. When the lowest level parameter not specified, the main report won’t show, it will give you the best performance, because auto submit won’t refresh the main report at all. The main report will only processed when the lowest parameter is selected.
1) Have cascade prompt defined
2) Define the main report query with detail filter (required)
3) Define a Boolean variable
4) Put main report into conditional block only when the lowest parameter selected
a) First time:
b) Choose level3, from level 1->Level3
c) Report shows, as soon as level3 is selected
c) Change level 3, report changed accordingly
d) Back to level 1 or 2, report disappears