cognos modelling

[pingrkiran]

modelling question...


cognos gurus,

migrating some cognos 7 reports to cognos 8.4 on oracle DB.

How do we implement this scenario  in report studio.

Can't  copy/paste the query in SQL object in report studio as it is Disabled.

open to any suggestions



select    distinct sub.customer_id , ua.LOGIN_NAME,  eu.FIRST_NAME,
               
     eu.LAST_NAME, eu.EMAIL_ADDRESS, t1.status,   (t1.attempt_date) LAST_ATTEMPT_DT
   
   
from    subscriptions sub, (
      select bcr.account_id account_id, bca.status STATUS,
               
      from billrequest bcr, billattempt bca ,
      (
         select cr.account_id acc_id,
                   max(cr.ATTEMPT_DATE) max_attempt_date
         from billrequest cr, billattempt ca
         where cr.id = ca.clearance_request_id
         and cr.ATTEMPT_DATE >= trunc(sysdate -7)
         group by cr.account_id
      ) t2
      where bcr.ACCOUNT_ID = t2.acc_id
      and bcr.id = bca.CLEARANCE_REQUEST_ID
      and bcr.ATTEMPT_DATE = max_attempt_date
      and  bca.EXT_RETURN_CODE in (2,3)
    ) t1, usr_auth ua, users eu
   
   
    where t1.account_id = acc.ID   
   
  and  sub.customer_id = eu.ID  and sub.CUSTOMER_ID = ua.UA_ID
 
  and sub.ref=0 and sub.prod='bundled'
 
 
  union
 
 
 
  select    distinct sub.customer_id , ua.LOGIN_NAME,  eu.FIRST_NAME,
                 
       eu.LAST_NAME, eu.EMAIL_ADDRESS, t1.status,(t1.attempt_date) LAST_ATTEMPT_DT
     
     
  from    subscriptions sub, (
        select bcr.account_id account_id, bca.status STATUS,
                 
        from billrequest bcr, billattempt bca ,
        (
           select cr.account_id acc_id,
                     max(cr.ATTEMPT_DATE) max_attempt_date
           from billrequest cr, billattempt ca
           where cr.id = ca.clearance_request_id
           and cr.ATTEMPT_DATE >= trunc(sysdate -7)
           group by cr.account_id
        ) t2
        where bcr.ACCOUNT_ID = t2.acc_id
        and bcr.id = bca.CLEARANCE_REQUEST_ID
        and bcr.ATTEMPT_DATE = max_attempt_date
        and  bca.EXT_RETURN_CODE in (2,3)
      ) t1, usr_auth ua, users eu
     
     
      where t1.account_id = acc.ID   
     
    and  sub.customer_id = eu.ID  and sub.CUSTOMER_ID = ua.UA_ID
   
  and sub.ref=1 and sub.prod='not bundled'
 
 
  Thanks!
  Rkiran

[RobsWalker68]

Have you designed your framework model yet?  Once you have undertaken that and published a package the report design should be relatively straight forward.

If I was you I would avoid taking an Impromptu catalog as is and implementing it in Framework Manager.  It will save you a lot of time in the long run if you give some time to thinking about how it should be modelled with regards to the current and future business requirements. 

I would also avoid just pasting SQL into a report studio as thats a pretty nasty way to do it and negates much of the advantage of usingCognos 8/10. 

Cheers

Rob

[pingrkiran]

Thanks Rob for quick response..

We have frame work setup.. as it is a transactional DB. they  have not modelled as starschema..


so we have to implement this logic in Report Studio

How do we do this in Report studio?

[RobsWalker68]

Hi,

A Framework Manager doesn't have to be a star schema although it brings advantages in some situations.

The FM model should have the necessary relations for your tables (subscription, bill request,bill attempt,usr_auth,users).

Looking at it, this derived table is the driver and from what I can understand it identifies the maximum attempt date for a bill request in the last 7 days and this is subsequently used to filter the remainder of the query.

select cr.account_id acc_id,max(cr.ATTEMPT_DATE) max_attempt_date
from billrequest cr,billattempt ca
where cr.id = ca.clearance_request_id
and cr.ATTEMPT_DATE >= trunc(sysdate -7)
group by cr.account_id)
t2

where bcr.ACCOUNT_ID = t2.acc_id
and bcr.id = bca.CLEARANCE_REQUEST_ID
and bcr.ATTEMPT_DATE = max_attempt_date
and  bca.EXT_RETURN_CODE in (2,3)
) t1


You should be able to implement a filter using the maximum function in report studio to get the same effect.

You will need a query for 'bundled' and another for 'non bundled' and then in query explorer use the union operation to union them together.

Rgds

Rob

[pingrkiran]

thanks rob ..

we have FM setup with relations..

Can you please outline steps how to implement this derived query..

(
        select bcr.account_id account_id, bca.status STATUS,
                 
        from billrequest bcr, billattempt bca ,
        (
           select cr.account_id acc_id,
                     max(cr.ATTEMPT_DATE) max_attempt_date
           from billrequest cr, billattempt ca
           where cr.id = ca.clearance_request_id
           and cr.ATTEMPT_DATE >= trunc(sysdate -7)
           group by cr.account_id
        ) t2
        where bcr.ACCOUNT_ID = t2.acc_id
        and bcr.id = bca.CLEARANCE_REQUEST_ID
        and bcr.ATTEMPT_DATE = max_attempt_date
        and  bca.EXT_RETURN_CODE in (2,3)
      ) t1

appreciate your time on this..

[RobsWalker68]

Ok try this.  There's a couple of ways to do it

Apologies as I don't have Cognos open so some of the syntax may need to be checked  ;D

1. In Report Studio, click on query explorer and drag in a query and name it 'Bundled'
2. Add the data items you need on the report
3. In the  detail filter create equivalent filters for:
a) sub.prod='bundled'
b) bca.EXT_RETURN_CODE in (2,3)
c) cr.ATTEMPT_DATE >= add_days(current_date, -7)
d) bcr.ATTEMPT_DATE = maximum(attempt_date for cr.account_id)
4. Drag in another query and name it 'not bundled' item except change the detail filter to use 'non bundled'
5. In query explorer again add an union operator and combine the bundled and non bundled operators.
6. Use the result from this union as the query for your list/crosstab/chart.

The bcr.ATTEMPT_DATE = maximum(attempt_date for cr.account_id) I think should do the same as your derived table but I tend to answer these type of queries playing around with actual data and not theoretical so if it doesn't work just add another query that identifies the maximum attempt date and equi join this in query explorer to your bundled/non bundled query and then do the union.

Cheers

Rob

[pingrkiran]

Thank You so much Rob..

I will Try these steps and let you  know.

[pingrkiran]

Thanks Rob i have follow the steps and it did worked.

I have one problem though

I have One framework package.

How would i exclude certain filetrs in report studio for few reports.

as these filters may need by some reports.

[MFGF]

You can define standalone filters in Framework Manager (usually in a folder) and include this folder in your published package, and these can filters then be selected if needed when building a report.

Regards,

MF.