MDX First and Last day Dynamically

Question

Hi Experts,

I am working on SSRS Report with SSAS as my Source.
I have a "Date" Filter on the Top on the base of which I View my Report.

Requirement:
I need Two Column in my SSRS Report which Shows 1st Day and Last Day of the Date Value Selected from Filter  Example ::

If I  select November 2011 from Date Filter
My SSRS Report Should Be
1st DAY                           Last Day    
1-November-2011          30-November-2011

=========================================

If I  select Q1 2011 from Date Filter
My SSRS Report Should Be
1st DAY                           Last Day    
1-April-2011          30-June-2011
==========================================

If I  select  2011 from Date Filter
My SSRS Report Should Be
1st DAY                           Last Day    
1-April-2011          31-March-2011
=========================================

My Date Dimension Hierarchy in Cube is

[Date].[FiscalHierarchy].[FYYear]

[Date].[FiscalHierarchy].[FYSemester]

[Date].[FiscalHierarchy].[FYQuarter]

[Date].[FiscalHierarchy].[FYMonth]

Any Help in achieving above requirement?

NOTE:: We can Take 1st and last Day(Date)  coming in Report Column  in any Standard Format .

Answer 1

WITH
MEMBER [Measures].[FirstDay] AS OPENINGPERIOD([Date].[Fiscal Date].[Date], [Date].[Fiscal Date].[Fiscal Year].&[2008]).item(0).PROPERTIES("Name")
MEMBER [Measures].[LastDay] AS CLOSINGPERIOD([Date].[Fiscal Date].[Date], [Date].[Fiscal Date].[Fiscal Year].&[2008]).item(0).PROPERTIES("Name")

SELECT {[Measures].[FirstDay], [Measures].[LastDay]} ON 0
FROM [Analysis Services Tutorial]

If you need just the first day and last day (and not the value of any measure related to first day and last day) the above logic works.

You just need to replace  [Date].[Fiscal Date].[Fiscal Year].&[2008] with the member you select in SSRS.

Hope this helps!

---

Karthik

Answer 2

Hi Karthik,

Thanks for the Reply.
Above MDX sysntex give correct output when executed in SSMS .
Issue:

1) Where and how should I used it in SSRS Report

2)Where should I create [Measures].[FirstDay] and [Measures].[LastDay],as we Cannot create this as a Calculated Measure in my Cube ,which I want to  use in my SSRS report. 

3)Your Statement ""You just need to replace  [Date].[Fiscal Date].[Fiscal Year].&[2008] with the member you select in SSRS"",
How could this be handled\give First and last day of  YEAR or SEMESTER or QUARTER or MONTH  which ever selected from Date Filter?

Any Help ??

Answer 3

1) Where and how should I used it in SSRS Report

You should use this query in the report dataset. Your

2)Where should I create [Measures].[FirstDay] and[Measures].[LastDay],as we Cannot create this as a Calculated Measure in my Cube ,which I want to  use in my SSRS report.

These measures will be created as part of your query in the report dataset

3)Your Statement ""You just need to replace  [Date].[Fiscal Date].[Fiscal Year].&[2008] with the member you select in SSRS"",
How could this be handled\give First and last day of  YEAR or SEMESTER or QUARTER or MONTH  which ever selected from Date Filter?

If you have bound your Fiscal Hierarchy Members dataset to your filter and have set UNIQUENAME as the value for filter - you just need to replace [Date].[Fiscal Date].[Fiscal Year].&[2008] with the Filter value.

Just google on how to create SSRS reports using MDX - you will get thousands of links.

---

Karthik