Multi-Prompt Selection Issues

[sag4cognos]

Hello All,

I have a report which runs off a prompt page.
The prompt page provide a list of all the employees. the report needs to display the sales measures of the employees.

The prompt page lets the user select Multiple employee numbers.
The employee dimension table has 118 employees. The sales fact table has records for only 72 employees.

Requirement: Based on the selection,  the report should display the corresponding sales numbers of the employees.

Problem: Since the fact table does not have the records for some of the employees, they are not displayed in the report , even when they are selected in the prompt.

I tried using the No data Option, but it does not fulfill my requirements.

If I select 10 employees, out of which, 8 have records in the fact table, the query executes and records some records. Since the query is returning records, the No data option fails. The report executes successfully and returns the output for the 8 employees who have records entered into the fact table.

I am working on doing some outer joins, but I do realize that if the end user selects only one record, the outer join will bring in the rest ( which is redundant data ).

Request some thoughts and ideas on solving this requirement.


Thanks,
Sag

[cognostechie]

Yeah, in a practical world, only some of the employees should be selling something rather than all of the employees. Thay way, there will be less fights in the company  ;D

If you create an outer join on both the sides, then it might bring in all the data but in your case, you can create outer join only on the Sales Fact. By doing so, if you select only one employee from the prompt, the report would show data only for that employee because your filter would still be on that employee. If you select all 118 employees, the report would show all 118 records and sales for 72 of them. This should be the cardinality :

Employee Dimension                   Sales Fact
--------------------------                   -------------

1..1                                               0..n