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 Query using a date range Expand / Collapse
Author
Message
Posted Sunday, March 9, 2014 1:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
Hi All.

Yet another newbie to SSAS here.

I'm trying to write a query which select data about financial transactions. The requirement is to select the the last 3 years. (Current year and 2 previous). We're trying to build a dashboard whihc will have a dozen charts on it, using a simialr approach.

The current year would be provided as a parameter, or derived from a subquery.

So far I have my query working using a range, but it's hard coded. How do change it so that it is dynamic? This is the query so far:

 SELECT NON EMPTY { [Measures].[Actuals], [Measures].[Forecast] } ON COLUMNS, 
NON EMPTY { ([Financial Periods].[Financial Periods].[Financial Year].ALLMEMBERS *
[Financial Periods].[Financial Period Status].[Financial Period Status].ALLMEMBERS *
[Financial Periods].[Financial Period Of Year].[Financial Period Of Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (SELECT ( {[Financial Periods].[Financial Periods].[Financial Year].&[2011-07-01T00:00:00]:[Financial Periods].[Financial Periods].[Financial Year].&[2013-07-01T00:00:00] } ) ON COLUMNS
FROM [Financials])

WHERE ( [Chart Of Accounts].[Chart Of Accounts].[Level 2].&[OPEX] )

What I want to do is derive the "currentyear" and in the "where clause" say: from currentyear:currentyear-2

So.. How do I set the "current year", and then how do I refer to it with my range filter? Is there a better way of doing this? is Filter() better? if so why?

Any help or suggestions would be greatly appreciated.

Cheers

Pete
Post #1549074
Posted Sunday, March 9, 2014 1:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
I'm still thinking in SQL terms.. and so what I want to do is this... in MDX... (I know the syntax below won't work but im just trying to illustrate the point).

declare @CYear varchar(10)

set @CYear = '2012/2013'

begin

select datepart(year, dueDate), count(*)
from [AdventureWorksDW2012].[dbo].[FactInternetSales]
where datepart(year, dueDate) between @CYear and (@CYear -2)
group by datepart(year, dueDate)

end ;

Ideally i'd like to derive the current period from within the query from the cube.
Post #1549076
Posted Sunday, March 9, 2014 9:05 PM This worked for the OP Answer marked as solution
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
Woohoo!!! I think I have it sorted.

Here's my query:

SELECT NON EMPTY { [Measures].[Actuals], [Measures].[Forecast] } ON COLUMNS, 
NON EMPTY lastperiods(3,exists([Financial Periods].[Financial Periods].[Financial Year].members,[Financial Periods].[Financial Year Status].&[Current]).item(0)) ON ROWS

FROM [Financials]
WHERE ([Chart Of Accounts].[Chart Of Accounts].[Level 2].&[OPEX])

It uses the Lastperiods() function to work out the date range (3 Periods. 1 current and 2 previous), based upon the Current Financial year which derived in the second part of the function: [Financial Periods].[Financial Year Status].&[Current]).item(0)
Post #1549096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse