2

I am new to Cognos and wondering how or if possible to achieve the following.

Firstly, I have a table:

Period Id     Name  Start_Date     End_Date

1             a     01/04/2011     06/08/2013

I can create a prompt to select the Period Id from the scheme and pass it through, but I would like now to prefill a second prompt with Start_Date and End_Date of the Period Id selected in the first prompt:

Scheme Start_date <= Period.Start_Date where ID = selected Period ID from ?Period Prompt?
Scheme End_date <= Period.End_Date where ID = selected Period ID from ?Period Prompt?

I cannot use an SQL connection, so I cannot build the SQL separately eg: 

select Start, End from....Where ....= ?Prompt?

Thank you in advance....

Roger

Marcus Rickert
  • 4,138
  • 3
  • 24
  • 29
Darklantern
  • 81
  • 4
  • 10
  • Even if you are not allowed to use SQL directly do you have access to a) the Framework Manager model or b) the DB schema to install a DB view? – Marcus Rickert Dec 24 '14 at 05:48
  • HI Marcus, Yes I have access to the schema and framework manager. What I meant to say is that I am not allowed to open extra connections to the SQL server. I know how to extract the information using SQL but I am not allowed to add a permanent solution incorporating a specific connection outside of the reports package. – Darklantern Dec 26 '14 at 11:13
  • Then you should be able to establish a relationship between the *Period Id* chosen in the first prompt and the Period entity containing *Start_Date* and *End_Date*. In the query for the second prompt you can filter for the *Id* value chosen in the first prompt. Cognos takes care of the rest. – Marcus Rickert Dec 26 '14 at 23:27
  • Thanks for your reply, I havent had time to go back to this job , next week I will give it a try. – Darklantern Jan 16 '15 at 05:28

1 Answers1

1

You need to create Cascading Prompts.

Create two prompts. The first links to a parameter, lets call it Parameter1.

Your second prompt has a Cascading Source set to Parameter1. When creating the the second prompt, you have the option to create a 'Parameterized Filter'. Use that option to set what value is being filtered, and point it at parameter1.

Here is an example from IBM.

Damienknight
  • 1,876
  • 2
  • 18
  • 34