MDX Query in SSAS with date range parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MDX Query in SSAS with date range parameters

Author

Topic

jaspreetsingh8
Starting Member

3 Posts

Posted - 2010-02-15 : 08:11:27

Following is my mdx query

SELECT NON EMPTY
{
[Measures].[Cache Attendees Count]
}
ON COLUMNS,
NON EMPTY
{
([Cache Attendees].[Visit Id].[Visit Id].ALLMEMBERS *
[Cache Attendees].[User Id].[User Id].ALLMEMBERS *
[Cache Attendees].[Screen Name].[Screen Name].ALLMEMBERS *
[Cache Attendees].[User Type Id].[User Type Id].ALLMEMBERS *
[Cache Attendees].[User Type Name].[User Type Name].ALLMEMBERS *
[Cache Attendees].[Group Date Count].[Group Date Count].ALLMEMBERS *
[Cache Attendees].[Insert Time Stamp].[Insert Time Stamp].ALLMEMBERS )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(
SELECT
(
STRTOMEMBER(@FromCacheAttendeesInsertTimeStamp) : STRTOMEMBER(@ToCacheAttendeesInsertTimeStamp)
) ON COLUMNS FROM (
SELECT
(
STRTOSET(@CacheAttendeesConferenceId) ) ON COLUMNS FROM [Cube_Attendee])
)
WHERE ( IIF( STRTOSET(@CacheAttendeesConferenceId).Count = 1, STRTOSET(@CacheAttendeesConferenceId), [Cache Attendees].[Conference Id].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I want to filter my cube with three parameters

1. @CacheAttendeesConferenceId
2. @FromCacheAttendeesInsertTimeStamp
3. @ToCacheAttendeesInsertTimeStamp

When i pass following parameters
ConferenceId = 1, StartDate='2010-01-28T00:00:00', EndDate='2010-02-03T00:00:00'

Then it show records

But When i pass following parameters
ConferenceId = 1, StartDate='2010-01-27T00:00:00', EndDate='2010-02-03T00:00:00'

Then it display message No Data Available

PLease help me on this issue why not FromDate & ToDate range works properly.

Jaspreet Singh

JOPE87
Starting Member

1 Post

Posted - 2010-04-12 : 07:59:20

Jaspreet Singh,

Did you find a solution for this? I'm facing the same problem I think.

The only thing I have noticed is that it seems to work when the value of the parameters @FromCacheAttendeesInsertTimeStamp and @ToCacheAttendeesInsertTimeStamp match with an existing member with that value.

Thanks in advance,
Johann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources