How to pass two or more than one variables from crosstab in active report

Check list of all posts

Please see sample below. Chart is driven by crosstab below, when user click any cell in the area of national gross sale, the whole row will be highlighted, and the chart above will be changed accordingly.

Obviously, we need to pass two variables from crosstab. The one is region: national, east, west etc. the other is measure method: Gross sales, Gross profit, net profit.  We cannot pass combined variable, as active report has no functionality to parse the variable and split it into two, also it can be very trick in some cases. The safest way to publish three variables: 1) combined; 2) region and 3) method.

This document will use a few screenshot to demonstrate a solution to resolve this issue. The key is to make both region and method as crosstab node member behind the scene. Please note that make property in crosstab row does NOT work.

Make query as a unified query to serve crosstab, where there are three query items : Row name – combined; row code – region; measure type - method

 

Set all three variables in crosstab

By default, row name is crosstab node member, this is easy to understand.

The solution is to make both region and method as crosstab member as well, but make their width as 0, and therefore not show on crosstab. Because row name is the combination of region and method, or lowest granularity, each row will have a single region and single method. Add these two FAKED crosstab member is only serve the propose of passing these two variables. Please see screenshot below