March 9, 2014 at 1:39 pm
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
March 9, 2014 at 1:41 pm
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.
March 9, 2014 at 9:05 pm
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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply