Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Thursday, March 3, 2011

Named Set for Months in Current Financial Year

ORDER(
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN  CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE  CSTR(CINT(DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT(Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Financial Period].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

No comments:

Post a Comment