Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX filter last periods where Month exists Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 8:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:03 AM
Points: 1, Visits: 70
Hi

I have been trying to work out a solution to this problem for a couple of days now but I am relatively new to Analysis Services.

I have a query that selects the number of responses (measure), ports (dimension) for the last 2 month periods (dimension)

It is acceptable if there is no data for previous month
If there is no data for the most recent month then the port is to be ignored
Here is my query which works for some ports and not others

SELECT

NON EMPTY {

[Measures].[Responses]

}

ON COLUMNS,

NON EMPTY

FILTER (

{

[Port].[Port].[Port] * LASTPERIODS(2, STRTOMEMBER("[Time].[Month].&[2013-05-01T00:00:00]"))

},

STRTOMEMBER("[Time].[Month].&[2013-05-01T00:00:00]"))

ON ROWS

FROM [Cube]

I'm pretty sure my logic isn't correct but I've been trying out using EXISTS, sub queries, WHERE clauses and this is the closest I have came to a resolution.

Thanks in advance Graham
Post #1462440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse