How to calculate YTD according to the Australian Financial Year in MDX (i.e. from July to June)?

Normally, the YTD calculation is based on the calendar year i.e. from January of the report year selected to the date selected.

I have a report requirement where the Year To Date figure is to be based on the Australian Financial Year. If the report month selected is less than July 2011, the YTD  calculation would be from 1 July 2010 to 30 June 2011. If the report month selected is greater than or equal to July 2011, the YTD calculation would be from 1 July 2011 to 30 June 2012.

MDX to do this is as below:

WITH
SET [LastDayOfMonth] AS CLOSINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(“[Date].[Monthly].[Month].&[2011]&[6]”, CONSTRAINED)  )
SET [FirstDayOfMonth] AS  OPENINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(“[Date].[Monthly].[Month].&[2011]&[6]”, CONSTRAINED)  )
SET [FromDate] AS Head(Descendants(STRTOMEMBER(“[Date].[Monthly].[Month].&[“+
  CASE WHEN CINT(Format([LastDayOfMonth].Item(0).MemberValue,”MM”))  >= 7 THEN Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)
  ELSE CSTR(CINT(Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)) – 1) END
  + “]&[7]” ), 4), 1)

SET [ToDate] AS Tail(Descendants(STRTOMEMBER(“[Date].[Monthly].[Month].&[“+
  CASE WHEN CINT(Format([LastDayOfMonth].Item(0).MemberValue,”MM”))  < 7 THEN Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)
  ELSE CSTR(CINT(Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)) + 1  ) END
  + “]&[6]” ), 4), 1)

MEMBER [Measures].[Fiscal YTD Injuries HardCoded] AS SUM({[Date].[Monthly].[Date].&[2010-07-01T00:00:00]: [Date].[Monthly].[Date].&[2011-06-30T00:00:00]},[Measures].[Injury Count])
MEMBER [Measures].[Fiscal YTD Injuries] AS
    SUM({STRTOMEMBER(“[Date].[Monthly].[Date].&[” + Format([FromDate].Item(0).MemberValue, “yyyy-MM-dd”) + “T00:00:00]”)
          : STRTOMEMBER(“[Date].[Monthly].[Date].&[” + Format([ToDate].Item(0).MemberValue, “yyyy-MM-dd”) + “T00:00:00]”) }
           ,[Measures].[Injury Count])
SELECT { [Measures].[Injury Count]
   , [Measures].[Fiscal YTD Injuries]
   , [Measures].[Fiscal YTD Injuries HardCoded]
  } ON COLUMNS
  ,{[FromDate], [ToDate] } ON ROWS
FROM INX_Cube

 

Read Full Post »

ETL SQL job failed instantly with an error “The file name .. Errorlog.Log specified in the connections was not valid”

I set this ETL SQL job to run as a service account which has read and write access to the relational database and warehouse. But it failed to run with the above error. This error is very misleading because the log file does exist. The name and the location of the log file are valid. How on earth it became invalid?

So I thought probably the issue with the permission to this file. Adding the service account to have read and write access to the whole ETL folder resolves this issue.

 

Read Full Post »