Format numbers function for SQL

Handy function for formatting numbers in SQL. It can be customized easily according to your need.

ALTER FUNCTION [dbo].[ufn_formatNumber](@num float)
RETURNS varchar(20)
AS
-- Returns the number formatted to K,M or () in case of negative numbers
BEGIN
Declare @out varchar(20)
     IF (@num IS NULL)
        SET @out = 0;
       
     IF (abs(@num)<=10000)    -- Thousand Handler
     SET @out='$' +cast(abs(round(@num,2)) as varchar)
       
     IF (abs(@num)>10000)    -- Thousand Handler
     SET @out='$' +cast(abs(round(@num/1000,2)) as varchar)+' K'
   
     IF (abs(@num)>1000000)  -- Million Handler
     SET @out='$' +cast(abs(round(@num/1000000,2)) as varchar)+' M'
   
     IF (@num<0)
     SET @out= '('+@out +')'
   
   
    RETURN @out;
END;
GO

Format Numbers in Millions and Thousands

Here is simple and esay to use javaScript function which formats numbers to M and K and also adds brackets to negative numbers. Very handy and easu to use function to nicley fomat numbers on your webpage.


function formatNumbers(_num, _round) {
    var isNegative = false;
    var num = Number(_num);
    var suffix = "";
    var numOut;
    if (num < 0)
        isNegative = true;

    num = Math.abs(num);

    if (num > 1000000) {
        suffix = " M";
        num = Math.round(num / 1000000, _round);
    }
    else if (num > 10000) {
        suffix = " K";
        num = Math.round(num / 1000, _round);

    }
    else {
        num = Math.round(num, _round);
        suffix = "";
    }

    /************* Add brackets to negative numbers ***************/
    if (isNegative) {
        numOut = "(" + num + ")";
    }
    else
    {
        numOut = num;
    }

    numOut += suffix;
    return numOut;

}



This can be easily customized according to your need with out using any heavy library file

Center Align Microstrategy Dashboard using HTML page and iFrame

If you want to center align your dashboard in microstrategy and you are using express mode, than simple jQuery trick won't work inside an HTML container. Now, you would need some thing more. This can be done using a simple HTML page which will point to your MSTR dashboard using iFrame.

Steps:

1. Create a html file and paste the following code


<style type="text/css">
body {
overflow:hidden;
margin:0px;
}
</style>

<iframe id='db1' src="https://yourservername.com/MicroStrategy/servlet/mstrWeb;jsessionid=6EA16051877A9FDDFD626CEEB093DF20?Server=yourMSTRServerName&Project=yourProjectName&Port=0&evt=2048001&src=mstrWeb.2048001&visMode=0&currentViewMedia=1&documentID=0OP2376F412239HJ12AB8D9616930CE8"

style='height:102%;border:none;' width="100%" height="102%"></iframe>
<script>

document.addEventListener("DOMContentLoaded",init);


function init() {
var dbWidth=Number(1200);
var w=window,d=document,e=d.documentElement,g=d.getElementsByTagName('body')[0],
x=w.innerWidth||e.clientWidth||g.clientWidth,
y=w.innerHeight||e.clientHeight||g.clientHeight;
elem = document.getElementById("db1");
var leftMargin=(x-dbWidth)/2;
elem.style.marginLeft = leftMargin+'px';
elem.style.width=dbWidth+"px";

}


</script>


2. Now change the iFrame src with your mstr dashboard URL
3. Change the dbWidth parameter with your dashboard width
4. Save the page and host it on a webserver

Now, browse the above published page and you should see your dashboard center aligned irrespective of MSTR view mode

Center Align Microstrategy Dashboard

There are many times when you want your MSTR dashboard to be center aligned, but its not. If your dashboard is viewed in Interactive mode then there is a simple trick to achieve same. This can be done by adding simple javascript code in a html container.  You may find same approach on other blogs, but they use jQuery. WHile you use jQuery, you need to load jQuery lbrary files which some times is a problem. Hence, I decided to write the same using only javascript.

Steps:


1. Add an HTML container in your dashboard and paste the below code into it.

<script>

document.addEventListener("DOMContentLoaded",init);

document.addEventListener("resize", init);

function init() {

var dbWidth= Number(1200);

var w=window,d=document,e=d.documentElement,g=d.getElementsByTagName('body')[0],

x=w.innerWidth||e.clientWidth||g.clientWidth,

y=w.innerHeight||e.clientHeight||g.clientHeight;

elem = document.getElementById("rwb_viewer");

var leftMargin=(x-dbWidth)/2;

elem.style.left = leftMargin+'px';

}

</script>

2. Replace the dbWidth value with your dashboard width (in this example its 1200)

3. Make the color of HTML container as your background color or place it behind some component to hide it.

4. Save the dashboard and run it in Interactive mode.

Conditional Formatting of Background color in Tableau Table

In Tableau if you want to have conditionally formatted background colors, then it’s not there if you use the simple text table. However, you can achieve the same by using bar charts and doing some simple tweaks.

  

Steps to get Conditional formatting of backgrounds in a table. 

1. Create a bar chart
2. Create a Calculated field as name “One” and in Formula type 1



3. Drag this field on to columns shelf
4. Now edit the axis for it and make it fix axis with values 0 to 1
   
5. Also remove any tick marks and make the title blank
6. Now drop your metric on Label for this Mark.
7. As per your requirement drop the metric you want to use for conditional formatting on to color in Mark tab. In this case I created a calculated measure ColorForSales with formula =” iif(sum([Order Quantity])>100,1,0)”. Which will show 1 if quantity is > 100
8. Edit colors for ColorForSales as Red to green diverging and set range as 0 - .5 – 1 and two step color as red and green
   
9. Add calculated field ‘One’ again to Columns shelf and make it fixed axis(0,1000000) and remove the axis tick marks.
10. Select Bar type for this Mark as well and make color of bar as white. As the axis range is fixed at 1 Million, the bar won’t show up. Also hide the labels from the bar
11. Set the Title for this as you need  (Sales in this case)
12. Now Make this Mark as dual axis so that the it will appear as the title for the sales Measure
13. Repeat the process for all the measures you need and your are ready with your Conditionally formatted background Table
    

If you are showing this in a dashboard then you can avoid adding a additional mark for the headers and can use text labels ( Though you may spend some time aligning them properly)