Date Filtering

[drkestrel]

I am new to MDX Query, and having read up on some articles it seems like the colon operator could be used to specify a range in an ordered set like that for a date/time dimension.

What I effectively need is to be able to do filtering on a time_dimension
- between two date/time (e.g. between 10:00 10Jun02 and 23:00 21Feb03)
- After one date/time (e.g. After 13:00 14Jun03)
- Before one date/time (e.g. Before 17May01)

I have tried something like the following to filter between two dates, which failed. The time related clauses have been unintended to make them stand out. All other non-time related clauses has been tested and are working as expected.

      WITH
        MEMBER [class_dimension].['Plant Super Class' OR 'People Super Class']
            AS '([class_dimension].[Plant Super Class],
Measures.[Measurement]) + ([class_dimension].[People Super Class], Measures.[Measurement])'
        MEMBER [org_dimension].[Org1 OR Org2]
            AS '([org_dimension].[Org1], Measures.[Measurement]) + ([org_dimension].[Org2], Measures.[Measurement])'
 /*****************************************************************
  *   The following date/time calculated member clause does not seem to be working                                      *
  *****************************************************************/
MEMBER [time_dimension].[Selected]
 As 'Sum(FILTER([time_dimension].Members,
  VBA!CDate([time_dimension].CurrentMember.Name) >=VBA!CDate('01/01/1999 10:00') AND
  VBA!CDate([time_dimension].CurrentMember.Name) <=VBA!CDate('12/12/2002 22:00'))'

       SELECT
           {Measures.[Measurement]} on ROWS,
           {[area_dimension].Members} ON COLUMNS
       FROM
           dataCube
       WHERE
           (
            [class_dimension].['Plant Super Class' OR 'People Super Class'] ,[org_dimension].[Org1 OR Org2]
 /****************************
  *   Date/time filtering is not working *
  ****************************/
[time_dimension].[Selected]
           )

The error I get is Syntax error, expecting SELECT, near:
'01/01/1999'.....................

I know I could filter by something like

[time_dimension].[1999].[Q1].[1]:[time_dimension].[2002].[Q4].[3]

, but I would rather have an absolute date specified.

 

[drkestrel]

Did more experiments, with no luck.

**Full MDX Query 1:
WITH SET [timeRange] AS
'FILTER( [time_dimension].[day].MEMBERS,
VBA!CDate ([Time_dimension].CurrentMember.Name)>=VBA!CDate ("02/01/03&quot
)'
MEMBER [time_dimension].[range]
AS 'Aggregate([timeRange])'
SELECT {[area_dimension].members} on COLUMNS,
{Measures.[measurement]} on ROWS
FROM datacube
WHERE ([time_dimension].[Range])

**Problem with Full MDX Query 1:
No errors, but no "measures"/data brought back, even though there are >6000 records when viewed through SQL Server 2000's Analysis Service.

**Full MDX Query 2:
WITH SET [timeRange]
AS 'FILTER
(
[time_dimension].[day].MEMBERS,
VBA!CDate([time_dimension].CurrentMember.Name & "/"
& Ancestor([time_dimension].CurrentMember, [time_dimension].[month]).Name & "/"
& Ancestor ( [time_dimension].CurrentMember, [time_dimension].[year]).Name ))
>= CDate ("01/01/03&quot
)'
MEMBER [time_dimension].[range] AS 'Aggregate([timeRange])'
SELECT {[area_dimension].members} on COLUMNS,
{Measures.[measurement]} on ROWS
FROM dataCube WHERE
([time_dimension].[Range])

**Problem with MDX Query 2
ERROR:
Formula error - syntax error - token is not valid: "FILTER( [time_dimension].[day].MEMBERS, VBA!CDate([time_dimension].CurrentMember.Name ^&^ "/" & Ancestor([time_dimension].CurrentMember, [time_dimension].[month]).Name & "/" & Ancestor ( [time_dimension].CurrentMember, [time_dimension].[year]).Name )) >= CDate ("01/01/03&quot)"
(Note using UK Date format here)

**Full MDX Query 3
WITH SET [timeRange]
AS 'FILTER
(
[time_dimension].[day].MEMBERS,
VBA!CDate([time_dimension].CurrentMember.Name + "/"
+ Ancestor([time_dimension].CurrentMember, [time_dimension].[month]).Name + "/"
+ Ancestor ( [time_dimension].CurrentMember, [time_dimension].[year]).Name ))
>= CDate ("01/01/03&quot
)'
MEMBER [time_dimension].[range] AS 'Aggregate([timeRange])'
SELECT {[are_dimension].members} on COLUMNS,
{Measures.[measurement]} on ROWS
FROM dataCube WHERE
([time_dimension].[Range])

**Problem With MDX Query 3
Formula error - syntax error - token is not valid: "FILTER( [time_dimension].[day].MEMBERS, VBA!CDate([time_dimension].CurrentMember.Name + "/" + Ancestor([time_dimension].CurrentMember, [time_dimension].[month]).Name + "/" + Ancestor ( [time_dimension].CurrentMember, [time_dimension].[year]).Name )) ^>=^ CDate ("01/01/03&quot)"

What is the correct/efficient way of doing such date filtering (note for simplicity, I have omitted the end-date in queries 2 and 3 above), but what I really want is to specify
1) A Date range with both a start and end date (records not necessarily present on either date)
2) Before a date (Use lastperiods??)
3) After a Date (use openingperiod??)

 

[MDXer]

I'm looking at this and a few questions come to mind:

1) What is the Structure of your time Dimension mainly the
levels. and Is it set as a Time Based dimension. Does
it have Multiple Hierarchies?

2) What is the Purpose of the VBA!CDate() Function.

3) As I understand it you want to to See all of the
ARE_Members with The Measurement measure for a Date
range of say feb 21, 2003 thru feb 30, 2003.

Expected results like the following

Are-1 Are-2 Are-3 Are-4
----- ----- ----- -----
Measurement 101 203 330 450

Like I said I am trying to get a handle on what you want the query to do.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck