We have a database with a lot of attribute tables where the common link is the ID field to the individual main record number. In this huge report I'm creating, I want to use the queries tab of the detailed filter expression to include/exclude different attributes of the database which filter a main results query. The alternative would be to join all of these tables into a large query where I could filter directly on the data items within the main query. Those kinds of queries have really poor processing time and I want to avoid that.
Include any degree year between 2015 and today. In the prompt page, there is an option of degree years. Choosing this filter allows the user to choose which degree years to include (optional). This filters the degree year query, a small query of IDs and degree year. In my main query, I have a filter that says Main Query ID includes all IDs from the degree year query. These are optional include filters and are easy and work well.
My challenge is I have another prompt page lists Exclude filters. So, let's say I have exclude prompt of eye color and the user selects they don't want anyone with blue eyes in the results. I have a query called eye color that is just ID and eye color, that filters on the records from the prompt selection. When I go back to my main query, it has an optional filter that reads: Main Query ID <> IDs from the eye color query (the user selected blue). This works. However, if my user does not care which eye color the record has, then that filter is going to exclude all eye colors.
I am looking for a way for prompt values to not exist unless a selection is made on the prompt page. Or I'm looking for another way to tackle this.
I apologize if this is confusing. Writing out an obscure example that people will understand is a challenge.
Jeanie Krieger – I agree with Thomas in the previous post and possibly simplify your logic by just making the prompts optional. In general, Report does offer enough flexibility that will allow you to do what you want to do.
Like Todd mentioned, we use conditional blocks on our prompt pages to determine what fields are needed based on report type. For example, As of Date, Official Reporting Report or Current Date. Based on the Report Type, then it either prompts for As of Date, Reporting Period or nothing.
In the report, we create a query item based on the Reporting type and values entered in the conditional prompt and it is then used in the filter.