Date/Time Stamp: mdx

Showing posts with label mdx. Show all posts

Thursday, March 29, 2012

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

[$Dim Station].[Dim Tests] [$Dim Station].[Dim Tests] [$Dim Test Cycle].[Test Cycle] [$Dim OverallResult].[Overall Result] [$Dim Test Date].[Test Date]
2006-03-10T16:14:13 AA998280 ASM Fail 03-10-2006
2006-03-11T10:37:04 AA998280 OBD Only Fail 03-11-2006
2006-03-11T12:41:38 AA998280 OBD Only Fail 03-11-2006
2006-03-14T11:31:33 AA998280 ASM Fail 03-14-2006
2006-03-14T12:08:11 AA998280 ASM Fail 03-14-2006
2006-02-01T10:10:51 AA998280 TSI Fail 02-01-2006
2006-02-01T11:40:20 AA998280 OBD Only Fail 02-01-2006
2006-02-01T14:57:37 AA998280 TSI Fail 02-01-2006
2006-02-02T14:07:10 AA998280 ASM Fail 02-02-2006
2006-02-03T11:07:16 AA998280 ASM Fail 02-03-2006
2006-02-03T15:16:29 AA998280 ASM Fail 02-03-2006
2006-02-04T14:49:22 AA998280 OBD Only Fail 02-04-2006
2006-02-06T15:53:24 AA998280 ASM Fail 02-06-2006
2006-02-07T14:10:47 AA998280 OBD Only Fail 02-07-2006
2006-02-07T16:09:47 AA998280 OBD Only Fail 02-07-2006
2006-02-09T15:11:58 AA998280 ASM Fail 02-09-2006
2006-02-09T17:10:22 AA998280 OBD Only Fail 02-09-2006
2006-02-10T16:47:52 AA998280 OBD Only Fail 02-10-2006
2006-02-10T17:00:50 AA998280 TSI Fail 02-10-2006
2006-02-15T11:12:51 AA998280 OBD Only Fail 02-15-2006
2006-02-17T12:07:59 AA998280 ASM Fail 02-17-2006
2006-02-21T14:35:25 AA998280 OBD Only Fail 02-21-2006
2006-02-23T14:48:36 AA998280 OBD Only Fail 02-23-2006
2006-02-24T11:53:01 AA998280 ASM Fail 02-24-2006
2006-02-25T14:38:41 AA998280 TSI Fail 02-25-2006
2006-02-27T16:55:43 AA998280 OBD Only Fail 02-27-2006
2006-02-28T10:30:33 AA998280 OBD Only Fail 02-28-2006
2006-02-28T13:50:07 AA998280 ASM Fail 02-28-2006
2006-02-28T14:43:15 AA998280 ASM Fail 02-28-2006
2006-03-04T14:17:36 AA998280 OBD Only Fail 03-04-2006
2006-03-07T14:22:09 AA998280 OBD Only Fail 03-07-2006
2006-03-09T13:05:30 AA998280 ASM Fail 03-09-2006
2006-03-10T10:33:33 AA998280 OBD Only Fail 03-10-2006

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.

date range in DRILLTHROUGH Statement

Hi,

I am trying to implement date range with following MDX query in my report but giving error. Can you please tell me how to filter date range in DRILLTHROUGH Statement?

*********************************************************************************************************************************

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ON columns

FROM [OLAP Test Cube]

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

,[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]

)

RETURN

KEY([$Dim Station].[Dim Tests],0)

,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle],

[$Dim OverallResult].[Overall Result]

*********************************************************************************************************************************

Error:

Executing the query ...

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Execution complete

*********************************************************************************************************************************

FYI If I remove data range part (RED color area) then it is working fine.

Regards,

Dinesh Patel

Try to use a subselect for the datarange

like..

SELECT ([Measures].[Total Test Count]) ON columns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]} on columns from [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail])

HANNES

|||

When I execute following statement then It drill down 03-10-2006, 03-11-2006 data also.

DRILLTHROUGH

SELECT ([Measures].[Total Test Count]) ONcolumns

FROM (select {[Test Date].[Test Date].&[02-01-2006]:[Test Date].[Test Date].&[02-02-2006]}

oncolumnsfrom [OLAP Test Cube])

WHERE

([Station Name].&[1ST CHOICE EMISSIONS & INSPECTIONS], [Overall Result].&[Fail]

)

RETURN

KEY([$Dim Station].[Dim Tests],0) ,KEY([$Dim Station].[Dim Tests],1),

[$Dim Test Cycle].[Test Cycle], [$Dim OverallResult].[Overall Result],

[$Dim Test Date].[Test Date]

seee out put below:

|||

HANNES

any solution?

|||The first suggestion given worked for me - I didn't see extra dates. It might be something to do with your linkings in the cube.sql

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:

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,

Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.

Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.

Date Range filter

Hi guys,

I need to filter result in MDX query by passed Start Date and EndDate

I come up with following solution

WHERE (

FILTER([Dimension1].[ Dimension1].ALLMEMBERS,

CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) >= CDate('2/1/2007')--'2/6/2007'

AND CDate([Dimension1].[ Dimension1].Properties( "Insert Date" )) <= CDate('2/10/2007')--'2/7/2007'

)

But when I execute script it returns me null for all values in fact table.

I am sure there is data in specified interval

Any suggestions are very appreciated.

What is best approach to filter by date range in MDX query?

What service pack are you using? There's been some fixes in SP2 that may apply to this case.|||

Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells.

|||

This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns

Does anyone has idea how to make it works?

|||

Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed:

Validate that your filter "set" is in fact correct. I'd look at the the specifics behind the selection, date formatting, etc to ensure it's still being properly parse.sql

Date range + parallel time

Hi,

I'm quite new to MDX and found a problem I can not solve. I have a fact table which contains a start date and an end date, plus several measures. I also have a time dimension, with years and months. I need to dynamically build an mdx query that, given a year and a month, would show any measure in the fact table whose start date is lower than the given date and the end date is higher than the given date. To complicate things a bit, the same query must show the same measure in the previous year to the given date. Both results must be in the same axis.

We know how to show each result separately, using date ranges in the WHERE clause, but have no idea about how to combine both results.

Thanks

--eduardj

Finally solved it.

I created a date range dimension, with all the date ranges in the original fact table, and a measure-less fact table with a relationship with the new date range dimension and the old time dimension. After that, I created a Many-to-Many relationship between the original fact table and the time dimension through the newly created fact table. Besides, I created a hierarchy in the time dimension which related the times (parallel) that always had to be shown together.

Kind of messy, but it works beautifully.

thanks

--eduardj

Sunday, March 25, 2012

Date picker parameter with MDX...

I am sure others have ran into this issue.

But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.

For those of you that are interested you merely need to format the parameter values coming into your dataset your passing the report params to.

Ex.

Code Snippet

="[COE Date].[Date].&["&Format(Parameters!FromCOEDateDate.Value,"yyyy-MM-dd")+"T00:00:00]"

The above example shows the FromCOEDateDate parameter used in the report

Wednesday, March 21, 2012

date literals in MDX

I'm wondering if there's a way to put a date literal into an MDX statement. The only way I've been able to figure out to accomplish this is to use the Double datatype equivalent of the date datatype. Any other way?

The reasoning behind this question is that I have a dimension attribute which is a datetime value and has over 250,000 members. I need to select a range of dates, but I can't use the : (colon/range) operator because there's no guarantee that a particular member with the value of midnight will exist. So I have to do a Filter statement on MemberValue. Using a "date literal" improves performance 10x, but the code isn't very readable because that literal is a number, not a date. Compare performance of the following (which is the best I could do to demonstrate against Adventure Works):

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > VBA!DateSerial(2004,7,18)

)

on 1

from [adventure works]

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > 38186

)

on 1

from [adventure works]

The second query performs 10x faster than the first and returns equivalent results. It's just hard to read.

There is no Date literal representation in MDX. It's a nice trick you have with double, but it actually does type conversion (which is still very fast as you discovered). For a more readable, yet performant way - you can use CDate function as following:

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > CDate("7/18/2004")

)

on 1

from [adventure works]

The reason it will work much faster than your first query with DateSerial, is because CDate is one of few VBA functions on the list of.

Now your scenario got me interested - having 250,000 dates is enough to cover about 685 years. But even if this was at the hour granularity, it is still 29 years. And since you say that some of the dates could be missing, it is likely to be at least twice as big (otherwise what is the reason to have missing dates). So what is this company you are working for if it has data from before America was discovered (if day is the granularity) or before computers were invented (if hour is the granularity) ?

|||

Mosha. Thanks. You're absolutely right. It's about 25% faster than the number literals I was using, so that's the best trick.

To fill in your statement about CDate, it's one of the few VBA functions that have internal implementations as specified in Irina's list here:

http://www.e-tservice.com/downloads.html

As for why there are so many, the attribute is actually a datetime attribute, not just a date attribute. (It's got minutes and seconds.)

Thanks again.

Saturday, February 25, 2012

Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren

Friday, February 17, 2012

Date comparison in MDX

i have a question concerning MDX:

i have the following mdx statement:

with set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')

select

{(([Policy].[Next Status].& ), [Measures].[PolicyCount]),

(([Policy].[Next Status].&[R] ), [Measures].[PolicyCount])} on columns,

[Last18month] on rows
from [PolicySales];

and i want to add a filter according [Policy].[Next Status Date] is within the selected month,

for example if the selected month is [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00],i want to filter according to [Policy].[Next Status Date] is between [2005-12-01T00:00:00] and

[2005-12-31T00:00:00]

Can you please help me

Regards

Assuming that the .MemberValue for both [Next Status Date] and Month attributes are defined as DateTime values (eg: [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].MemberValue is 2005-12-01T00:00:00), a "filtered" [PolicyCount] could be created, like:

with

set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')

member [Measures].[NextStatusCount] as

Aggregate(Filter([Policy].[Next Status Date].[Next Status Date],

[Policy].[Next Status Date].MemberValue >= [Dim Time].[hYearMonthQuarterRS].MemberValue and

[Policy].[Next Status Date].MemberValue < [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue),

[Measures].[PolicyCount])

select

{(([Policy].[Next Status].& ), [Measures].[NextStatusCount]),

(([Policy].[Next Status].&[R] ), [Measures].[NextStatusCount])} on columns,

[Last18month] on rows
from [PolicySales]

|||

Dear sir,

thank you very much for your reply,your answer works fine

it's very helpfull,but my real problem is: the selected month used in the filter is a parameter in Reporting services,and the parameter could be:

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005]

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005]

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]

so how i can replace the value of the parameter selected in ([Dim Time].[hYearMonthQuarterRS].MemberValue) and ( [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue)

the parameter name is: DimTimehYearMonthQuarterRS

as i told before the parameter can take any value from the hierarchy [Dim Time].[hYearMonthQuarterRS]

this is my mdx used in the reporting services that i want to add the filter to it:

="WITH member nextstatus_E as '(([Policy].[Next Status].& Email ), [Measures].[PolicyCount])' member nextstatus_R as '(([Policy].[Next Status].&[R] ), [Measures].[PolicyCount])' set [Last18month] as strtoset('{"& parameters!DimTimehYearMonthQuarterRS.Value &".lag(17)" &":"& parameters!DimTimehYearMonthQuarterRS.Value &"}') select {[Measures].[nextstatus_E],[Measures].[nextstatus_R]} on columns,{ ([Last18month] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { " & parameters!p_selected_month.Value & " } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentAgencyName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentTypeName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentAgentName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimProductProductTypeName, CONSTRAINED) ) ON COLUMNS FROM [PolicySales]))))))) WHERE ( IIF( STRTOSET(@.DimProductProductTypeName, CONSTRAINED).Count = 1, STRTOSET(@.DimProductProductTypeName, CONSTRAINED), [DimProduct].[Product Type Name].currentmember ),IIF( STRTOSET(@.AgentAgentName, CONSTRAINED).Count = 1,STRTOSET(@.AgentAgentName, CONSTRAINED), [Agent].[Agent Name].currentmember ), IIF( STRTOSET(@.AgentTypeName, CONSTRAINED).Count = 1, STRTOSET(@.AgentTypeName, CONSTRAINED), [Agent].[Type Name].currentmember ), IIF( STRTOSET(@.AgentAgencyName, CONSTRAINED).Count = 1, STRTOSET(@.AgentAgencyName, CONSTRAINED), [Agent].[Agency Name].currentmember ), IIF( STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED).Count = 1, STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED), [Customer Vehicle].[Vehicle Usage].currentmember ), IIF( STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED).Count = 1, STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED), [subscription - Policy Holder].[hGender_RS].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Thank you

|||If I understand your query correctly, regardless of which level the parameter is at, the query should report on the [Last18month] from the end. For example, for 2005 selection, it should cover Jul, 2004 to Dec, 2005. So the filter in my query will always be evaluated at the month level for each of 18 rows - unless the filter should use the same range (Jan, 2005 to Dec, 2005) for each of the 18 rows, regardless of the month? If you could give a simple example of a parameter selection, and how the report should get filtered, that would help clarify.

Thursday, March 29, 2012

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

date range in DRILLTHROUGH Statement

Date Range for Report built against cube using MDX query

Date Range for Report built against a cube using MDX query

Date Range filter

Date Range filter

Date Range filter

Date range + parallel time

Sunday, March 25, 2012

Date picker parameter with MDX...

Wednesday, March 21, 2012

date literals in MDX

Saturday, February 25, 2012

Date filter in MDX

Friday, February 17, 2012

Date comparison in MDX

Date/Time Stamp

Blog Archive

About Me