Filter dataset for previous quarter

Question

Filter dataset for previous quarter

tonzonline

Mr or Mrs. 500

Points: 597
More actions
March 30, 2016 at 3:22 am

#325266

Dear all,
I'm trying to filter my SSRS report for previous quarter data.
Here is my step:
1. I have "date invoice" in my query
2. in data set -> filter, I add "date invoice", with operator ">=", and in value -> expression, I put : =DATEADD( "q", -1, DATEADD("q", DATEDIFF("q", 0, TODAY()),0))
3. add another filter, "date invoice", with operator "<=", value -> expression is : =DATEADD( "d", -1, DATEADD("d", DATEDIFF("d", 0, TODAY()),0))
When I run the report, it give me error :
Failed to evaluate the FilterValue of the DataSet ‘DataSet1’. (rsFilterEvaluationError)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)
Can anyone advice me where is the problem?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tonzonline Mr or Mrs. 500 Points: 597 More actions · Answer 1

Hi all,

Just to add, the SSRS is for Analysis Server (SSAS Cube) and the "date invoice" information is Text datatype (I think so). Could it be the problem ?

But I tried to use CSTR(DATEADD( "q", -1, DATEADD("q", DATEDIFF("q", 0, TODAY()),0))) but to no avail.

Kindly help.

Thanks.

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Sounds like you have the wrong column in your cube. You have to do the date math on a date column, not a text column. If you fix that, the date functions will work fine. If you don't you're asking the AS to do a LOT of unnecessary string manipulation when you really don't need to.

tonzonline Mr or Mrs. 500 Points: 597 More actions · Answer 3

Hi,

I confirmed my column 'date invoice' is a Date. I checked to my V.Studio SSAS project file, and in the DSV of my Fact Table, DataType is System.DateTime

Now at my SSRS report builder design :

1. At the Data set -> Filter, I added CDate(Date invoice.value) just to make sure.

2. Changed also the selection at the right of "Expression" to be Date/Time (before, it is Text)

3. Value is still =DATEADD( "d", -1, DATEADD("q", DATEDIFF("q", 0, TODAY()),0))

When I run, it still says error like below :

Failed to evaluate the FilterValue of the DataSet ‘DataSet1’. (rsFilterEvaluationError)

----------------------------

An error has occurred during report processing. (rsProcessingAborted)

Thanks.

pietlinden SSC Guru Points: 63503 More actions · Answer 4

=DATEADD( "d", -1, DATEADD("q", DATEDIFF("q", 0, TODAY()),0))

TODAY() is a .NET expression. The equivalent T-SQL is GETDATE()

Spiff SSCertifiable Points: 7826 More actions · Answer 5

Since this is using Analysis Services as the source this would be better off done in the dataset unless you had a reason to be doing it in the report?

An MDX filter expression could be used against he date hierarchy could be used. E.g.

Ancestor(StrToMember("[Date].[Calendar].[Date].&[" + Format(Now(),"yyyyMMdd") + "]", CONSTRAINED), [Date].[Calendar].[Calendar Quarter]).Lag(1)