Have created a value prompt to show the distinct values from one of fact table. Have created query (using package source) from Jethro Database.
When I try to see the Generated SQL option in report studio, the distinct clause not included in native SQL. However its added in Cognos SQL.
Only the native SQL sent to database, so its failing to fetch the data.
What do you mean with the value prompt showing the distinct values? If you select a data item to show in a value prompt, the values are distinct by default, so I don't understand your mention of 'distinct'.
Secondly, which values are you getting from the fact table for a value prompt? For me value prompts are for filtering on dimension attribute values.