Search code examples
sql-servercognoscognos-10

Is there a way to avoid entering the data twice if you are using two different sources in Cognos?

I'm using two sources to create a report in Cognos 10 Report Studio. When I joined the two sources (full outer join) in Framework Manager, it takes an hour to run it in the report studio. So I decided to put a parameter/prompt named as Form_Number in each Source query, Source1 query and Source2 query in Cognos Query Explorer, by doing this, it will make the query faster. So it was working fine until the user requested to have both prompts joined together, because they are lazy enough to enter the form number twice. Is there a way that I could enter the Form Number prompt only once?

This is the current set-up.

Source 1 is using [FormNumber] = ?Source1_Form_Number? enter image description here

Source 2 is using [FormNumber] = ?Source_2_Form_Number? enter image description here

enter image description here

Solution

  • Yes. Just make the parameter name the same for both queries. In your example you have two distinct parameters: Source1_Form_Number and Source2_Form_Number. This will always result in two prompts. Simply change this to a single parameter for both, e.g. Source_Form_Number and the user will only be prompted once.