DESCRIPTION:
In Strategy 9.x/10.x, derived elements could only be only created in view reports and in Report Services documents. From Strategy 9.3.0, it is possible to create derived elements in the data template of normal reports and OLAP reports in Strategy Web.
However, there is a limitation that when the element 'All Others' is unchecked in the derived element in the data template, the elements that are not used in derived elements are not displayed in the available elements list. To display those unused elements, the user needs to check the All Others to get all the data. This information is explained below with an example created from the Strategy Tutorial project.
Create a report with objects Year and Cost. Three years are returned in the grid.
In Strategy Web 9.x, right mouse click on the attribute 'Year' to create derived elements on Year. Create a new list with years 2004 and 2005. Uncheck 'All Others' so that only one row returns in the grid, as shown below.
When the derived element is edited, it is seen that the element '2006' no longer appears in the list of available elements, as shown below.
This happens because the SQL only chooses elements 2004 and 2005 in the WHERE clause. The data for the year 2006 is not obtained from the database.
select a12.YEAR_ID YEAR_ID, sum(a11.TOT_COST) WJXBFS1 from "CITY_MNTH_SLS" a11 join "LU_MONTH" a12 on (a11.MONTH_ID = a12.MONTH_ID) where a12.YEAR_ID in (2004, 2005) group by a12.YEAR_ID
41163
