How to handle this?

Cognos 10 Business Intelligence Framework Manager
1

Hi all,

I working on a completely new setup of Cognos 10 and encounter some issues when I want to create the metadata layer.
What I would like to do is start pretty basic and create something similar as this query:

select pst_pst_id, id_receiver, count(tra_id) cnt
from (
select tra.tra_id, tra.pst_pst_id, tra.nop_nop_id, tra.bedrag, pst.rtl_rtl_id, pst.id_receiver
from hand_trans.tra, hand_trans.tst, hand_trans.pst
where pst.pst_id = tra.pst_pst_id
and tra.tra_id = tst.tra_tra_id
group by tra.tra_id, tra.pst_pst_id, tra.nop_nop_id, tra.bedrag, pst.rtl_rtl_id, pst.id_receiver
having max(status) = 21
)
group by pst_pst_id, id_receiver

As you can see i’m only using three tables so to import those is easy.
I did also manage to get the having max(status) = 21 in place using a filter on the tst table.

My problem lays in using the group by and sub query in a correct way.

If have been stuck for a few days now and start to wonder if I should be solving these ‘issues’ in framework manager
or on a higher level.

I would really appreciate it if someone could help me out, so I can continue building the metadata layer :slight_smile:

Thanks,

Kinimod

2

Best practice is to import your database tables as is.
Then create a business layer where you do your business logic like you want. (this is where you should do the group by…etc)
Then you have a presentation layer for what you want to expose to the report designers.

Resources:

http://www-01.ibm.com/support/docview.wss?uid=swg21339060