I am new to Cognos. I want to create a report in 'Cognos-bi' for the external SQL query, which is having multiple parameters with multiple values. For the "each" parameter I need to use value prompt, which should display each parameter values like a drop down list.
When I used to create value prompts it would create only the pre-existing package items in data source but not the data item columns.
Is there a way to fetch these data item columns into the data source package items?
Create a separate query for each prompt. Make each query a custom sql that goes to the same target database as your external sql query. You really can't use the same custom sql query for prompts and the main report data, since ideally you want to be filtering the main report data by the prompt selections.
For example, I want to query salaries for the main query:
Select e.last_name, e.first_name, s.salary
from salary_table s
join employee_table e on s.employee_id = s.employee_id
If I wanted to prompt the report user to select an employee, I would create another query to external sql (custom sql):
select e.last_name||', '||e.first_name "Employee Name", e.employee_id
from employee_table e
where termination_date is null
I name that query pEmployees. I create a value prompt on the prompt page and set its source query to pEmployees. The employee_id is the 'Use value' and "Employee Name" is the 'Display value'. I would sort it by Employee Name. Set the Parameter the prompt is linked to as 'pEmployeeSelected' or whatever variable name you want.
On the main report query, add a line to filter it based on the prompt selected.
Select e.last_name, e.first_name, s.salary
from salary_table s
join employee_table e on s.employee_id = s.employee_id
where e.employee_id = #prompt('pEmployeeSelected')#
Now no filters are required in the main report query item, since you are filtering in the sql. If you instead filter in the query item, Cognos pulls all employees every time the report is run, then finds the one employee and reports on it. Very inefficient.
If you are filtering the sql by prompts, that same query cannot be used to generate value prompt drop downs. That is why we use two seperate query items, one to get a list of employees, and one to actually query salaries (and employee details).