-
Context
There is no problem to make measures be selected as needed. Please see ex-post (How to design Cognos interactive reports - Make measures flexible) for detail reference. The solution for both list and crosstab is to apply style variable, and therefore to control whether measure is going to show or not. The problem is that we still need to query data whether data is needed or not. As most of report naturally aggregates data, the query to bring data from database could become a major performance. This document will demonstrate the idea to make query only retrieve data when needed. Please see attached report specification for review (version 10.2 with sample Package GoSales) . The whole sample is based on report (Make measures flexible), and we’ll be focus on query only.
Sample
Two crosstab above are generated based on selected measures. The one is with columns, while another is with rows.
Query
Coumn: #prompt ('pColumn','token')#
Year: [Sales (query)].[Time].[Year]
Revenue : if (#sq(promptmany ('pMeaure'))# contains 'Revenue' ) then ([Sales (query)].[Sales].[Revenue]) else(0)
Product Cost: if (#sq(promptmany ('pMeaure'))# contains 'Cost' ) then ([Sales (query)].[Sales].[Product cost]) else(0)
Generated SQLs
To see exactly what was generated in SQL, we can turn on tool such as SQL profilers to check generated SQL statement when click reprompt . From sample SQL below, we can see it is so effective to apply this idea, as not used column is simply as 0 without any logic and any aggregation applied.
Select Revenue Only:
SELECT coguda00.product_line_en AS Product_linekey,
TIME_DIMENSION11.current_year AS Yearkey,
Sum(coguda20.quantity * coguda20.unit_sale_price) AS Revenue,
0 AS Product_cost
FROM gosales.product_line coguda00, ......
WHERE ......
GROUP BY ......
Select Cost Only:
SELECT coguda00.product_line_en AS Product_linekey,
TIME_DIMENSION11.current_year AS Yearkey,
0 AS Revenue,
sum(coguda20.QUANTITY * coguda20.UNIT_COST) AS Product_cost
FROM gosales.product_line coguda00, ......
WHERE ......
GROUP BY ......
Select both revenue and Cost:
SELECT coguda00.product_line_en AS Product_linekey,
TIME_DIMENSION11.current_year AS Yearkey,
Sum(coguda20.quantity * coguda20.unit_sale_price) AS Revenue,
sum(coguda20.QUANTITY * coguda20.UNIT_COST) AS Product_cost
FROM gosales.product_line coguda00, ......
WHERE ......
GROUP BY ......
No comments:
Post a Comment