SSAS dependent FormatString to display different unit of values

Question

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;

Is this SSAS Multi-Dimensional or Tabular? – Mike Honey Apr 04 '16 at 06:17 — Mike Honey, Apr 04 '16 at 06:17

score 3 · Answer 1 · answered Apr 02 '16 at 07:24

3

I think you can use SCOPE for this: https://msdn.microsoft.com/en-us/library/ms145515.aspx

In the cube you can select calculations. This is where you can use scope to create these different units.

answered Apr 02 '16 at 07:24

Jeffrey

412
2
10

score 3 · Accepted Answer · answered Apr 06 '16 at 01:01

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.

SSAS dependent FormatString to display different unit of values

2 Answers2