Querying only part of a table

[Yunus]

Hi everyone.

I am trying to do something that I'm not sure is possible and would appreciate any other suggestions on how to accomplish the end goal.  I have a table that contains records for current and former customers, 95% of the time we only care about the current customers, however there still is a need to retain the former customer data.  There is a field that identifies if the customer is current or former, so a simple filter will work to provide just the current customer data set.  What I would ideally prefer to do is have the current customers returned by default and only include former customers if the author somehow requests them.  Kind of like a filter that is applied unless the author puts some condition in place to bypass the filter.

The current method is to have 2 tables, and create a Union when needed to show all the results, this creates an overly complex join structure and slow queries since we are unioning and joining in RS and causing Cognos to do the work that we should be asking the database to do.

Is there a way to do this at either the database level, FM level or RS level? I forgot to mention that custom SQL is disabled for our authors.

[bdbits]

Maybe a prompt macro with a default?  [Former Customer] = #prompt('pFormerCustomer','string','''n''')#

Prompt macros are usable in both FM and RS.

Please don't enable custom SQL.

[Yunus]

I will look into prompt macros, thet look promising.

Thanks!

And we have no intention of enabling custom sql, we apply data security in Framework and custom sql can bypass it.