When we apply colour formatting to measures in SSAS we get a “strange” colour code for our expressions from the BIDS colour-picker. For example, if we want to colour a certain measure in Red, we can write:
CREATE MEMBER CURRENTCUBE.[Measures].[Red Measure] AS
This = 1,
If we use the colour picker we can get more complex numbers for more complex colours. If we want to apply the colour with RGB of (128,128,192), the colour picker generates the code 12615808 for use in the FORE_COLOR function.
Constructing our own color code is not all that hard. In fact, all we have to do is the following:
1. Multiply the Blue decimal code by 65536 (which is 2^16)
2. Multiply the Green decimal code by 256 (yes, 2^8)
3. Add the numbers from step 1 and 2 and the decimal code for Red
In other words, we can use the following formula:
c = r + 256*g + 65536*b
Where c is the colour code we need and r,g,b are the decimal codes (between 0 and 255) for each colour channel.
If we apply this to our (128,128,192) colour, we get:
c = 128 + 256*128 + 65536*192 = 128 + 32768 + 12582912 = 12615808
And this is exactly what we get from the colour picker control in BIDS.
As Chris Webb points out in the comments section below, we can also write FORE_COLOR=RGB(128,128,192) instead of calculating the colour code manually, which is way more practical. 🙂
I wish such little bits and pieces are better documented on MSDN as the current page in regards to the BACK_COLOR and FORE_COLOR properties is fairly basic: http://msdn.microsoft.com/en-us/library/ms145991.aspx.
Considering that RGB() is a VBA function, I tested the performance we get from using it. I have a simple measure, which I am formatting with an IIF expression. I tested three scenarios – with no FORE_COLOR formatting, with FORE_COLOR and numeric codes, and FORE_COLOR with RGB function. Each query was run 3 times on warm cache and returned a result set of 500,000+ cells. The results were as follows:
No FORE_COLOR: 2.3 seconds
FORE_COLOR=12615808: 5.3 seconds
FORE_COLOR=RGB(128,128,192): 17.7 seconds
In other words, applying a VBA function for colour formatting severely impacts the performance of our queries. Thus, if you really need to format the colour of your measures, you may be better off doing it the hard way by converting the RGB values to the colour codes the same way the colour-picker control does in BIDS.
As Valentino Vranken commented below, you can also use hex codes for the colours. However, there is a small twist. The codes need to be in BGR, not in RGB format. So, Red becomes 0000FF. The actual format of the hex expression is FORE_COLOR=”&Hxxxxxx“. For Red we can use FORE_COLOR=”&H0000FF”. There is no performance difference between hex and decimal codes, so it is up to your preference whether you go with one or the other. Converting 0000FF to decimal also shows the same decimal number (255) as performing the math above and if you already know the BGR hex you can simply convert it to a decimal.
6 thoughts on “Colour Codes in SSAS”
I always use the RGB function for this – you can just use something like:
So no need to worry about calculating colour codes at all…
Yup, I saw the funny number today and was playing with it to understand what it means and how it’s constructed. Plus, I’ve seen people trying to get the number in the past, so I thought I should share.
Still, I changed the post to say “we get the number from the colour-picker” instead of “we have to use” and I added this info as a paragraph to avoid confusion.
@Chris Webb Chris, I just measured the performance of RGB vs the codes and it seems like it really makes a difference – have a look at the update in the post.
Very interesting, especially the speed comparison!
I’d like to add another (easier) method of calculating that magic color number. Use your favorite color picker to get hold of the hexadecimal representation of the RGB numbers (e.g. 128,128,192 becomes 8080C0), then Start > Run > Calc, switch to Hex, type the values in BGR order (C08080), switch to Dec and there’s your number! 🙂
@Valentino Vranken Yes, thanks for this addition, Valentino.
By the way, commenting on my own post, if you use hex codes (format is FORE_COLOR=”&Hxxxxxx”, e.g. FORE_COLOR=”&HC08080″) performs the same as using a decimal code as outlined in the post.
Comments are closed.