Make query reusable - revisited
ContextBesides there are a few ways to reuse query explored in part1, there is another method to reuse query. This method takes advantage of prompt token and make whole query be controllable.
In centralized report development, there are many cases where query is very similar, the only difference is to group by different level, such as territory, province, or to select different item, such as product, location. This document is intended to provide a method that all query items are controlled by a parent query. The parent query can be a simple singleton, or a complicated query.
SampleCreate a query that can be customized for any query items, including measure and filter.
Please see attach screenshot below; the report specification (version 10.2) can be downloaded for detail review.
Create a report query with prompt token
QueryItem1: #prompt ('QueryItem1','token')#
QueryItem2: #prompt ('QueryItem2','token')#
Filter1: #prompt ('Filter1','token')#
Measure1: #prompt ('Measure1','token')#
Create a control query
QueryItem1: '[Sales (query)].[Products].[Product line]'
QueryItem2: '[Sales (query)].[Retailers].[Region]'
Filter1: '[Sales (query)].[Order method].[Order method type] = ''Telephone'''
Measure1: '[Sales (query)].[Sales].[Revenue]'
Create page with parent Singleton and child crosstab
Singleton -- controlQuery
Crosstab – reportQuery
Create master detail relationship