October 30, 2012 at 6:09 am
Hi,
I'm trying to work out how to create a report. The user wished to use the measure for Current Pupils.
The next Step is to add a WHERE Statement_First_Issued_Date Between [Date] and [Date]
Or it could be > [Date]
At present I do not know how do this using Report Builder or reporting services against the cube.
Obviously it would be easy using SQL
I tried using Operator Range Inclusive but found a few issues.
1.You can only use the dates if they are available. So if you want to set it from 01/01/2012 and 01/10/2012 you can’t if there are no dates available on these dates. Not good for this kind of query.
2.I set it as a test using 05/10/2007 and 05/09/2012 (Dates that were available) and it brought up
05/09/2012
05/10/2001
05/10/2004
05/10/2005
05/10/2006
Which doesn’t make sense at all. It brings back the 05/09/2012 and then nothing for 05/10/2007. And yet its brought back 05/10 for years 2001, 2004, 2005.
So clearly I don’t understand what the range inclusive means. I thought it was a between clause? Ive looked on the internet but can.t find any decent advice about it.
Basically is there any way of doing this so you can use dates for BETWEEN, Greater than Equal too etc. And if not, why not? It seems very strange that this wouldn’t be available when people do this all the time using SQL.
October 30, 2012 at 10:18 am
As an addition to this I used the MDX filter to do a between clause
[SEN].[Statement First Issued Date].[01/01/1994] :[SEN].[Statement First Issued Date].[07/11/2002]
and again this doesnt work because you can only use dates that are in the column. we can for example to a from and to date for the year if the dates aren't there.
Clearly I need to do it in date in the date time dimension as this has all the dates... I'm still testing. Id set it up as a query but my users are so used to doing queries on the dates they don't like the logic of not doing this.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply