[Solved] creating a calculated column based on measures from different queries

[shahrukhs_fan]

Hi,
Ã,  Ã, I'm making this report in cognos report studio which has got two queries.Ã,  The first query shows data like carrier parent, total minutes for the current month.Ã,  There is one row for each carrier parent.Ã,  eg..
Carrier Parent |Ã,  Total Minutes
Ã,  Ã,  AÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 1200
Ã,  Ã,  BÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 1000
Ã,  Ã,  CÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  500Ã,  Ã, 

and so on.

The second query shows the same data but for the prior two months. eg....

Carrier Parent |Ã,  Ã, Total Minutes
Ã,  Ã, AÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  2000
Ã,  Ã, BÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  1500
Ã,  Ã, CÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 700


Now, what I wish to do is create a calculated column that would give me the Total Minutes measure from Query 1 divided by the Total Minutes measure from Query 2.
The result would be something like..........

Carrier ParentÃ,  Ã, |Ã,  Ã,  Ã, Calculated columnÃ,  Ã, 
Ã,  AÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  0.6
Ã,  BÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  0.66
Ã,  CÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  0.71


How would I go about doing this?Ã,  I tried master detail, but then, when i go to creating the calculated column I only see the query item's in the respective query.

Hope I've explained the issue right.Ã,  If anything isn't clear please let me know and I'll elaborate.

Thanks in advance

[Opher]

Hello - This data will require going through a UNION with columns shifted.  Take a look at the documentation on Tabullar Set - Union and that should work for you.

Good luck,
Opher

[bobg4u]

Can't you get all the columns in a single query?? I guess that would be the easiest way.
How is the data stored in the DB?

[bdybldr]

I would use a Child tabular Model to accomplish this.

TabModel
Ã,  Ã,  Ã,  |
Ã,  Ã,  Ã,  Child Tab 1 = Select [CarrierParent], [TotalMin] AS TotalMin1 from... Where date = currentmonth
Ã,  Ã,  Ã,  Child Tab 2 = Select [CarrierParent], [TotalMin] AS TotalMin2 from ... where date in last two months

Then add a filter to the Main Tab Model: ChildTab1.[CarrierParent] = ChildTab2.[CarrierParent]

Then you can place them in your report and create your calculated column in the report totalMin1/TotalMin2.

Hope this helps.Ã,  Keep us posted.

[Boris-A]

You could also create a third query which would have the two first queries as tabular references.

Ideally if you can have all your data in one query that is the most efficient way to do it, but if you absolutely need the first two queries (for whatever reason) then a third with the tabular references could be done (using a tabular set to combine them of course).

Let us know if you need more clarifications on how to do tabular sets/reference.

cheers

[shahrukhs_fan]

Thanks a lot all of you.  You guys r gr8.
Problem's solved.