Make expression reusable
Expression is used everywhere in Cognos report, including query item and filters.
IntroductionMost of report is involved with dates. There are two approaches to handle it: 1) create a date control query, and then use this query to join with main query to filter out data. The major advantage is to change date logic in single date control query. Once changed date logic, all main queries joined with date control query will be changed accordingly. The main disadvantage is that performance could be an issue, as data is filtered out by join condition. 2) to resolve performance issue, you need to add date logic in all main queries. However, this brings a main disadvantage of maintenance. When date logic is requested to change, you need to change many places.
To get better performance and maintenance, we can use prompt token to resolve this issue. The idea is to save date expressions in parameters, then use #Prompt()# in query items or filters. When change date logic, you will change it in single place. At the same time, you can get good performance as well, because the filters are built into query.
Shared expression within single reportThe sample below is to demonstrate how to save expression in prompt for query items. Note that you can save any expression you want, as long as they can be used in query item and filters. Please down report specification (version 10.2) for review in detail.
Step 1: Create prompt QueryItemX as below
Step 2: Create prompt QueryItemY as below
Step 3: Put QueryItemX and QueryItemY into a table and make it hidden
Step 4: Create a new query using Prompt token
Step 5: run query as crosstab
Shared expressions in different reportIs it possible to share expressions for another report, the answer is YES. Theoretically, we can define all expressions in one report in a shared report library, and then share this report in all other reports via layout component reference. Please download the report specification version 10.2 for review in detail
Step 1: Open report above, add shared expression for the table as name for sharing, both prompts are contained in this table
Step 2: Use layout component reference from report above
Step 4: Make the whole section hidden
Step 5: Create new query using parameters defined from shared expressions
Step 6: run report with these shared expressions
Based on the discussion above, it is clear to architect report to apply this approach as follows: 1) Create a report in report library, with different block, where each block contains a group of parameters. Name each block with clear name convention. 2) All other reports can use layout component reference from different blocks, based on requirement. More than one block can be referenced to a report.