master detail on a joined query in RS

[misscognos]

Hi,

I have a poor FM model that I inherited. There are three sets of query subjects, each of the sets has relationships defined, but the two sets of query subjects have no linkage between them. In other words, if I try to put query items from the two (or three) independent sets in a query, it would result in a cross join. I am not looking to fix this right now in the model.

In a report, I need to do this:
-I have 3 queries. One is the master query.
-I have two other queries that are detail queries.
-The two detail queries come from independent sets of query subjects- they cannot be merged into a single query.

I set up a master detail relationship from master to query 1, from master to query 2. This works perfectly.

Now, I need to add up two totals from the two detail queries. The obvious approach is to use a layout calculation. But, since I have already done this once in a previous master detail relationship, the page is already associated with a previous query i needed to do similar manipulation on.. so I can't associate the page to another query (this is related to a previous post that I had here).

So, I thought I would do this:
-I put a data item into the two detail queries with value 'joinfakeout'.
-I did a one to one join between the two queries. I know both detail queries will always return a single row in the context of the master detail relationship.

The data is correct when i look at the tabular data.

But, when i try to do a master detail relationship, the data is all wrong. In the master detail relationship I link master_query.dataitemtolink->detail_query1.dataitemtolink and master_query.dataitemtolink2->detailquery2.dataitemtolink. I use the terms "query1" and "query2" here to explain, but they are now all in a single joined query. When I do this, the results are now all wrong. The numbers are inflated by a factor of 20,000 (roughly, the actual number is irrational).

My question to you guys is:
can you find a flaw in my logic. Can you fake out a join like this and expect that the master detail linkages will pass along to the individually joined queries? Does this approach make sense or am I missing something? if my logic is flawed, can you suggest an alternative?

[misscognos]

FYI... This is the previous post I was talking about:

http://www.cognoise.com/index.php/topic,32009.msg104635.html#msg104635

[misscognos]

Haha.. I will take everyone's silence as meaning one of a few things:

-I haven't explained clearly enough what I am asking.
-i am a moron for trying to do it this way (I agree but I don't have a lot of options).
-there isn't a solution.

[CognosPaul]

Having some trouble visualizing what you're doing.


You have three data containers.


-----------------------
|  Query 1 (Master)   |
|                     |
| ------------------- |
| | Query 2 (Detail)| |
| ------------------- |
|                     |
| ------------------- |
| | Query 3 (Detail)| |
| ------------------- |
-----------------------


If I understand, you're trying to get a value from Q1 into Q2 and Q3 to perform a calculation?

[misscognos]

Yes.



Now, in query 2, I have a total.

In query 3 I have a maximum (sorry, I said total earlier).



There is no relationship between query 2 and query 3.



I need to take the total from query 2 and subtract the max from query 3 in it.



I can't do it in a layout calculation due to crx-api errors. I have exposed everything required in the properties property already and still get errors. The page itself needs to be associated to the master query (query 1).



So, since I can't do it in a layout calculation and i only need one row (total row) from query 2 and one row from query 3 (max row), I thought I would try to use a subselect (create a new query and drag original query into it, results in an arrow in query explorer). Do this for query 2 and query 3. In the new queries 4 and 5, I have all the data items I need plus the data items required for the linkage to query 1 for the master detail relationship. In queries 4 and 5 (since I only expect one row each), i created "fake join data item".



So:



Query 1 (master)

Query 4 <- Query 2 (i.e subselect data items i need as well as master detail link items plus fake join data item)

Query 5 <- Query 3 (i.e subselect data items i need as well as master detail link items plus fake join data item)

Query 6 --- Join 1.1 on fake join data item query 4 and query 5.



Report:

-Create a list based on Query 6. This is in the footer of a list based on the master query 1.

-Define master detail relationshiop between Query 1 and Query 6 on the data items i subselected into queries 4 and 5 from queries 2 and 3.



Doing this results in incorrect data. Can you assume that the "filter" for the master detail linkage will be passed all the way down to the indvidual queries?

[CognosPaul]

The reason you're getting incorrect values is probably because the master/detail isn't working as expected for the subselect queries. Instead of having the master detail from Q1 to Q2/Q3 on data items, try doing data item to a parameter. By having the master/detail populate the parameter, it will (should) still work for Q4 and Q5.

[misscognos]

Thanks Paul.. unfortunately, the report has multi-select prompts and the master detail relationship is not based on prompted values. The reason for the sub select is to ensure queries 2 and 3 are a single row each to "validly" join them.

[CognosPaul]

My suggestion was to change the master/detail relationship in the Q2 and Q3 into parameters. Instead of doing, for example, [Q1].[Field] --> [Q2].[Field], put a detail filter on [Q2].[Field] = ?FieldParam?, then in the master detail wizard set the master to [Q1].[Field] and the Detail to FieldParam. When you pull in the joined query, you should make the same m/d relationship.

[misscognos]

Thank you Paul. I played around with this in a simple report. I didn't realize the parameters worked like this on MD. I like this idea and will try it in the real report!