Change this query to use a date range

Hi Camilla,
The following is a basic example to try and give you a starting point example - it is based on the standard Microsoft AdventureWorks cube:

 SELECT NON EMPTY { [Measures].[Order Quantity] } ON COLUMNS
 , NON EMPTY { ([Product].[Product Line].[Product Line].ALLMEMBERS ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM ( SELECT ( [Order Date].[Date Key].&[20070101] : [Order Date].[Date Key].&[20070131] ) ON COLUMNS 
 FROM [Adventure Works DW2012]) 

Select allOpen in new window

So the above gives Order Quantity in column header and Product Line on each row with the Order Quantity as the metric value, where the Order Date was between 1st July 2007 and 31st July 2007.

So it uses the 'colon' syntax for the date range

I would first try and do a very simple version of your MDX query to prove that the syntax for your date range is correct.
So maybe even adapt the query I have posted above for your context.

Also you can connect to an Analysis Services cube using SQL Server Management Studio, you can then browse the cube adding attributes, measures and filters via the GUI and then view the auto-generated MDX to get some ideas and confirmation

I tried a lot of examples and still couldn't get the "colon" working. Let me look at your example and see.
No one else at work knows MDX queries.

Also you can connect to an Analysis Services cube using SQL Server Management Studio, you can then browse the cube adding attributes, measures and filters via the GUI and then view the auto-generated MDX to get some ideas and confirmation

You have a screenshot of how to do this?

"You have a screenshot of how to do this?"

I'll explain this without screenshot :)

Have a look at the Object Explorer window in SSMS (Management Studio).  At its top there's a toolbar with a dropdown called Connect.  Select Analysis Services from that list to open the Connect to Server window.  This one now allows you to connect to your SSAS instance.

Yeah, I have that. I thought there's a GUI section to drag/drop and build MDX queries.

Hi Camilla,
Please see a screenshot attached.
After connecting to SSAS with SSMS, expand nodes in Object Explorer down to the Cubes node, right-click on 'cube' and choose Browse - then in right hand pane you can add various items including filters.
The very right-hand toolbar button in the 'Browse' pane allows you to toggle between MDX and Design(GUI) view.
MS-SSAS-Browse-Cube.png

Thanks! I just told my boss that I'm having issues with this MDX. Not good but it is what it is.

Barry, I checked and i don't have "Design mode" in SSMS. I know the syntax of date range and looked at other examples but still don't know where that "date range" clause should go in the above MDX.

Hi Camilla,
Please confirm if you are able to connect o Analysis Services with SSMS.

I can, Here's a screenshot.

I've been playing around with the "select" and "set" and "member" sections but still not any closer.
MDX.png

Hi Camilla,
When you first open up SSMS and connect to Analysis Services and right-click on 'Survey Response' cube for example, and choose Browse, what is first displayed in right hand pane before you do anything else - please post up a screenshot if possible.

I'll do it when I get to work. Thanks for sticking with this.

Ah, now I see the "Design mode". But still, I dont know how to change that MDX query and have a date range for those 2 fields I have noted above. See attached
MDX.png

Hi Camilla,
From the middle pane(under Measure Group) you can expand the Dimension nodes(for example Report Date) and then you can drag one of the date fields up into the top right pane, where you see Hierarchy, Operator and Filter Expression.
You can then change the Operator to 'Range(Inclusive)' and enter the required Start Date and End Date in the Filter Expression column.

let me see

It's a great feature...that design tool!

Look at the 2 screenshots. I have the data range. I know what the errors are but don't know how to fix them. (If we can't figure it out, I'll just turn it over back to my boss)

1. I added the time range. Screenshot "error_measure" shows the error in the column. Error is
CellOrdinal      4
VALUE      #Error The  function expects a string or numeric expression for the  argument. A tuple set expression was used.
FORMATTED_VALUE      #Error The  function expects a string or numeric expression for the  argument. A tuple set expression was used.

I think this is because "MEMBER [Measures].[LastPeriodFilteredReceived]" is looking for one expression but what I have is a set.

2. Second try : error_test screenshot. I added a variable and called it "recTest". I got the attached error. I think this means I need to add "recTest" in the "select" statement (my orig SQL above)

Should I give up on this?
error-measure.png
error-test.png

Hi Camilla,
I would start with a very simple example and create this via the GUI(Design Tool)
Put 'Dealer Name' on ROWS
Put 'Survey Metric Count Received' on COLUMNS
Add in a filter for some date column(Maybe 'Report Date') for a narrow date range so as the query runs quickly.

Run this simple example and make sure there are no errors and that valid data is returned.

This will confirm that it is possible to run a basic query with a date range and that there are no fundamental problems.

Let me try it. Thanks for sticking with this.

See below. I added the time range to where clause.
I also  have this  

[Measures].[Survey Metric Count Received]

Select allOpen in new window

instead of

( [Measures].[Survey Metric Count Received], StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) )

Select allOpen in new window

Maybe I need to put the date range in the "where" clause and change the code to this

MEMBER [Measures].[LastPeriodFilteredReceived]
AS
 [Measures].[Survey Metric Count Received] 

Select allOpen in new window

This works.

SELECT 
	{ 
	  [Measures].[Survey Metric Count Received]
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children
		)
	} ON ROWS
FROM [Survey Response]
WHERE ( 
          {  StrToMember( '[Survey Type].[Id].&[16]' )  }, 
		  {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  },
          {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } ,
		  {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201006]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
		  
      )

Select allOpen in new window

My test above worked. So, I tried this but get an error

Executing the query ...
The Calendar hierarchy already appears in the Axis0 axis.
Execution complete

WITH
MEMBER [Measures].[ReportMeasure]
AS 
	StrToMember( '[Measures].[MeanR12]' ), FORMAT_STRING = '##0.00'

MEMBER [Measures].[LastPeriodFilteredReceived]
--set recTest
AS
  [Measures].[Survey Metric Count Received]
 //( [Measures].[Survey Metric Count Received], StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) )


MEMBER [Measures].[LastPeriodFilteredSent]
AS 
      [Measures].[Survey Metric Count Sent]
	//( [Measures].[Survey Metric Count Sent], StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) )


SET ReceivedInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredReceived]


SET SentInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredSent]


SET MeasuresOverPeriod1
AS
	(ParallelPeriod([Report Date].[Calendar].Levels( 'YYYYMM' ), '3', //it was 12
	  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )) *
	  [Measures].[ReportMeasure]


SET CurrentPeriod
AS
	case when 'YYYYMM' = 'YYYYQ'
	then
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']').Parent }
	else
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']') }
	end
SET CurrentPeriodReportMeasure
AS
	CurrentPeriod * [Measures].[ReportMeasure]
SET CurrentPeriodSent
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Sent]
SET CurrentPeriodReceived
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Received]


SELECT 
	{ 
	        MeasuresOverPeriod1,
		ReceivedInLastPeriod,
		SentInLastPeriod,
		CurrentPeriodReportMeasure,
		CurrentPeriodReceived,
		CurrentPeriodSent
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children,
			[Dealer].[Number].Children,
			[Dealer].[Territory - Code].Children,
			[Dealer].[Territory - Name].Children
		)
	} ON ROWS
FROM [Survey Response]
WHERE ( 
          {  StrToMember( '[Survey Type].[Id].&[16]' )  }, 
		  {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  },
          {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } ,
		  {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201006]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
		  
      )

Select allOpen in new window

Hi Camilla,
OK so this is good - you now have a sample MDX query which uses some of the attributes/measures that you need and it also has a date range

SELECT 
	{ 
	  [Measures].[Survey Metric Count Received]
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children
		)
	} ON ROWS
FROM [Survey Response]
WHERE ( 
          {  StrToMember( '[Survey Type].[Id].&[16]' )  }, 
		  {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  },
          {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } ,
		  {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201006]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
		  
      )

Select allOpen in new window

So I am guessing that the above code gives you a nice simple report with 'Dealers' listed down the rows in the left and for each dealer it gives the 'Survey Metric Count Received' value and you know that it is for the date range specified.

So now start to gradually add to this to get what is ultimately required.
Do you know full details of the final report or the business questions that this report are trying to give details/answers for?

Yes, I have the entire MDX. I just need to change 2 columns. See my post above (we posted at the same time)
ID: 40373410

I also just tried putting the sections back to the sample I have...one by one..but still get that calender error I posted above.
It's like it can't be in the "where" clause and in other sections at the same time.

That calendar error...the dot notation can't be used in slicer and columns. That's what I found on google.

This is my latest. I don't know where to add the date-range criteria. I'm getting results back.

I've tried to add it to 2 places. I have them lines commented.

see attached screenshot for how the result looks

WITH
MEMBER [Measures].[ReportMeasure]
AS 
	StrToMember( '[Measures].[MeanR12]' ), FORMAT_STRING = '##0.00'

	MEMBER [Measures].[LastPeriodFilteredReceived]

AS
  [Measures].[Survey Metric Count Received]

  MEMBER [Measures].[LastPeriodFilteredSent]
AS 
      [Measures].[Survey Metric Count Sent]

SET ReceivedInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredReceived]


SET SentInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredSent]

SET MeasuresOverPeriod1
AS
	(ParallelPeriod([Report Date].[Calendar].Levels( 'YYYYMM' ), '3', //it was 12
	  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )) *
	  [Measures].[ReportMeasure]


SET CurrentPeriod
AS
	case when 'YYYYMM' = 'YYYYQ'
	then
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']').Parent }
	else
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']') }
	end
SET CurrentPeriodReportMeasure
AS
	CurrentPeriod * [Measures].[ReportMeasure]
SET CurrentPeriodSent
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Sent]
SET CurrentPeriodReceived
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Received]
 
SELECT 
	{ 
	 
	   MeasuresOverPeriod1,
	   ReceivedInLastPeriod,
	   SentInLastPeriod,
		CurrentPeriodReportMeasure,
		CurrentPeriodReceived,
		CurrentPeriodSent //,
		
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children,
			[Dealer].[Number].Children,
			[Dealer].[Territory - Code].Children,
			[Dealer].[Territory - Name].Children
		)
	} ON ROWS
FROM 
// (
//    
//    SELECT {
//              {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201306]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
//            } ON 0
//
//  from 
  [Survey Response]

 // )
WHERE ( 
          {  StrToMember( '[Survey Type].[Id].&[16]' )  }, 
		  {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  },
          {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } //,
		 // {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201006]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
		  
      )

Select allOpen in new window

mdx-latest.png

Hi Camilla,
I think that because of the attributes you have in the main SELECT ... ON COLUMNS the 'Calendar' hierarchy is already referenced, and cannot be referenced again in a WHERE clause.
So the next option is to do the required date range filtering in a SUB SELECT(which I think you may have tried in one version of your query already) but let us re-visit this again:  so the lines which you had commented out after the FROM clause, please un-comment those again and confirm how it behaves.

If I comment out the "sub select", I don't get any results back. No errors, no graph result. Just an empty lower pane. In the "messages", it has a text saying the query ran and execution is completed. I cant post the exact msg when I get to work.

Hi Camilla,
Please confirm what occurs if you un-comment the "sub select"

I will when I get to work. Thanks for sticking with this ticket.

You know what, I do get results back with the sub-select. Not sure why I thought it's not bringing back any results (we're all suffering because we don't know MDX here on our team :))

 However, whatever date I put in, I still get the same value for "lastperiodfitleredrecieved" and "lastperiodfilteredsent". These are the 2 columns that my manager said needs to "roll up" to use a data range. Look at row 5 on the screenshot. It's 7 and 13 for those columns no matter what date range I use.

Please see attached. What am I missing from it? Do I somehow need to add this sub-select (date range) to this section?

MEMBER [Measures].[LastPeriodFilteredReceived]
AS
  [Measures].[Survey Metric Count Received]

Select allOpen in new window

This is what I have now

WITH
MEMBER [Measures].[ReportMeasure]
AS 
	StrToMember( '[Measures].[MeanR12]' ), FORMAT_STRING = '##0.00'

MEMBER [Measures].[LastPeriodFilteredReceived]
AS
  [Measures].[Survey Metric Count Received]



MEMBER [Measures].[LastPeriodFilteredSent]
AS 
      [Measures].[Survey Metric Count Sent]

SET ReceivedInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredReceived]


SET SentInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredSent]

SET MeasuresOverPeriod1
AS
	(ParallelPeriod([Report Date].[Calendar].Levels( 'YYYYMM' ), '3', //it was 12
	  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' )) *
	  [Measures].[ReportMeasure]


SET CurrentPeriod
AS
	case when 'YYYYMM' = 'YYYYQ'
	then
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']').Parent }
	else
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']') }
	end
SET CurrentPeriodReportMeasure
AS
	CurrentPeriod * [Measures].[ReportMeasure]
SET CurrentPeriodSent
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Sent]
SET CurrentPeriodReceived
AS 
	CurrentPeriod * [Measures].[Survey Metric Count Received]
 
SELECT 
	{ 
	 
	   MeasuresOverPeriod1,
	   ReceivedInLastPeriod,
	   SentInLastPeriod,
		CurrentPeriodReportMeasure,
		CurrentPeriodReceived,
		CurrentPeriodSent //,
		
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children,
			[Dealer].[Number].Children,
			[Dealer].[Territory - Code].Children,
			[Dealer].[Territory - Name].Children
		)
	} ON ROWS
FROM 
 (
    
    SELECT {
              {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201306]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
            } ON 0

  from 
  [Survey Response]

  )
WHERE ( 
          {  StrToMember( '[Survey Type].[Id].&[16]' )  }, 
		  {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  },
          {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } //,
		 // {  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201006]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
		  
      )

Select allOpen in new window

This is what I have now
mdx1013.png

Tried this but this is not either

set testRec
 as
  { StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201306]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[Survey Metric Count Received]

Select allOpen in new window

Is there anyway to have the "where" clause and change the other sections that have "calendar"??

Barry (or other experts), I'll leave this open for now hoping someone could help me. thanks

Hi Camilla,
The following is a simplified snippet of your query - it only contains the Survey Count Received measure and the Dealer but it contains ParallelPeriod function to try and to the rolling 12 months accumulation:
Please confirm if this brings back any results or gives any error

WITH

-- Survey Metric Count Received - July 2014
MEMBER [Measures].[LastPeriodFilteredReceived]
AS
	( 
		[Measures].[Survey Metric Count Received]
		, StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) 
	)


-- Received in Last Period
-- change this one to use date range - go back 12 months from July 2014 - and apply this to the LastPeriodFilteredReceived measure

SET ReceivedInLastPeriod 
AS
	{ StrToMember( 'PARALLELPERIOD([Report Date].[Calendar].[YYYYMM], 12, [Report Date].[Calendar].[YYYYMM].&[201407])' ) } * [Measures].[LastPeriodFilteredReceived]



-- Get the results
SELECT 
	{ 
		
		ReceivedInLastPeriod,
		
		
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].Children,
			
		)
	} ON ROWS
FROM 
(
	SELECT {
              		{  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201306]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
            	} ON 0

  	FROM 
  	[Survey Response]

)
WHERE 
( 

	  {  StrToMember( '[Survey Type].[Id].&[16]' )  }
	, {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  }
 	, {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } 

)

Select allOpen in new window

Hi Camilla,
I had some small syntax errors in the code in my last post - please try this code:

WITH

-- Survey Metric Count Received - July 2014
MEMBER [Measures].[LastPeriodFilteredReceived]
AS
	( 
		[Measures].[Survey Metric Count Received]
		, StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) 
	)


-- Received in Last Period
-- change this one to use date range - go back 12 months from July 2014 - and apply this to the LastPeriodFilteredReceived measure

SET ReceivedInLastPeriod 
AS
	{ StrToMember( 'PARALLELPERIOD([Report Date].[Calendar].[YYYYMM], 12, [Report Date].[Calendar].[YYYYMM].&[201407])' ) } * [Measures].[LastPeriodFilteredReceived]



-- Get the results
SELECT 
	{ 
		
		ReceivedInLastPeriod
		
		
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		[Dealer].[Name].Children
		
	} ON ROWS
FROM 
(
	SELECT {
              		{  StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201306]' ): StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) }
            	} ON 0

  	FROM 
  	[Survey Response]

)
WHERE 
( 

	  {  StrToMember( '[Survey Type].[Id].&[16]' )  }
	, {  StrToMember( '[Dealer].[Manufacturer - Id].&[15]' )  }
 	, {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } 

)

Select allOpen in new window

Thanks Barry. I was overseas and couldn't check this. I'll try it. Thanks again.

Yours runs, Barry (sorry for the late reply, I was working on other stuff). This MDX guy here (he's new to MDX) rewrote that to this (with different parameters) which is now giving an error. I'll apply the parameters to your query and see if that works

WITH
MEMBER [Measures].[ReportMeasure]
AS 
	StrToMember( '[Measures].[MeanR4]' ), FORMAT_STRING = '##0.00'
MEMBER [Measures].[LastPeriodFilteredReceived]
AS
    case when 'YYYYQ' = 'YYYYQ'
    then
	    ( [Measures].[Survey Metric Count Received R4], StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) )
    else
        ( [Measures].[Survey Metric Count Received R12], StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) )
    
    end
MEMBER [Measures].[LastPeriodFilteredSent]
AS 
	case when 'YYYYQ' = 'YYYYQ'
    then 
	    ( [Measures].[Survey Metric Count Sent R4], StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) )
    else 
        ( [Measures].[Survey Metric Count Sent R12], StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) )
    
    end
SET ReceivedInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) } * [Measures].[LastPeriodFilteredReceived]
SET SentInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' ) } * [Measures].[LastPeriodFilteredSent]
SET MeasuresOverPeriod
AS
	(ParallelPeriod([Report Date].[Calendar].Levels( 'YYYYQ' ),'4', StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' )): 
	                                                               StrToMember( '[Report Date].[Calendar].[YYYYQ].&[20143]' )) *
	[Measures].[ReportMeasure]
SET CurrentPeriod
AS
	case when 'YYYYQ' = 'YYYYQ'
	then
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']').Parent }
	else
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[' + cstr(Format(now(), 'yyyyMM'))  + ']') }
	end
SET CurrentPeriodReportMeasure
AS
	CurrentPeriod * [Measures].[ReportMeasure]
SET CurrentPeriodSent
AS 
    case when 'YYYYQ' = 'YYYYQ'
    then
	    CurrentPeriod * [Measures].[Survey Metric Count Sent R4]
    else
        CurrentPeriod * [Measures].[Survey Metric Count Sent R12]
    
    end
SET CurrentPeriodReceived
AS 
	case when 'YYYYQ' = 'YYYYQ'
    then
	    CurrentPeriod * [Measures].[Survey Metric Count Received R4]
    else
        CurrentPeriod * [Measures].[Survey Metric Count Received R12]
    
    end
SELECT 
	{ 
		MeasuresOverPeriod,
		ReceivedInLastPeriod,
		SentInLastPeriod,
		CurrentPeriodReportMeasure,
		CurrentPeriodReceived,
		CurrentPeriodSent
	} ON COLUMNS, 
	NON EMPTY 
	{ 
		(
			[Dealer].[Name].[All].Children,
			[Dealer].[Number].[All].Children,
			[Dealer].[Territory - Code].[All].Children,
			[Dealer].[Territory - Name].[All].Children
		)
	} ON ROWS
FROM [Survey Response]
WHERE ( {  StrToMember( '[Survey Type].[Survey Type Category - Code].&[R]' )  }, {  StrToMember( '[Dealer].[Manufacturer - Id].&[4]' )  },
 {  StrToMember( '[Geographical Hierarchy].[Id].[Id].&[41]' )  }, {  StrToMember( '[Organizational Hierarchy].[Id].[Id].&[40]' )  }, {  StrToMember( '[Survey Field Value].[Field].&[Overall Dealer]' )  } )

Select allOpen in new window