Reusing Totals from different queries in another query

[Ron McIntyre]

I tried searching but could not find anything that exactly matches my need.

I have a report I'm trying to make that has a need for 3 different lists.



As you can see, I want to use the totals from List 1 and List 2 in List 3

My Queries are shown below. 



I tried to join Query 1 and 6 into a new query and it does show the totals but it is mutliplied by the number of records in query 6. 



How do I properly take the totals from List 1 (Query 1) and List 2 (Query 6) and use the totals only in another list so I can add them together?

Unfortunately my Report Authoring training was 2 years ago but due to Project delays, we're only going into production with Cognos now.  If this is a real newb question, I apologize in advance.

Thanks in advance,

Ron
Montreal, Quebec Canada

Modification Note: Changed Image URLs to Skydrive addresses.

[CognosPaul]

Without seeing your queries or model, it's hard to give precise advice. There are many ways of accomplishing this.

The first thing that I would suggest is, in query 9, take the raw values and not the totals. If the totals are being multiplied by the number of rows, the raw values should just total up nicely.

You could also make a new query that returns the total from each query directly, without making a join.

So the data items would be
[Query1].[Component Cost]
[Query9].[Time Code Total]

with both of the aggregate functions set to total. You'll need to set "Cross Product Allowed" to Allow in the query properties.

[Ron McIntyre]

Without seeing your queries or model, it's hard to give precise advice. There are many ways of accomplishing this.

Thanks for the reply Paul.

It is appropriate forum etiquette to post the text of a report?

In the interim, I found a solution that seemed to work.

[MFGF]

It is appropriate forum etiquette to post the text of a report?

Please feel free to post your report spec.

In the interim, I found a solution that seemed to work.

If you get chance, could you post up your solution?  It may be helpful to others with similar issues.

Thanks,

MF.

[Ron McIntyre]

Please feel free to post your report spec.

OK, will do.

If you get chance, could you post up your solution?  It may be helpful to others with similar issues.

Hopefully I'll be able to explain it!  ::)

[Ron McIntyre]

Please feel free to post your report spec.

OK, starting with that.  Please keep in mind this is my first report after having taken my Report Authoring classes 3 years ago.  It is likely clunky and very "unpretty".

Also, since the actual report is too many characters, I have saved it as a text file available HERE.


Modification Note: Changed URL link to a Skydrive address.

[Ron McIntyre]

If you get chance, could you post up your solution?  It may be helpful to others with similar issues.

• Step 1: I created a new Query 10 and created it using a join of Query 2 and Query 3.  This duplicates Query 1.
• Step 2: I created a new Query 11 and created it using a join of Query 7 and Query 8.  This duplicates Query 6.
• Step 3: I added a column calculation which is the total in each one.  For Query 10, total(([Query2].[qty_used]/[Query2].[qty_per])*[Query1].[Cost] for report).  For Query 11, total((([Query7].[qty]*[Query7].[time_per])*1) for report)
  
• Step 4: I added a Data Item in Query 10 and Query 11 that has a value of 1 (to allow join).
  
• Step 5: I joined Queries 10 and 11  as Query 9 on the data item I created in Step 4.
• Step 6: I then added the 2 values noted above in Step 3 into Query 9 in a new Data Item. [Time_Total1]+[Comp_Total1]

[CognosPaul]

A few notes about the specs.

It looks like there are a few places where you're using singletons to present the selected parameter. You can get rid of Query12 by replacing the Singleton in Cost Selected for Analysis with a Layout Calculation with paramdisplayvalue('Cost_Select')

You are doing a lot of joins in the report. Is there any way to join those tables in the framework?

[srinu1253]

Why do you want to join you can try with union between Query 1 and Query 6.

any comments aon this solution???

[Ron McIntyre]

You are doing a lot of joins in the report. Is there any way to join those tables in the framework?

Yes and no.  The ERP solution we are using with Cognos models their DB themselves and provides the models to us.  Some joins I need are in the framework and some are not.

In order to change the framework, I'd need to buy additional modules which do not seem to be worth the cost/effort.

Like I said ... clunky, I know! :)

[Ron McIntyre]

Why do you want to join you can try with union between Query 1 and Query 6.

Hmmm ... what are the benefits (pro's and con's) of UNION versus JOIN?  My understanding (which is limited and may be misguided!  ;) ) is that a UNION would link 2 queries into a new subset but that the data in the queries must contain the same number of data items (and must appear in the same order) and that the data types must be compatible.

I guess this is why I did a JOIN.

Was I wrong?