Report specification: Relational, DMR, and Cube
Below are three separated lists with three different levels, product line, product type and product.
There are many ways to development report, assume the report in real world is much more complicated than simple revenue number.
- Option 1 Develop three queries for product line, product type and product, and three list data containers for product line, product type and product, respectively, it will end up 3 queries + 3 lists
- Option 2 makes both query and layout be shared for three lists. Once made three lists use the same query, the layout component reference can be used. It will end up 1 query and 1 list. The real advantage is to dramatically reduce the maintenance effort.
Three queries are defined as follows
Old Product line Query
Old Product type Query
Old Product Query
Create three lists uses their query, respectively.
This solution works, but it is difficult to maintain, given the fact that the real report is much more complicated that this sample. When changing list query, you need to change all three queries, and change three list layouts and keep it consistent. It is very easy to make mistake, such as miss some filters. In order to make it easy to maintain, we want to make both query and layout to be shared.
Option 2 – use Layout component reference and Prompt token macro to make both query and layout to be shared
The idea is to make a single query, and a single layout. When changing logic, you need to change only a single query and layout.
Query – make same query for all three lists using prompt token
Step 1 make a new Product level Query be generic for all three lists as below
Step 2 make three simple queries be as below
new Singleton Product line Query
new Singleton Product type Query
new Singleton Product Query
It will end up with 4 queries below
Layout – make three lists share the same layout using layout component reference
The layout overview is to have a singleton for each list as master, and each singleton contains a list. The singleton master controls what parameter to pass. See two screenshots below
Step 1 make product line list with master detail relationship; define the first list as ReusableList
Step 2 use layout Component reference to get the second list
The key point is use different singleton query, or product type
Step 3 apply the same way for product