May 17, 2017 at 12:06 pm
I have data from 1960 to present date in the dimension .
When i use the following stattement it shows me only last 58 data .
{SELECT {LASTPERIODS(58, [SDDS_Time_Dim].LASTCHILD)} ON AXIS(0) , {Descendants([SDDS_Country_Vw].[List],,LEAVES)} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Country_Vw].[List].[Code] ON AXIS(2) , {ORDER(Descendants([SDDS_Series].[List],,LEAVES),[SDDS_Series].[List].Currentmember.Name, basc)} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Series].[List].[Code] ON AXIS(1) FROM [SDDS] }
I need to show data from 1960 to present and not last 58 .
Please let me know how do I rewrite this without effecting performance .
May 18, 2017 at 7:28 am
Not knowing what your date dimension looks like (assuming SSDS_Tim_Dim is your date/time dimension) you could do something like:
SELECT
{
[SSDS_Tim_Dim].[Month]
} ON AXIS(0),
{
Descendants([SDDS_Country_Vw].[List],,LEAVES)} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Country_Vw].[List].[Code] ON AXIS(2) , {ORDER(Descendants([SDDS_Series].[List],,LEAVES),[SDDS_Series].[List].Currentmember.Name, basc)
} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Series].[List].[Code] ON AXIS(1)
FROM [SDDS]
WHERE ([SSDS_Tim_Dim].[Month].&[196001] : [SSDS_Tim_Dim].[Month].&[201705])
The LASTPERIODS() is what is limiting you to the 58 results. You'll need to modify this for your date dimension specifics.
Good luck,
Rob
May 18, 2017 at 8:00 am
Hi
My current time dimension is yearqauater something like 1998Q1
The mdx query is not working
May 18, 2017 at 12:01 pm
You're really not giving much information so anyone can help you. What is the error? How's it not working? The query I posted above, was just an idea -- you needed to adjust it to work in your environment.
Rob
SELECT
{
[SSDS_Tim_Dim].[YearQuarter]
} ON AXIS(0),
{
Descendants([SDDS_Country_Vw].[List],,LEAVES)} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Country_Vw].[List].[Code] ON AXIS(2) , {ORDER(Descendants([SDDS_Series].[List],,LEAVES),[SDDS_Series].[List].Currentmember.Name, basc)
} DIMENSION PROPERTIES MEMBER_CAPTION, [SDDS_Series].[List].[Code] ON AXIS(1)
FROM [SDDS]
WHERE ([SSDS_Tim_Dim].[YearQuarter].&[1960Q1] : [SSDS_Tim_Dim].[YearQuarter].&[2017Q2])
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply