Implementing a date range in SSRS with the data coming from a cube

Question

I have been trying to implement a date range parameter in SSRS 2015. The data is from a cube. I dragged my date into the dimension box of the query designer and went into the parameter tab to change the fromDate and toDate to date/time format and this resulted in the following MDX query.

SELECT ( STRTOMEMBER(@FromDate, CONSTRAINED) : 
STRTOMEMBER(@ToDate, CONSTRAINED) )

My date format is dd/mm/yyyy. I have looked on several forums and tried a few method but continued to get the error:

The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function 
were violated

Thank you in advance

Answer 1

Your parameters will need to be formed in full address mdx. e.g. @FromDate could be this - I've used the DateKey:

'[Date Dim].[Date Hier].&[20180301]'

So some concatenation, within ssrs, would be required before getting passed:

"[Date Dim].[Date Hier].&[" + 20180301 + "]"  //<<you'll need to change to the names used in your cube

I'd also be tempted to use strToSet as you want a set. So mine might be something like:

( STRTOSET( "[Date Dim].[Date Hier].&[" + @FromDate + "]:" +
          "[Date Dim].[Date Hier].&[" + @ToDate + "]" , CONSTRAINED) )

Here is a good reference: Range parameter on the MDX-query