GREAT Total Compared To Total Of User Selection

[sanchoniathon]

(I don't remember if there's a post limitation in this forum but here i am again with another simple question)

- We have 2 queries
- Query#1 has data items and metrics to display the GREAT TOTAL with no user prompt selection
- Query#2 has data items and metrics to display totals based on what the user selected on the prompt page
QUERY#1
-- Great Total --
[Total Sales] (NO user prompt selection)
[Total QTY] (NO user selection)
[Total QTY] / [Total Sales]
QUER#2
-- Total Based on user prompt's selection --
[Total Sales] (based on user selection)
[Total QTY] (based on user selection)
[Total QTY] / [Total Sales]
Should I create a Query#3 based on a JOIN between Query#1 and Query#2 to calculate the following:
[Total Sales] (based on user selection) / [Total Sales] (NO user prompt selection)
[Total QTY] (based on user selection) / [Total QTY] (NO user selection)

The thing is that I don't have all the same data items (ID's) on both queries (simply not all displayed on report) as for I don't really need them on both queries.
How should I deal with this?
Thank you for any valuable suggestions.

[bi4u2]

If you have the same number of items and same data types, it probably makes sense to go with a UNION for Query #3.

[sanchoniathon]

Hi bi4u2,

The problem i have is that some Data Items from Query#1 that would need to be joined (if it's needed ?) to Query#2 are beeing impacted by the Report's filter (some prompts)

versus

the GREAT TOTAL (that is query#2) which is simply some unaltered metrics comging directly from the Cognos Package.

So if i joined both queries by using some of those (no choice) common fields,
query#2 would therefore also be impacted by the values from Query#1 (which we DON'T WANT) ?

The final result is to be able to perform calculations (ratio%) between sales from user selection contained in Query#1 and the whole company's sales contained in Query#2.


I'm not sure how to do this ?

[sanchoniathon]

Is this possible ?