MDX select difference between two date dimesions

I wanted to select difference between two date dimensions in MDX, where value of a KPI measure is not empty, after some tries I could figure out the query as seen below:

  WITH

MEMBER[Measures].[dif] as Datediff("d",[Delivery Date].[Date].currentmember.MEMBERVALUE,[Ship Date].[Date].currentmember.MEMBERVALUE)

SELECT NON EMPTY { [Measures].[dif], KPIValue("Product Gross Profit Margin") } ON COLUMNS,  { NonEmpty

    ([Delivery Date].[Date].[Date].ALLMEMBERS * [Ship Date].[Date].[Date].ALLMEMBERS, KPIValue("Product Gross Profit Margin") ) }

ON ROWS FROM [Adventure Works]

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

Database schema reader

Very often we need to read information about database schema such as column data type, table names, and convert them to poco classes.

The library available at http://dbschemareader.codeplex.com/ has many of the most commonly used features for such a purpose, this can reduce coding efforts to generate poco classess for EF. It can also be customized to a new data access layer in our project.


Also, there is a utility available on codeproject to view database schema in windows application at http://www.codeproject.com/Articles/23053/View-Database-Structure-Using-C

Export webparts in a sharepoint page

A sharepoint page has markup and webparts zones inside the markup. When a user adds webparts to the web part zones, the markup for added webparts is not stored directly inside the page, but rather inside database tables. Hence the webpart markup can not be seen if users download a copy of the page, neither does sharepoint designer show webpart markup. 

There are few situations when we need the webparts markup, e.g. copying page to some other farm, backup on local folder, etc. The blog at https://chuvash.eu/2014/09/19/export-any-web-part-from-a-sharepoint-page/ mentions several different ways to export webparts in a page, in particular I liked the idea of running javascript at https://github.com/mirontoli/sp-lend-id/blob/master/pajla/sp-export-webpart.js in console window.

However since my old days with SharePoint 2007 I know another method to export all webparts in a sharepoint page along with markup of the webpart page itself. Here it goes:

1) Open the document library (Pages library in most cases) in SharePoint designer

2) Copy the webpart page say page1.aspx and paste it, this creates a new webpart page with name like page1_copy(1).aspx

3) Right click on page1_copy(1).aspx and select "Detach from Page Layout" option

 4) Click "Yes" to the dialog prompt

5) Right click on page1_copy(1).aspx and choose "Open" or "Edit file in Advanced Mode"

6) Copy contents and paste in a local file. 

Get public key token of an assembly using visual studio

This blog explains a very convenient way to get public key token of a visual studio project assembly.
I am repeating here since path has changed a little.

In short: Add an external tool in visual studio with the following properties

1. Title: Get &PublicKeyToken
2. Command: C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6 Tools\sn.exe
3. Arguments: -Tp $(TargetPath)

Reference:

https://blogs.msdn.microsoft.com/kaevans/2008/06/18/getting-public-key-token-of-assembly-within-visual-studio/

Modify SharePoint application page using IHTTPModule

There are various ways to modify a sharepoint oob application page, with pros and cons, but the method described in blog post below is one of the cleanest ways I have come across.

https://blogs.msdn.microsoft.com/sasohail/2010/12/18/modifying-sharepoint-application-pages-in-the-right-way/

Post complex objects as parameters to web api controllers

I have an API project and want to call a web method asynchroneously from client project.

The easiest way to do this is to use code like

HttpClient.PostAsJsonAsync<T>(T value) sends “application/json”
HttpClient.PostAsXmlAsync<T>(T value) sends “application/xml”

Widget widget = new Widget()
widget.Name = "test"
widget.Price = 1;

HttpClient client = new HttpClient();
client.BaseAddress = new Uri("http://localhost:44268");
client.PostAsJsonAsync("api/test", widget)
    .ContinueWith((postTask) => postTask.Result.EnsureSuccessStatusCode() );

Copied from http://stackoverflow.com/questions/10304863/how-to-use-system-net-httpclient-to-post-a-complex-type

To call a method with simple parameters use

string url = "http://myserver/method";    
string content = "param1=1&param2=2";
HttpClientHandler handler = new HttpClientHandler();
HttpClient httpClient = new HttpClient(handler);
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, url);
HttpResponseMessage response = await httpClient.SendAsync(request,content);

Copied from http://stackoverflow.com/questions/27376133/c-httpclient-with-post-parameters

To debug the posted JSON, use JObject parameter type at the web api method

/* Production */
public HttpResponseMessage TrackingRecord([FromBody] TrackingRecord record)
{
    ....
}

/* Debugging */
public HttpResponseMessage TrackingRecord([FromBody] JObject json)
{
    ....
}

Copied from http://forums.asp.net/t/2042177.aspx?Posting+Json+from+C+app+to+Web+API+fails

View Excel Pivot table mdx queries

Excel pivot table is a great tool to create pivot charts easily and quickly, however it does not have a OOB button to view the mdx  query. But thankfully there are many tools available to help us here.



http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx

http://olappivottableextend.codeplex.com/

http://webpivottable.com/



The posts below mention some of them.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7c2927c8-0e4a-4658-a3d4-55691a613471/how-to-view-mdx-query-in-excel-sheet?forum=sqlanalysisservices
http://stackoverflow.com/questions/2863436/how-can-i-display-the-mdx-generated-by-excel-2007-when-using-a-pivot-table

WCF web service content type error

After adding a new wcf service to my MVC application, when I tried to post a request, the application responded with error:

POST http://localhost:63342/ISAPI/Service1.svc/MyMethod 415 (Cannot process the message because the content type 'application/json' was not the expected type 'text/xml; charset=utf-8'.)

It can be resolved by adding Factory="System.ServiceModel.Activation.WebServiceHostFactory" to the .svc file markup like so:

<%@ ServiceHost Language="C#" Debug="true" Service="MyWebsite.ISAPI.Service1" CodeBehind="Service1.svc.cs" Factory="System.ServiceModel.Activation.WebServiceHostFactory" %>