Using total from master query in detail query

[misscognos]

Hello,

I have the following but I am not sure if there is a way to get this done easily.

-a list based on a master query.
-master list has multiple footers based on grouped data but for the purposes of this question let's just call it one footer for the sake of simplicity.
-In the first footer (split up cells), I have placed some calculations under some columns.
-I went to structure->headers and footers and inserted a row under the footer (so just an additional row that is part of the same footer).
-In the new row, I merged some cell the cells (but not all) to have a wider "column" in which to place a list.
-I put in a new list for the detail query in the new row.
-Linked up the master and detail lists through a relationship and all works well to this point.

Problem:
-I need to take a calculation from the master list that is part of the first row of the footer and take a total in the detail list and subtract the two. The calculation in the first list is 'sort of' a total but not created by using the totaling functionality built in RS.

I tried a layout calculation but I never expected it to work since the context for the second list is not the same as the first list. I was right, it didn't work.

Your input is much appreciated. Thanks!

[SpareTire]

I think I understand what your trying to do as I have been in that same position. To rephrase: you have page sets where the page has calculations on the header and footer that are not being filtered by the page set query. Am I right?

You cannot filter a layout calculation based on a page set. I had to replace my own layout calculations with a table (albeit, a table with one value) and then group the value so it appears once. Then filter that table using the master detail relationship.

[misscognos]

Hi, thanks for the reply. This is part of a migration from impromptu to  c10.

My main problem is that I need to subtract two numbers.. but they are not in the same query. One is in the master query and one is in the detail. There is a master detail relationship. The result of the subtraction needs to be displayed in the detail list. I was wondering if there is a way to leverage the defined md relationship to be able to do it.

The model is terrible!

[SpareTire]

I don't think there is. The data item from the master query needs to also be in the detail query. Is there a way to recalculate it in the detail query?

If i know one thing its that Cognos wants everything to be in query to calculate it.

If you cannot recreate the master query calculation, then I would suggest duplicating the master query calculation (master query2), creating dummy items in master query 2 so that it matches the detail query and then union them together to make a new detail query (detail query 2). Then the calculations appear in both the master query and detail query 2. If it sounds painful, that's because it is. :o

[misscognos]

Thanks. I didn't think there was either. In impromptu you can reference other queries pretty easily. Problem is the fm model is amongst the worst I have ever seen... There are many little islands and I can't put this in the detail because the queries are from different islands. This is the last report and at this point in time there may not be too much value in a proper model (apparently). I will see if I can join the queries in rs.. well take bits of the master and join to detail or something along those lines.

[misscognos]

Actually, I was just playing around with the samples. I created a much simpler report with a similar general layout. I am able to reference a calculation from the main query in the detail query in a layout calc successfully and subtract two numbers from different queries with no problem. I guess I need to figure out why it errors when I do it in the real report...

[misscognos]

This seems to work (proper testing yet to be done but looks promising):

-associate page to main query.
-set properties property for PAGE and add data items you need from master query in detail to the properties list.
-with items you need in the properties property from the master query, you can reference items from the master query and detail query in a layout calculation and subtract the numbers.

(I had already tried this on the master list before posting here and that didn't work.. ie. I put the items from master query in the properties property for the master list and that didn't work).

Didn't realize that the page had a properties property.

Anyone see any problems with this approach?