Is there any way to avoid the top rows with NULL values that has one column value (One of the table Business Key) in Cognos report prompt values

Question

Is there any way to avoid the top rows with NULL values that has one column value (One of the table Business Key) in Cognos report prompt values

When the user does not select any value in the value prompt (Cognos Report Portal) and clicks FINISH button, the results include the column (One of the Business Keys with Value) that has other columns with NULL values.

Its no different as selecting the whole table.

`Select * From InsuranceTable`

Is there a way in Cognos to avoid the first top 10 rows when the user opts not to select anything in the value prompt

     Business_Sub_Division  Business_Division_Name  Claim_Handling_Unit
   1 NULL                   Construction            NULL
   2 NULL                   Binding Operations      NULL
   3 NULL                   E&S Casualty            NULL
   4 NULL                   Executive Assurance     NULL
   5 NULL                   Facultative Reinsurance NULL
   6 NULL                   Healthcare              NULL
   7 NULL                   Professional Liability  NULL
   8 NULL                   Open Brokerage          NULL
   9 NULL                   Property                NULL
  10 NULL                   Special Risks           NULL
  11 Asset Protection       Executive Assurance     Canada - Claims
  12 Captive Agents         Property                Executive Assurance
  13 Excess Casualty        Healthcare              Europe - Claims
  14 Financial Institutions E&S Casualty            Executive Assurance

Answer 1

I'm a little bit confused as to what you want to achieve. However, if you simply want to filter out the rows that have NULL for column 1 and column 2 even if the user doesn't pick a value from the prompt then your filter could look something like this (assuming that you are selecting Claim_Handling_Unit in the value prompt):

(?param? IS MISSING AND [Businss_Sub_Division] IS NOT NULL AND [Claim_Handling_Unit] IS NOT NULL)
OR
(?param? IS NOT MISSING AND [Claim_Handling_Unit] = ?param?)

You have two bits of logic, one where nothing is selected and the other where a value is selected. If nothing is chosen then we only select rows where both of the other two values are not null. The key is that the two conditions don't overlap. Either ?param? is missing or it is not missing.

You can also accomplish the same logic using IF...THEN like this:

IF (?param? IS MISSING) 
THEN ([Businss_Sub_Division] IS NOT NULL AND [Claim_Handling_Unit] IS NOT NULL) 
ELSE ([Claim_Handling_Unit] = ?param?)

..or with a CASE statement

CASE 
WHEN (?param? IS MISSING) THEN ([Businss_Sub_Division] IS NOT NULL AND [Claim_Handling_Unit] IS NOT NULL) 
ELSE ([Claim_Handling_Unit] = ?param?) 
END