Date Range for Report built against cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Thanks

MDX sets do not really work like this, they are made up of sets of discrete members and while they are ordered and you can get ranges of members, the idea of a between predicate does not really map well. I usually try to constrain my report parameter so that users cannot pick values outside of the valid member range.

If you can't do this you could probably use the filter function, provided that you had the actual date value stored somewhere (the MemberValue property is ideal for this). Which would make your range statement something like this:

filter( <date dim>.<hierarchy>.<date level>.Members

, <date dim>.<hierarchy>.MemberValue >= <fromDate>

and <date dim>.<hierarchy>.MemberValue <= <toDate>))

But this is going to be a lot slower than directly specifying a start and end member directly.|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

|||Yeah, that's what I do aswell. And 5 years is only 1500 members which is not big as dimensions go.

Date Range for Report built against a cube using MDX query

Hi,

I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.

My Issue:

I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.

Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.

My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.

Many Thanks

Absolutely it is possible. Have you tried removing the CONSTRAINED flag? Also do a search of this forum, i have posted several items regarding using dates and MDX within reports.

|||

Hi,

Thanks for your reply. I managed to solve this issue by creating a time dimension with all the possible date combinations for the next five years and previous year which could occur in my data. Performance was an issue that is why this solution implemented.

Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+