LastNonempty return null when filter time hierarchy

Hi there,

I am using SSAS 2005 SP2. Now I have a big problem. My scenario is that:

I have a measure, inventory and set its aggregateFunction to "LastNonempty".

I have a Time dimension and create a hierarchy for it. The hierarchy contains two levels, namely Year and Month.

Everything goes fine when I display the report using BIStudio. However, if I filter the Time hierarchy and set it only to display two months like 2007, 04 and 2007, 05, the measure become null. If I clear the filter and show all 2007's months the measure displays correctly.

Does anything also have that problem?

BTW, I also tried this scenario in MS example database "AdventureWorksDW". Set one measure to be lastnonempty, and create a time hierarchy. Then, set filter to show only two months. The null problem shows again. Hopefully someone can help me to solve this problem.

Thanks in advance.

Best regardly,

Tom

If you browse the [Measures].[End of Day Rate] value in Adventure Works (which has aggregate function to "LastNonEmpty"), selecting multiple months in the filter (for example, May and June 2004) seems to work fine - no Null's appear. Can you reproduce your problem with this standard measure?|||

Hello Puri,

Let me describe the scenario I created from AdventureWorkDW.

I have four dimension tables: DimGeography, DimCustomer, DimTime, DimProduct.

And only one fact table: FactInternetSales.

After the cube was created, I set the aggregatefunction of the measure "Freight" to "LastNonempty".

Besides, I create a time hierarchy in the dimension "Due Date". The hierarchy has two levels, namely "Calender Year" and "English Month Name".

Then I deploy the created cube. I brow it via BIStudio. I drag and drop freight to the measure part and time hierarchy to the upper dimension part. Everything works fine. It can show data of 2001 to 2004 and I can drilldown to show months belong to specific year.

Howerver, if I set the filter to the time hierarchy and only show data of "April and August" in 2003, then I can't drilldown to the month level.

It will show null if I click the "+" sign next to the "2003".

Best Regardly

Tom

|||

Hi Tom,

Without trying to reconstruct the cube you describe, one issue with your time hierarchy may be the use of "English Month Name" at the lower level - since the same month occurs under multiple years, this is not a natural hierarchy. You can refer to the "Month Name" attribute in the Date dimension of Adventure Works, to see how that is defined - note that "Calendar" is a natural hierarchy in the Date dimension.

Here, you might be better off first studying something which works, like the sample Adventure Works cube; rather than first building something which doesn't, then trying to figure out why...

|||

Dear Deepak,

I tried to use Adventure Works Sample Cube. In this cube, I create a new Time hierarchy which contains two levels, Calendar year and Month Name. Besides, I set the aggregateFunction of "Internet Order Quantity" from Internet Sales group to "LastNonEmpty". Then I deploy it and browse it in the BIStudio. I drag and drop the time hierarchy I created and Internet Order Quantity to the measure part. Everthing works fines. Howerver, if I set the filter to only shows "May, 2003 and June, 2003" the null problem still can't solve. At the Year level, it will show "CY 2003" and the value of Internet Order Quantity is "10". If I click the "+" sign, it shows nothing in the month level. Could you help me with this problem?

Best Regardly

Tom