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

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
    ACCEPTED ANSWER

    Re: Cognos FM

    ‏2014-03-11T17:28:28Z  in response to prat123

    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.