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/