I think I've found a bug in the way the Excel Add-In generates MDX under certain 'rolling' conditions. The requirement I have is to be able to forecast at the day level for a rolling 6 months; starting from the current period (which is to be updated each week) running for a period of 180 days (~ 6 months)
To prevent requiring 180 columns, a dimension property based filter must be available to select the month in which to forecast. This will provide a more concise data entry form detailing up to 31 days of the selected month in which to add forecast values.
My form is dimensioned up as follows:
| Dimension | Position |
| Employee | Filter |
| Time(Month) | Filter (Dimension Property) |
| Scenario | Filter |
| Location | Rows |
| Time (Day) | Columns |
I set up the columns as a dynamic range to ensure that the forecast 'rolls' with changes in current period. The range was set from current member id + 0 : current member id + 180. [Current Period is set to 16th September 2008 - today).
The simplified MDX that this produces is below:
select
{
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0)
:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180)
}
*
{
[Measures].[Value]
} on columns,
{
descendants([Location].[Standard].[All].[All Locations],,after)
} on rows
from
(
select
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
where
{[Employee].[Employee].[All].[John Doe]}
*
{[Scenario].[All Members].[All].[Forecast]}
The first element to notice is that the columns have been set to a range using ancestor at the member id level and lag to cover the 180 days:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0)
:
Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180)
The next point to highlight is the sub=query that represents the selected time dimension property value (September 2008):
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
When you run this in SSMS, the following data set is returned:
The Locations appear on the rows, the days appear on the columns - exactly as required.
By changing the sub-query filter to October 2008 - the next month in the range, and definitely covered by the -180 day lag (Not sure why the Lead function isn't used here?) - results in a problem, the results returned now are missing the day level columns:
The root of this problem is the column expression - if you replace the column expression with a direct lag on the current period member the expected results are returned:
select
{
[Time].[Base View].[MemberId].&[20080916].Lag(0)
:
[Time].[Base View].[MemberId].&[20080916].Lag(-180)
}
*
{
[Measures].[Value]
} on columns,
{
descendants([Location].[Standard].[All].[All Locations],,after)
} on rows
from
(
select
{[Time].[Month].[All].[September 2008]} on columns from [LocationPlan])
where
{[Employee].[Employee].[All].[John Doe]}
*
{[Scenario].[All Members].[All].[Forecast]}
Now, the only workaround I can come up with is to build the form using a custom MDX formula so I reckon this warrants raising a bug on connect - which I've logged here:
https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=368206&SiteID=181