How to display string measure in crosstab from report studio and query studio

Check list of all posts

1. Requirement

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.

2. Framework manager

Though we can define calculation in report studio or query studio, the better way is to build this logic into framework manager.

Database

Framework manager

Database tier

Business tier

Please note that aggregate is set as sum. Otherwise we could experience problems in query studio when adding values for conditional style.

Dimensional tier

Presentation tier

3. 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'

end

4. 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.