SolvedPrivate

Change this query to use a date range

Posted on 2014-10-08
39
29 Views
Last Modified: 2016-02-18
(I don't know how to request attention on new questions so I deleted and reposting this question)
---

Anyone knows MDX queries? I've been googling this but can find an answer.

This MDX query, uses the exact date of June 2014 for 2 columns. I need to use a range. For example, if user selects June 2014...the data should be rolled from July 2013 to June 2014

I tried using colon like this as a test for 2 months but the column shows #Error

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

Open in new window


Any ideas on how to change this?

I tried the examples here as well :
 http://www.purplefrogsystems.com/blog/2013/04/mdx-between-start-date-and-end-date/


WITH
MEMBER [Measures].[ReportMeasure]
AS 
	StrToMember( '[Measures].[MeanR12]' ), FORMAT_STRING = '##0.00'
MEMBER [Measures].[LastPeriodFilteredReceived]
AS
	( [Measures].[Survey Metric Count Received], StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) )
MEMBER [Measures].[LastPeriodFilteredSent] //****change this one to use date range
AS 
	( [Measures].[Survey Metric Count Sent], StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) )
SET ReceivedInLastPeriod ///***change this one to use date range
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredReceived]
SET SentInLastPeriod
AS
	{ StrToMember( '[Report Date].[Calendar].[YYYYMM].&[201407]' ) } * [Measures].[LastPeriodFilteredSent]
SET MeasuresOverPeriod
AS
	(ParallelPeriod([Report Date].[Calendar].Levels( 'YYYYMM' ), '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 
	{ 
		MeasuresOverPeriod,
		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]' )  } )

Open in new window

0
Comment
Question by:Camillia
  • 25
  • 12
39 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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]) 

Open 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
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
"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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Yeah, I have that. I thought there's a GUI section to drag/drop and build MDX queries.
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks! I just told my boss that I'm having issues with this MDX. Not good but it is what it is.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
Hi Camilla,
Please confirm if you are able to connect o Analysis Services with SSMS.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
I'll do it when I get to work. Thanks for sticking with this.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
let me see
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Let me try it. Thanks for sticking with this.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
See below. I added the time range to where clause.
I also  have this  
[Measures].[Survey Metric Count Received]

Open in new window


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

Open 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] 

Open 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]' ) }
		  
      )

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
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]' ) }
		  
      )

Open in new window

0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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]' ) }
		  
      )

Open 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?
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
That calendar error...the dot notation can't be used in slicer and columns. That's what I found on google.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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]' ) }
		  
      )

Open in new window

mdx-latest.png
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
Hi Camilla,
Please confirm what occurs if you un-comment the "sub select"
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
I will when I get to work. Thanks for sticking with this ticket.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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]

Open 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]' ) }
		  
      )

Open in new window

This is what I have now
mdx1013.png
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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]

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Is there anyway to have the "where" clause and change the other sections that have "calendar"??
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Barry (or other experts), I'll leave this open for now hoping someone could help me. thanks
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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]' )  } 

)

Open in new window

0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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]' )  } 

)

Open in new window

0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
Comment Utility
Hi Camilla
I did some other revisions to the code - so please try this
WITH

-- Survey Metric Count Received - July 2014
MEMBER [Measures].[LastPeriodFilteredReceived]
AS
	( 
		[Measures].[Survey Metric Count Received]
	)


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

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



-- Get the results
SELECT 
	{ 
		
		ReceivedInLastPeriod
		
		
	} 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]' )  } 

)

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
Thanks Barry. I was overseas and couldn't check this. I'll try it. Thanks again.
0
 
LVL 7

Author Comment

by:Camillia
Comment Utility
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]' )  } )

Open in new window

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now