Friday, 14 May 2021

query calculation and layout calculation

Query Calculation is created within the Report Query by using data items from the package. Layout Calculation is created only by using data items from the report query but not from the package. 1. Create a simple list report using following data items from gosales (query) package. 2. Drag and drop "Query Calculation" from the "Tools" tab. Name it as "Query Calculation" and add "Revenue" data item into it. 3. Now, drag and add "Layout Calculation" from the "Tools" tab. If you notice, you will only see data items from the report query but not from the package. 4. Run the report and verify the results. Please note that, for the output generated above, last column "Layout Calculation" will not be part of the SQL. Data displayed in the last column is processed locally on the Cognos Server. Even, you cannot see the last column in the report query. SQL: ----- select T0.C0 Product_line, T0.C1 Product_type, T0.C2 Revenue, T0.C2 Query_Calculation from ( select coguda00.PRODUCT_LINE_EN C0, coguda01.PRODUCT_TYPE_EN C1, sum(coguda10.QUANTITY*coguda10.UNIT_SALE_PRICE) C2 from GOSALES.PRODUCT_LINE coguda00, GOSALES.PRODUCT_TYPE coguda01, GOSALES.PRODUCT coguda02, GOSALES.PRODUCT_NAME_LOOKUP coguda03, GOSALES.PRODUCT_SIZE_LOOKUP coguda04, GOSALES.PRODUCT_BRAND coguda05, GOSALES.PRODUCT_COLOR_LOOKUP coguda06, GOSALES.ORDER_DETAILS coguda10, GOSALES.ORDER_HEADER coguda11 where coguda02.PRODUCT_NUMBER=coguda10.PRODUCT_NUMBER and coguda11.ORDER_NUMBER=coguda10.ORDER_NUMBER and coguda00.PRODUCT_LINE_CODE=coguda01.PRODUCT_LINE_CODE and coguda01.PRODUCT_TYPE_CODE=coguda02.PRODUCT_TYPE_CODE and coguda02.PRODUCT_NUMBER=coguda03.PRODUCT_NUMBER and coguda06.PRODUCT_COLOR_CODE=coguda02.PRODUCT_COLOR_CODE and coguda04.PRODUCT_SIZE_CODE=coguda02.PRODUCT_SIZE_CODE and coguda05.PRODUCT_BRAND_CODE=coguda02.PRODUCT_BRAND_CODE and coguda03.PRODUCT_LANGUAGE=N'EN' group by coguda00.PRODUCT_LINE_EN, coguda01.PRODUCT_TYPE_EN) T0 5. We can apply aggregate functions like Total, Count, Rank, Maximum, and Minimum to the Query Calculation but for Layout Calculation, they are all disabled. Aggregate on Query Calculation: Aggregate on Layout Calculation: 6. Also, we cannot even apply filters, grouping, sectioning, sorting on the Layout calculation but you can apply all of them to the Query Calculation.

No comments:

Post a Comment