Topic
  • 1 reply
  • Latest Post - ‏2014-03-11T17:28:28Z by bargreen
prat123
prat123
24 Posts

Pinned topic Cognos FM

‏2014-02-10T19:53:04Z |

Hi, 

I have requirement to implement the below query in FM with 3 tables sales,orders and products. 

select sales_id, sales_count,sales_amount,prod_amt from
( select s.sales_id as sales_id,count(sales_id) as sales_count, sum(sales_amount) as sales_amount
from sales s, product p where s.prod_id =p.prod_id group by s.prod_id) as a
(select p.prod_id as product_id,sum(prod_act_amt) as prod_amt from orders o,products p
where o.prod_id =p.prod_id group by p.prod_id) as b
a.prod_id = b.prod_id

i created 2 query subjects for each sub query and created determinants on prod_id. 

 select s.sales_id as sales_id,count(sales_id) as sales_count, sum(sales_amount) as sales_amount
from sales s, product p where s.prod_id =p.prod_id group by s.prod_id

above query as sales_product query subject

select p.prod_id as product_id,sum(prod_act_amt) as prod_amt from orders o,products p
where o.prod_id =p.prod_id group by p.prod_id

above query as order_product query subject. 

These 2 query subjects are joined by a.prod_id = b.prod_id. 

Finally sales_orders query subject is created from sales_product and order_product query subjects.

Now, the new requirement is to filter these sub queries something like below

select s.sales_id as sales_id,count(sales_id) as sales_count, sum(sales_amount) as sales_amount
from sales s, product p where s.prod_id =p.prod_id and s.region='USA'
group by s.prod_id

user would like to filter on any column in sales or product . Same with the second query.

So how can we implement that in the FM model. 

 

Thanks inadvance

 

 

 

 

 

 

  • bargreen
    bargreen
    9 Posts

    Re: Cognos FM

    ‏2014-03-11T17:28:28Z  

    What studio is the customer going to use? If it is Query Studio, they can define their own filters. If Report Studio, the author can create a prompt.

    You can also create filters in FM and publish them with your package, then the user can simply use them in the studios.