Is This Possible? Defaulting of a Dimension Atrribute

I have a dimension with one visible attribute, let's call it "Status." This attribute is binded to one column from a forecast fact table that stores monthly snapshots. This column simply has a 'Yes' or 'No' value for every record in the fact table.

For a specific customer and product combination in this fact table, this "Status" column could have started off having a value of 'No' a few months ago, and now has 'Yes' for the current month. In other words, it can have both possible values.

For example, let's assume the following forecast data for the Customer A and Product B combination:

Month | Forecast $ | Status
-
November 2006 | $5 | 'No'
December 2006 | $10 | 'No'
January 2007 | $10 | 'Yes'

This "Status" attribute is meant to be used at the Page Field level in Excel, and users would like to be able to see customer forecast data based on the "Status" being filtered for either 'Yes' or 'No'.

Now, all of our measures in the cube have the following MDX ((TAIL(EXISTING [Date].[Month].MEMBERS).ITEM(0).ITEM(0), [Measure]). This defaults the measures to the current month in a report unless a time frame, whether year(s) and/or month(s), is explicityly specified or filtered at the Page Field level.

Assuming a timeframe is not explicitly specified at the Page Field level and a user is looking at the forecast data for Customer A for Product B, this customer will show up regardless of what the "Status" Page Field is filtered for. I believe this is the case because it historically has had a record associated with both 'No' and 'Yes'. What does differ hough, is whether or not Forecast $ will populate or not.

For example, Status = 'Yes', then Forecast $ will show $10. If Status = 'No', then Forecast $ will now be NULL. Ideally, if Status = 'No', this customer would not even show up for the current month.

So is this possible? Hopefully what I'm asking makes sense.

Thanks!From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.|||

Mosha Pasumansky wrote:

From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like. Would it be easier if you took a look at my solution file to see what I may have missed? I've tried this numerous times with no luck.

Once I get this behavior resolved, I'll follow up with you with the MDX.

Thanks!|||

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

|||

Mosha Pasumansky wrote:

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

My fault. My mind must have been elsewhere when I posted. I did as you suggested with no luck. I set it to false from the default of true.

I did not follow your suggestion on the LastChild semiadditve aggregation type yet, as I wanted to focus on the above since I'm not too familiar with this LastChild thing. Now that I think about it, I assume this wouldn't be available to me in Standard edition? We're running Standard Edition.|||In fact, LastChild is the only semiadditive aggregation type available in Standard Edition - so you got lucky :)|||Anyone have any other thoughts? Is what I'm looking for even possible?

The IsAggregatable property when set to false, simply removed the 'All' member and didn't do what I am seeking.