Multiple charts with same query but different filters

[pumccg]

Is there a way to send one query to the database and use it against multiple charts in the report, but with each chart having different filters?

[Lynn]

You can make a query reference and then apply different filters, but I'm not sure if that would only send a single query to the database.

Create the main query, then drag another query into the query explorer, then drag the main query along side the new query. Edit the target items and filters as desired.

[pumccg]

By query reference do you mean having the main query (say Q1) feeding into the second query (Q2) eg Q2 <--- Q1

I could then put separate filters on each query but then Q2 would also inherit the effect of Q1's filters, would it not?

[Lynn]

Yes, that is what I meant. Q1 would need to be the super set and contain only filtering that is common for all dependent queries. Then each dependent query would be filtered further as needed for the requirements of each chart layout container.

I'm not sure it would necessarily be a better option than just creating separate queries, but technically it is an option to consider.

[pumccg]

I did look at that approach initially, but didn't pursue it too far as I think the filters are mutually exclusive. Even if they're not, I was puzzled about where the filtering would take place, particularly for "Detail Before" filters. I'll have another play anyway and see how it goes.

I can do it Ok for identical charts, filtering on just say Year, by converting a chart into a Matrix chart, and nesting on Year in the Categories. However this doesn't work for different charts.

[pumccg]

Lynn,
I've had another play using your suggestion, and I think it's going to work. The filters aren't mutually exclusive and feeding Q2 into Q1 etc and then using them as required on specific reports seems to work ok. Many thanks :)

[MFGF]

Just to clarify - even if you have just one query defined in your report, if you are using it for multiple report containers (list/chart/crosstab/map etc) then a separate database query will be issued to populate each container.

Cheers!

MF.

[pumccg]

If that's the case why do we bother re-pointing queries when we add new charts if they are using the same data items etc  particularly when the filters are identical?

[MFGF]

The only good reason I am aware of is ease of maintenance later on. It's easier to update one copy of the query in the report spec.

MF.

[pumccg]

Ouch! I've been wrong on this one then for a long time!

I suppose the other benefit is sharing filters and prompts, and greater interactivity when using drill down/up using dimensional packages.

Thanks for setting me straight.