I have to create a report that uses the same columns but the filter is slightly different for each output. For instance (this is an example to help clarify - not one that makes real world sense):
- All trouble tickets opened in past 3 days
- All trouble tickets closed in past 7 days
- All trouble tickets reassigned to another group in past 7 days
- All trouble tickets open by product that are not urgent
- All trouble tickets where the customer has not paid us
So I have created 5 separate queries all with same data but different filters.
This seems okay - but what happens if I need to change a column in the next few months - it means I would have to update all 5 queries? That seems inefficient ...
Is there a better design method to make it easier to update or something else I should consider?
If you mean change a column in the database and your package, then yes, you need to update all of your queries.
I'm only talking about the queries.
I just figured there was a way to use a common query and change the filters for each List.
If the data columns are all the same and all you're changing is the filter, just use one query and have a filter that goes off the prompts like this:
If (?param? = 'past 3 days')
then (date_diff etc.)
else if (?param? = 'reassigned')
then (reassigned_flag = 'y')
etc.
I don't think the filters will take a case statement but I know they'll take an if/then/else that evaluates to boolean.
Thanks ... I was hoping someone had a suggestion for how to make this easier.
I was having nightmares about spending hours updating queries.