Top N Records

Top N Records

• asureus

  There should be a couple of ways, but what i would probably do is to create a sub report which is a child of a report that displays "Profit". I would send value of "Profit" to the subreport and the subreport would simply display top N records according to that profile parameter value.

  
  

• Upsetian

  Thanks for you reply.

  Could you explain me in more details

  This is the query. I have not used store procedure.

  i have to implement the same query in ODBC.

  select CUST_ACCOUNT_NUMBER, CUST_ACCOUNT_NAME,rev_source,sum(total_amt),sum(total_Cost),sum(profitability)

  from

  (

  SELECT

  CST.CUST_ACCOUNT_NUMBER, CST.CUST_ACCOUNT_NAME, 'SALES' as rev_source,

  SUM (LINE.LINE_Amount) TOTAL_AMT,

  ISNULL(SUM (AP.TOTAL_ASSEMBLED_COST),0) TOTAL_COST,

  ISNULL(SUM (LINE.LINE_Amount),0) - ISNULL(SUM (AP.TOTAL_ASSEMBLED_COST),0) AS PROFITABILITY

  FROM CUSTOMER_ACCOUNTS CST, INVOICES INV, ASSEMBLED_PRODUCTS AP,

  INVOICE_LINES LINE, QUICK_CODES INV_TYP, QUICK_CODES ITEM_TYPE

  WHERE

  INV.INVOICE_DATE BETWEEN '01-jan-2005' AND '31-dec-2006'

  AnD INV.INVOICE_ID = LINE.INVOICE_ID

  AND INV_TYP.CATEGORY = 'INVOICE_SOURCE_TYPES'

  AND INV_TYP.QUICK_CODE_ID = INV.INVOICE_SOURCE_TYPE_ID

  AND INV_TYP.QUICK_CODE = 'SALES'

  AND INV.Bill_To_Account_ID = CST.Cust_Account_ID

  AND ITEM_TYPE.CATEGORY = 'INVOICE_ITEM_TYPES'

  AND ITEM_TYPE.QUICK_CODE_ID = LINE.INVOICE_ITEM_TYPE_ID

  AND ITEM_TYPE.QUICK_CODE = 'MODEL'

  AND LINE.Invoice_Item_ID = AP.Assembled_Product_ID

  group by CST.CUST_ACCOUNT_NUMBER , CST.CUST_ACCOUNT_NAME

  union

  SELECT

  CST.CUST_ACCOUNT_NUMBER, CST.CUST_ACCOUNT_NAME, 'CONTRACTS' as rev_source,

  SUM (CL.CONTRACT_LINE_AMOUNT - ISNULL(CL.TOTAL_LINE_DISCOUNT_AMOUNT,0)) TOTAL_AMT,

  ISNULL(SUM (SD.AMOUNT),0) TOTAL_COST,

  ISNULL(SUM (CL.CONTRACT_LINE_AMOUNT - CL.TOTAL_LINE_DISCOUNT_AMOUNT),0) -

  ISNULL(SUM (SD.AMOUNT),0) AS PROFITABILITY

  FROM

  CUSTOMER_ACCOUNTS CST,ASSEMBLED_PRODUCTS AP,CONTRACTS CNT ,CONTRACT_LINES CL

  LEFT OUTER JOIN SUPPORT_ORDERS SO ON CL.CONTRACt_ID = SO.CONTRACT_ID AND SO.COVERED_PRODUCT_ID = CL.COVERED_PRODUCT_ID

  LEFT OUTER JOIN SUPPORT_DEBRIEFS SD ON SO.SUPPORT_ORDER_ID = SD.SUPPORT_ORDER_ID and chargeable_Flag = 0,

  COVERED_PRODUCTS CPD,QUICK_CODES STS

  WHERE

  CNT.CONTRACT_ISSUED_DATE BETWEEN '01-jan-2005' AND '31-dec-2006'

  AND CNT.BILL_TO_ACCOUNT_ID = CST.CUST_ACCOUNT_ID

  AND CNT.CONTRACT_ID = CL.CONTRACT_ID

  AND CL.COVERED_PRODUCT_ID = CPD.COVERED_PRODUCT_ID

  AND CPD.ASSEMBLED_PRODUCT_ID = AP.Assembled_Product_ID

  AND STS.CATEGORY = 'CONTRACT_STATUS'

  AND STS.QUICK_CODE_ID = CNT.CONTRACT_status_code_ID

  AND STS.QUICK_CODE = 'OPEN'

  group by CST.CUST_ACCOUNT_NUMBER, CST.CUST_ACCOUNT_NAME

  union

  SELECT CST.CUST_ACCOUNT_NUMBER, CST.CUST_ACCOUNT_NAME,'SUPPORT' as rev_source,

  isnull(SUM (SC.LINE_TOTAL),0) TOTAL_AMT, isnull(SUM (SD.AMOUNT),0) TOTAL_COST,

  isnull(SUM (SC.LINE_TOTAL),0) - isnull(SUM (SD.AMOUNT),0) as profitability

  FROM CUSTOMER_ACCOUNTS CST, ASSEMBLED_PRODUCTS AP,

  COVERED_PRODUCTS CPD,SUPPORT_ORDERS SO

  LEFT OUTER JOIN SUPPORT_DEBRIEFS SD ON SO.SUPPORT_ORDER_ID = SD.SUPPORT_ORDER_ID

  LEFT OUTER JOIN SUPPORT_CHARGES SC ON SC.SUPPORT_ORDER_ID = SO.SUPPORT_ORDER_ID

  WHERE SO.PROBLEM_OCCURED_DATE BETWEEN '01-jan-2005' AND '31-dec-2006'

  and SO.CUST_ACCOUNT_ID = CST.CUST_ACCOUNT_ID AND SO.CONTRACT_ID is null

  AND SO.COVERED_PRODUCT_ID = CPD.COVERED_PRODUCT_ID

  AND CPD.ASSEMBLED_PRODUCT_ID = AP.Assembled_Product_ID

  group by CST.CUST_ACCOUNT_NUMBER, CST.CUST_ACCOUNT_NAME

  ) cstxpro

  where (total_amt != 0 or total_cost != 0 or profitability != 0)

  group by CUST_ACCOUNT_NUMBER, CUST_ACCOUNT_NAME,rev_source

  order by sum(profitability) desc

  
  
  
• Top N Records