2

I have a star schema with a fact table containing the following:

ID             BIGINT IDENTITY, 
FK_Dimension1  BIGINT, 
FK_Dimension2  BIGINT, 
dataValue      NUMERIC(20, 8), 
classification NVARCHAR(20)

The classification defines the security classification for each dataValue, and can contain either "public" or "confidential".

I've created a degenerate dimension which contains the classification field, and the dataValue resides in the associated measure table.

My requirement is to format the dataValue in any tool that is rendering the data as RED if any value in the aggregation of that field contains an associated classification of "confidential". So for example if in an excel pivot table I was displaying the aggregation of 3 values and one of them was confidential then the value should display as red text.

I have the following MDX query in my cube calculations to handle the formatting:

CALCULATE;

SCOPE
(
  [Measures].[dataValue]
);

If([MyDegenDimension].[Classification].[Confidential]) Then
  Fore_Color(This) = 255
End If;

END SCOPE;

This works great whenever there are values in the dataValue field other than zero. If I drill down in my pivot table to show a single value that happens to be zero then the color formatting does not apply. The business requirement is that any confidential value should be flagged as red, and it's valid that a zero value could be confidential. Note it displays the dataValue as red even if the classification dimension is not used in the pivot table.

Is there any way I can alter my MDX query so that it works for zero values as well? I've posted a similar question a while back on SO here Calculation in SSAS Cube not working for zero data values but never got a complete resolution.

Mike
  • 1,645
  • 3
  • 13
  • 21
Greg the Incredulous
  • 1,676
  • 4
  • 29
  • 42
  • 1
    can you confirm that this is happening for "real 0" (+1-1=0 etc.) values rather than just "no value" entries? if [dataValue] is coming out as null it wont be coming through in the `SCOPE`. – Lefty Dec 17 '14 at 09:05
  • @greg in the previous question what was not complete about FrankPI's answer? Have you tried the MSDN SSAS forum - it is active. – whytheq Dec 17 '14 at 09:13
  • @whytheq thanks for the suggestion - I will post there too. Previous answer only solved problem if the dimension was actually in the pivot, which was actually worse than my initial solution. The business I am working for are scared of leaking information that is commmercial in confidence, which is why this is so important to them. – Greg the Incredulous Dec 17 '14 at 11:20
  • @Lefty - for sure. I've run SQL queries to return same underlying data and I can confirm that it's a zero, rather than a null or a "nearly zero". – Greg the Incredulous Dec 17 '14 at 11:21

1 Answers1

0

I think your SCOPE statement just needs to be tweaked.

Try this:

CALCULATE;

SCOPE
(
  [MyDegenDimension].[Classification].[Confidential], [Measures].[dataValue]
);

  Fore_Color(This) = 255

END SCOPE;
Mitch Schroeter
  • 1,001
  • 5
  • 6