Problems creating New Measure

[twinkle8]

Hi All,
I want to create a new measure as a combinationbetween two tables.
An example of tables could be:
- Name, column names and values of TABLE1
--------|----------
ATR_DESC|MET_TABLE1
--------|----------
ATR1 | 2

- Name, column names and values of TABLE2
--------|----------
ATR_DESC|MET_TABLE2
--------|----------
ATR2 | 7
The new Measure (let's call it MET) we want to create is a count of MET_TABLE1 or MET_TABLE1 depending of the attribute following the measure.
If MET follows ATR1 the result would be 2 and if MET follows ATR2 the result in this case would be 7.
Anyone can tell me how to create this new measure, without any join between these two tables?
Thanks in advance,
Bea

 

[drlex]

An idea of what product you're using would assist, as a solution in reportnet vs impromptu vs transformer will be different.

I'm a little confused as to what you've written, as the sentence "If MET follows ATR1 the result would be 2 and if MET follows ATR2 the result in this case would be 7." appears to relate what you've already shown in TABLE1 & TABLE2.


soi la, soi carré

 

[twinkle8]

Sorry, I am working with Framework Manager and to summarize the problem: I want to create a measure as a combination of two fact tables. Depending of the attribute I choose in the report I want to go to count in a table or in another one.
Thanks,
Bea

 

[longhorndkr]

I don't even understand your description of your tables. Can you pls. clarify using standard dbms row and column descriptions? Is MET_TABLE1 a column in Table1?

It sounds like: based on what you select for your report you want to dynamically do a count in one table or another (which sounds like you should be using an aggregate)? Is that correct?

 

[blom0344]

twinkle,

You can define a union query subject from both tables and assign a constant in the definition that point to table 1 resp. table 2.

That way you use the query item that references the constant to either select from table1 (giving measure 1) or table2 (yielding measure 2)

Ties Blom

 

[twinkle8]

longhorndkr that's correct.
First question: MET_TABLE1 is a column.
Second Question: Yes, I want to do an Aggregate Aware in Cognos.. Is that possible?

 

[twinkle8]

Thanks, blom0344.
The problem is that there is no join between table1 and table2.. It would be possible doing a Query Subject between two tables without a join in common?
Thanks,
twinkle

 

[blom0344]

Lookup what a union is in SQL terms. There is no join involved. You can define it as a pure SQL element in the framework:

SELECT A as name1,B as name2,C as name3 FROM TABLE 1
UNION
SELECT D,E,F FROM TABLE 2

Ties Blom