Saturday, July 13, 2013

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

  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.

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

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

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

1 comment:

  1. It's fantastic that you are getting thoughts from this paragraph as well as from our dialogue made at this time.

    Also visit my weblog ... tüm gazeteler