Dynamic list that returns latest four months based on current date
Hi there,
I currently need help producing a dynamic list that can be imbedded into my 'GAAP Accounting Period' elements list which, based on the current date, selects the last four months.
The 'GAAP Accounting Period' contains elements all the way back into the 1900s and goes into the future up to 2050 which is causing the difficulty. It is formatted as 'YYYYMM' (202212 as an example). I need to somehow incorporate a dynamic list that only selects the latest 4 GAAP Account Periods. So, as of today, that would include 202212, 202301, 202302, and 202303 but next month it would automatically adjust and include only 202301, 202302, 202303, and 202304.
I am currently doing this manually using a range list which I can apply to the Account Month Period elements. The following is the script mode of this list:
{([GAAP Accounting Period].[GAAP Accounting Period].&[202212]:[GAAP Accounting Period].[GAAP Accounting Period].&[202303])}.
How can I do this?
Thanks,
Ben
2 replies
-
Hi Ben,
OLAP cubes (SQLServer Analysis Services) unfortunately do not have the concept of a DATE type. The Names of the elements in your [GAAP Accounting Period] are simply strings, like [202303], thus there are no direct DATE() type functions available to set the end of the range to today's Year Month value.
What we have to do is to invoke the Analysis Services VBA function, Now(), to get the system date, then format that to match the format of your [GAAP Accounting Period] member names, like [202303], then concatenate that to the Attribute name to create a string that is the same as the fully qualified member name, like [GAAP Accounting Period].[GAAP Accounting Period].&[202303], then map that to the equivalent member in the attribute using the MDX function StrToMember().
Phew!
That will give us the end of the range. We can generate the start of the range using the MDX Lag() function on the same expression to give the Year Month 3 places back in the Attribute, i.e., [GAAP Accounting Period].[GAAP Accounting Period].&[202212].
Assuming you have given the accurate Attribute name, you should be able to copy/paste the code below to give you your dynamic 4 month list that will move forward as each new month comes around.
{StrToMember("[GAAP Accounting Period].[GAAP Accounting Period].&[" + Format(Now(),"yyyyMM") + "]").Lag(3) : StrToMember("[GAAP Accounting Period].[GAAP Accounting Period].&[" + Format(Now(),"yyyyMM") + "]")}We're setting Lag() to 3 as that will give you the last 4 months, starting with today's Year Month, [202303], then counting back 3 places to get to [202212], so you will get December 2022, January 2023, February 2023, March 2023. Obviously when April 1st rolls around, the end of the range will be 202304 and the start of the range will be 202301.
Hope that helps.
Ian