How to handle nested data deck with total in active report

Check list of all posts

Creating nested data decks is not a problem. The issue is that the total is needed for most of time. For example, we want to create sales by region and then by year, but user want to have all  regions and all years. There are two ways to resolve this issue, each has its advantages and disadvantages.

Using unions query

The idea is to have a single data deck with dimensions, region and year. In addition, we need to have all years and all regions. You can check both report and generated mht for review.

Create data deck query

Data deck query will generate all combinations, including

1. Both region and year are specified
2. Only region is specified
3. Only year is specified
4. Neither region or year is specified

Data query need to consider total as well, the filter must be defined as optional.

if (?p_Region? ='ALL') then (1=1) else ([Sales (query)].[Retailers].[Region] =?p_Region?)

if(?p_Year? = 9999) then (1=1) else ([Sales (query)].[Time].[Year] =?p_Year?)

Layout is

The advantage is to have a same report layout, with master detail relationship. The disadvantage is that too many queries are needed. This method may not work in case when the query logic is very complicated.

Using default card

The idea is to take advantage of default card concept, and to use nested data decks. You can check both report and generated mht for review.

Create data deck query, there will be two queries for two decks, the one is region query to list all regions, the other one is qDeck that holds all combination between region and year. We don’t need to use define special logic for p_Region and p_Year, as long as we define it as two optional parameters.

The layout is complicated different, as nested decks are needed. When the region is not specified, then it is total. When year not specified, then it is all years.

Data deck query will generate all combinations, including

Case 1: Both region and year are specified

Case 2: Only region is specified

Case 3: Only year is specified

Case 4: Neither region or year is specified

Somehow, parent child relationship doesn’t work in this sample, the solution is to use container filter based on variable varRegion and varYear. There will be different for each case:

Case 1: Both region and year are specified: varRegion and varYear

Case 2: Only region is specified: varRegion

Case 3: Only year is specified: varYear

Case 4: Neither region or year is specified: no variables are needed

The advantage is to have simple queries. The disadvantage is that 4 layouts are needed. This method may be suitable for complicated query logic.