1

I have a list report, and it pulls from a query subject that contains the following products: a, b, c, d, e. However, the report query contains a filter that filters for a, b, c.

Once you execute the report in HTML it displays products a through c as expected. The issue is that once you click on the product column, create filter, the filter shows values a through e instead of a through c. Do you know how to fix this, so that you can't filter on products that don't exist in the report?

helloworld22
  • 11
  • 3

1 Answers1

1

When you create a parameterized filter on a Report Studio report but don't create an associated custom prompt, Cognos will generate a system prompt for you. It will choose what it thinks is the right prompt type based on the data type of the item and nature of the filter. In the case of a value prompt it will display all available values, even if some values are already filtered out in another filter.

The solution is to create a custom value prompt on a prompt page that points to a filtered query as its source.

The rough steps are:

  1. Create a new query that includes the data item that you want to filter
  2. Filter the new query to only the values you want displayed as choices in the prompt
  3. Create a prompt page, if it doesn't already exist
  4. Add a value prompt to the prompt page and specify the parameter as the one you have in your main query filter
  5. Configure the new prompt's 'Query' property to point to the new query
  6. Set the 'Use Value' and 'Display Value' to point to the data item in the query

Now when you run the report you'll get a prompt page with a value prompt displaying only the values you want.

Johnsonium
  • 2,005
  • 1
  • 13
  • 15
  • To clarify, it's not the prompt page that's displaying the extra values... It's the results page that actually contains the data. I just tried creating a prompt that filters for the data items I want in the prompt's query, and that works for the prompt. However, on the results page when I click to create a custom filter it includes those other values in the database. – helloworld22 Oct 30 '17 at 15:16