Search code examples
sql-serverssascube

SSAS dependent FormatString to display different unit of values

I'm working on a SSAS cube for contracts for an energy company.

One measure is "Contract volume". They have both electricity and gas contracts. So the unit of contract volume is either km3 or MWh.

The measure "Contract volume" should preferably not be split on into Gas and El, but be kept in the same measure.

How do I show the different units to the enduser? Preferably I would use FormatString that is dependent on the contract type (the information is both in the underlying fact table and in a dimension). Can I do that?

Is there anyway I can make make gas aggregable with gas and el aggregable with el - but not gas aggregable el?

Edit: SQL Server 2008 R2 Multi dimensional

Edit: Thanks @Mike Honey Here is the code I ended up writing in the Script View:

SCOPE 
    (
        {[Measures].[Measure 1],
        [Measures].[Measure 2],
        [Measures].[Measure 3],
        [Measures].[Measure 4]}
    );

    SCOPE([Contract Type].[Contract types].[Energy].&[El].Children);
    FORMAT_STRING ( This ) = "#,##0.00 \k\W\h;-#,##0.00 \k\W\h";
    END SCOPE;
    SCOPE ([Contract Type].[Contract types].[Energy].&[El]);
    FORMAT_STRING ( This ) = "#,##0.00 \k\W\h;-#,##0.00 \k\W\h";
    END SCOPE;

    SCOPE([Contract Type].[Contract types].[Energy].&[Gas].Children);
    FORMAT_STRING ( This ) = "#,##0.00 k\m\3;-#,##0.00 \k\m\3";
    END SCOPE;
    SCOPE ([Contract Type].[Contract types].[Energy].&[Gas]);
    FORMAT_STRING ( This ) = "#,##0.00 \k\m\3;-#,##0.00 \k\m\3";
    END SCOPE;
END SCOPE;
Solution

  • I think you have 2 challenges - but both can be solved with a pair of SCOPE statements.

    In the Cube's Calculations tab, I would first create a Calculated Member on the Measures hierarchy, with a value Expression of NULL. Then (in Script View) I would add a pair of SCOPE statements (for Gas and El). The Subcube expression for each would specific the new Calculated Measure, and also the dimension member that specifies Gas and EL respectively.

    Then inside each SCOPE statement you can specify the source measure for Gas and El using the This = syntax, and then specify the measure format using FORMAT_STRING ( This ) = syntax.

    SCOPE ... END SCOPE can be nested, so if you prefer, you can add wrap those 2 SCOPEs in extra parent SCOPE which specifies the calculated measure, so you don't have to repeat that measure name.