Best practice for Lookup table / how to add filters to an alias shortcut

[JBischoff]

I have a single lookup table in my database, with a "lookup_category" field to differentiate the usage groupings. 

If I understand this correctly, if I create model query subjects based on my underlying lookup table and add relationships to my other assorted tables, I will override the ability to generate minimized SQL.  Looking through previous Cognoise posts, I came across the advice that I create multiple alias shortcuts and add filters to them.

I am able to add the lookup_category filter/where clause by creating an advanced relationship definition.  But I would also like to be able to use these lookup tables to fill prompt dropdowns in Report Studio, and as things stand the table, by itself, returns all my possible lookup values.

Am I on the right track at all?  Should I create additional MQS's in the business layer with the filters built in, to meet my prompt lookup needs?  Is it possible to add actual filters to an alias shortcut?

Thanks for your input!

[blom0344]

We use the same setup you are working with. Instead of alias shortcuts we simply generate copies of the lookup subject , each with their own filter to narrow down the usage. In terms of minimized SQL we take a hit, but our lookups are in the range of 10-100 codes per usage and performance is no issue therefore.

The pre-filtered lookup subjects are also suited for prompt as each subject will only show values for the required usage.