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