Select not null values in a single column in MDX

There is a keyword 'NON EMPTY' in MDX which returns all rows where at least one column has a non null value in it. But if we instead want to select all rows where value of a single column is not null then we can use the function NONEMPTY() like so:

SELECT  
  { 
    [Measures].[Hits] 
   ,[Measures].[Subscribers] 
   ,[Measures].[Spam] 
  } ON COLUMNS 
 ,{ 
    NonEmpty 
    ( 
      [Geography].[Country].Children 
     ,[Measures].[Subscribers] 
    ) 
  } ON ROWS 
FROM [Blog Statistics];

Reference: http://beyondrelational.com/modules/2/blogs/65/posts/11569/mdx-non-empty-vs-nonempty.aspx

Format Axis labels in High Charts

There are a number of formatting options in HighCharts.

To format a data label, formatter can be used as demonstrated at http://stackoverflow.com/questions/24708198/how-to-format-highcharts-datalabels-decimal-points.

A tooltip formatter formats tooltip e.g. http://stackoverflow.com/questions/16991335/highcharts-tooltip-formatter

To format axis labels a formatter can be configured as

formatter: function () {

    var ret = '',multi,axis = this.axis,numericSymbols = ['k', 'M', 'G', 'T', 'P', 'E'],

        i = numericSymbols.length;

    while (i-- && ret === '') {

        multi = Math.pow(1000, i + 1);

        if (axis.tickInterval >= multi && numericSymbols[i] !== null) {

            ret = Highcharts.numberFormat(this.value / multi, -1) + numericSymbols[i];

        }

    }

    if (ret === '') ret = this.value;

    return ret;

}

I have created a fiddle to demonstrate this at http://jsfiddle.net/Lng7w9v0/

MDX parallel period query

After experimenting for some time with MDX parallel period function I could finally write a query which returns two parallel periods as separate rows and not as another column in the same row.

The final query looks like this

SELECT {  [Measures].[Internet Sales Amount] } ON COLUMNS,

NON EMPTY{[Date].[Calendar Weeks].[Calendar Week].ALLMEMBERS} on rows

FROM (

SELECT { ParallelPeriod ([Date].[Calendar Weeks].[Calendar Year]

   , 1 

   , [Date].[Calendar Weeks].[Calendar Week].&[3]&[2012]) , [Date].[Calendar Weeks].[Calendar Week].&[3]&[2012] }

   ON 0 

   FROM [Adventure Works]  )

But interestingly along the way I could figure out another not very idealistic method of achieving the same results, mentioning here just in case it can be used by someone someday

SELECT {  [Measures].[Internet Sales Amount] } ON COLUMNS,

NON EMPTY{[Date].[Calendar Weeks].[Calendar Week].ALLMEMBERS} on rows

FROM (

SELECT ({ Filter( [Date].[Calendar Weeks].ALLMEMBERS - [Date].[Calendar Weeks].[(All)].ALLMEMBERS, Instr( [Date].[Calendar Weeks].currentmember.Properties( 'Member_Caption' ), 'Week 3 ' )  = 1 ) } ,{ [Date].[Calendar Year].&[2011], [Date].[Calendar Year].&[2012] } )

   ON 0 

   FROM [Adventure Works]  )

Knockout Highcharts could not find rendering div

When using knockout with HighCharts, a frequest issue is that highcharts library is unable to find the rendering div simply since it gets loaded earlier than the knockout dom.

I found a blog which explains a custom knockout binding handler to fix just this specific issue

The binding handler is

ko.bindingHandlers.initHighCharts ={
        init: function(element, valueAccessor, allBindings, viewModel, bindingContext) {
            var val = ko.unwrap(valueAccessor());
            if(val.data){
                try {
                    $(element).highcharts(val.data);
                } catch(e){
                    console.log(e);
                }
            }
        },
        update: function(element, valueAccessor, allBindings, viewModel, bindingContext) {

        }
    };

And the html code is

<div class="row">
<div class="col-md-10 no-margin">
    <div class="well container">
        <div data-bind="initHighCharts: {data: chartingOptions}"></div>
    </div>
</div>
</div>

MDX query for parallel period

I have been trying to figure out parallel period queries in MDX, and they seem to work nice, however I noticed another way of achieving similar results and just noting it here for my future reference.

Solution 1

The first solution is to declare members using "Cousine" function and specify "Ancester".

WITH 
  MEMBER [Measures].[Transaction of Selected Date] AS 
    (
      [Date].[Calendar].CurrentMember
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Transaction of Previous Year's same Date] AS 
    (
      Cousin
      (
        [Date].[Calendar].CurrentMember
       ,Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Year]
        ).Lag(1)
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Transaction of Selected Date]
   ,[Measures].[Transaction of Previous Year's same Date]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].[September 1, 2003];

Ref: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26f056f-1bc3-49e4-8733-fe11d15cafec/parallel-period-of-a-date-range?forum=sqlanalysisservices

Solution 2

Second solution is to declare members using "ParallelPeriod" function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Calendar Year]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003];

Solution 3

Third solution is to declare members using only "Lag" function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      [Date].[Calendar].CurrentMember.Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE  ([Date].[Calendar].[Calendar Year].&[2003]);


Ref: http://aniruddhathengadi.blogspot.co.uk/2011/03/retrieve-selected-year-data-and-same.html

Javascript date range of last three quarters and current quarter till yesterday

This is pretty small code, but took me a while to figure it out. So mentioning here just in case I would need to use it in the future.

// Show last 3 quarters (plus this quarter)
endDate = new Date();
var noOfQuarters = 3;
var quarter = Math.floor((endDate.getMonth() + 3) / 3);
startDate = new Date(endDate.getFullYear(), endDate.getMonth() - (noOfQuarters * 3 + endDate.getMonth() - (quarter - 1) * 3));

Locate Dll deployed through sharepoint solution in GAC

A sharepoint solution can have dll files which can either be deployed to the bin folder of IIS web site or to GAC.

Sharepoint 2013 solution dlls deployed to GAC can be found at “c:\windows\microsoft.net\assembly\GAC_MSIL” if dll build platform is Any CPU
and at “c:\windows\microsoft.net\assembly\GAC_64” if dll build platform is 64 bit

More information can be found at http://www.ashokraja.me/post/Locating-custom-assemblies-built-for-SharePoint-2013-in-GAC.aspx