CODEWITHSUNDEEP
reporting-services
sam
sam

Reputation: 45

SSRS dataset filter

Hi I'm trying to build a report in ssrs which gives the output based on current weekending date where end day is saturday. I'm pulling my data from SSAS Cube and I have a weekending date field so I applied a filter in the query designer to get the current weekending date using a parameter I used this expression:

DateAdd("d",7-DatePart(DateInterval.Weekday,Today,FirstDayOfWeek.Sunday),Today)

When I'm executing my query, it throws an error saying the restriction imposed by the constrained flag in the STRTOSET function were violated.

Please let me know how can I fix this.

Upvotes: 1

Views: 397

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

The error is thrown because you are passing a string that doesn't correspond with a valid member in your cube.

Note the Query Designer builds a MDX query based on the dimensions and members you select, this query uses the STRTOSET() function to convert your string parameter in a valid member. 

...
STRTOSET(@CurrentWeekendDate,CONSTRAINED)
...

If you pass the current weekend date to your parameter it produces:

STRTOSET('2016-01-10',CONSTRAINED)

As 2016-01-10 is not a valid member in your Date dimension it throws the error.

You have to pass something like this depending on your cube:

[Date].[Date Key].&[2005-01-01T00:00:00]

So in SSRS you have to set your parameter to Text and use this expression:

CORRECT EXPRESSION:

="[200 Date].[Week Ending Date].&[" &
Format(
DateAdd("d",7-DatePart(DateInterval.Weekday,Today,FirstDayOfWeek.Sunday),Today),
"yyyy-MM-ddThh:mm:ss"
)
& "]"

UPDATE: If [Week Ending date] level doesn't include time:

="[Date].[Date Key].&[" &
Format(
DateAdd("d",7-DatePart(DateInterval.Weekday,Today,FirstDayOfWeek.Sunday),Today),
"yyyy-MM-dd"
)
& "]"

Go through this tutorial if you get stuck.

Let me know if this helps.

Upvotes: 1

Related Questions