Has anyone figured out a way to automate a time period named set like “Current Calendar Month” to generate based on an MDX calculation?
Use Case: We have a report we want to run on a schedule (every Sunday night after the data is loaded from our Financial System). We want it to:
Pull current calendar month data (MTD & YTD) based on today’s date
Use a named set “Current Calendar Month” to determine time period to pull
Have the named set determined by an MDX formula that won’t have to be updated (we currently have to go in every month and select the new period on the first of the month & publish the change)
It seems like we should be able to come up with an MDX formula for this named set and eliminate the need to update it manually every month on the first of the month. In Excel, this would be something like =Month(Today()).
Thanks for replying Tim - the same is true for us.
We have two sets of reports. One is for our current “reporting” month (the one we are trying to close), and the other is for the current “calendar” month (essentially year to date through yesterday). The calendar month helps us stay on top of issues and address them quickly
We use the following MDX: StrToMember("[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]")
It’s easiest to explain from the inside out.
Now() gets the current date/time.
DateAdd("M", 9, Now()) gets the date/time exactly 9 months from now, which is needed because we have a March year-end. So if it is currently 02/2020, this would result in 11/2020 which is the correct fiscal period.
Format(DateAdd("M", 9, Now()), "yyyy""M""MM") represents the resulting date in the MonthKey format used by the Time dimension. For February 2020 this would result in “2020M11”
"[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]" is basic concatenation which results in a valid member path. Here we get “[Time].[Default].&[2020M11]”
Finally, wrap that all up in a STRTOMEMBER() function and, if your string is valid it will result in the member.
I was able to replicate the current month calculation in model manager but am having limited success when using it on reports due to our use of unique combinations for the columns. For reports with unique combinations, the new calculated field is not available for selection from the Named Set options. When I create a new report that only has one member in the column section of the report, the new Named Set option is available.
Our reports have unique combinations enabled so we can show multiple versions/time frames on a single report:
Yes there are limitations around using named sets together with unique combinations.
One workaround I’ve used is to place multiple data views side by side on the report so that they appear as a single table.
For example in your case you could have three data views, each with the Time dimension as a page-level selection: Current Month, YTD, and Full Year. Each of those would have the versions you want in the columns, and each would have to have the exact same set of rows so they align across.
Thank you again, @murray.mckernan. The calculation is working great.
I am having some issues with the three separate data views, though. The month column doesn’t have the same row set because we are hiding unused rows to make the report shorter. So the first data view is missing a few rows that the full year data views have, so they aren’t lining up. If I uncheck the unused rows box, the one page report becomes a six page report. Do you have another work around hiding up your sleeve?
Yes, but again it involves some MDX. The solution is to create a dynamic named set on the dimension that you want to display on the rows. The named set will be responsible for filtering and ordering of members so that any data view that uses the named set is guaranteed to have a consistent set of rows regardless of the selections being displayed on the report columns.
The DESCENDANTS function in this example (green) returns all leaf-level members of the “Account” dimension
The NONEMPTY function (blue) filters this list of members to those which have data at the intersection of all the dimension members listed in the perentheses (a “tuple” in MDX terminology). Which members should you use here? See below.
The ORDER function sorts this resulting list according to the value and direction specified. In this case it would sort by the account name, in ascending order.
When defining the tuple that will be used for the NONEMPTY function, a good rule of thumb is to include any dimensions that are in your data views’ columns, plus any dimensions at the page level that you do not want to affect the rows being displayed. Including a dimension in this tuple will make it disregard column or page selections for row filtering purposes. The data itself will still reflect page and column selections, but the rows will remain consistent.
If I’m reading the code correctly, it looks like you have a time perspective set up for full year. We don’t have that, but it sounds pretty useful. Do you mind sharing the MDX?
Is it possible to refer to a Time Nameset in the MDX when using Order?
I am struggling to order the Divisions when I use ‘Current Month’ Time Nameset. However, when I hardcode time (eg 2020M02), the MDX orders the divisions. Can either of you take a look?
First, the syntax to refer to a named set is simply [Current Month]. Fully qualifying it like [Time].[Default].&[Current Month] causes it to look for an actual member with the key “Current Month”, which doesn’t exist.
Second and more importantly, you can run into trouble if you try to refer to one named set in the definition of another named set. This is because at the database level, all your different named sets are defined in one big MDX “cube script” which is executed every time the cube is processed. Because you can’t control the order of operations within this script, you don’t know which named sets will be defined first during execution and if one named set refers to another which is only defined later in your script, you’ll get an error.
This is why in the example above, I’m not referring to [Current Month] directly, but rather have embedded it’s definition StrToMember("[Time].[Default].&[" + Format(DateAdd("M", 9, Now()), "yyyy""M""MM") + "]")
Named set doesn’t work for MDX. You can replace &[CurrentMonth] with CurrentMember, and put Time dimension at Page. Then CurrentMember is the time selected at Page.
Since I need 2 Time Periods, that wont work for what I’m trying to achieve. Instead, I hardcoded
time and will change it every month to plot current Ytd & Prior Ytd data on a chart.
So…I went about this the long way around because I had some trouble with MDX (I’m learning for the first time). Here is the workaround:
Set up a Time dimension Named Set “Current Calendar Month” (static calc is fine) with the following formula (Note: if you are not on a calendar fiscal year, you can use the 0 below in the Date Add part of the formula to adjust to your calendar. Murray’s fiscal year starts in Sept, so he puts a 9 there to adjust to make his year start then. We use a calendar year as our fiscal year so I replaced the 9 with a 0).
Set up a Time dimension Named Set “Current Calendar Year” (static calc is fine) with the following formula. Same as above, except it returns a year YYYY instead of Year-Month YYYY-MM.
I created a calculated version for Full Year using the formula provided by my Prophix implementation support Sahil that results in the full year budget no matter which month is selected:
I used two different views to set up my report using the above formulas. Because a month column is limited to a single month (Feb, for example). It may not include the same accounts as Jan. We had an unbudgeted expense in Jan, so there was an amount in the actual for that month. In Feb, we do not have activity in that account, so it was hiding the row on the report in the month columns, but displaying that row on the YTD column. I tried Murray’s formula and couldn’t figure out how to modify the supplied code to match our setup. What I did instead was create a new Version called “Current Month Placeholder” (calculated type) where I added all versions that might be referenced on the report within a given year (our actuals, final budget, and four projections)
Create your report with two views (using the same view as the base). Limit the first view to BASE perspective and make the second view YTD perspective
Link the two views so you can make a selection in one and it will apply to the other. I used this for the members in the pages (project, dept, time) BUT do not link the time perspective. I would recommend hiding the time perspective so the viewer of the report doesn’t get confused by two perspective selections on one report (also, you don’t want them changing perspectives!).
That was a very long/complicated workaround and I promise there is a better way to do it. With my baby MDX skills (and limited time) this was the best I could do. It is working for us (so far) without a hitch (fingers crossed/knock on wood). Hopefully this helped you find a solution to your issue.
