Assume that there are two dimensions D1 and D2, and a Fact table F,
Cross tab report displays from report studio
Cross tab report displays from query studio
While there is no problem for TM1 string measures, there is no native support in crosstab report, in either query studio or report studio. The solution is to turn strings into number, use the converted number in crosstab, then turn the number back to string. This solution provided in this blog can work very well against limited values. In case when there are unlimited values, the possible solution is to turn string into number by calculation, then somehow transform number back to string again.
- Framework manager
Though we can define calculation in report studio or query studio, the better way is to build this logic into framework manager.
Please note that aggregate is set as sum. Otherwise we could experience problems in query studio when adding values for conditional style.
- Report studio
Create a simple crosstab with dimension D1, D2 and measure as M1_Number
Select intersection and make Define Contents as yes
Unlock cell and then drag a layout calculation into cell
case when [Query1].[M1_Number]=1 then 'green'
when [Query1].[M1_Number]=2 then 'yellow'
when [Query1].[M1_Number]=3 then 'red'
- Query studio
Create list below
Make list “Provital “ as Crosstab
Define conditional style as below
By default, summary will be presented, we can disable summary by un-checking two options below.