Showing posts with label orderentrydateid. Show all posts
Showing posts with label orderentrydateid. Show all posts

Sunday, March 25, 2012

Calculated measure based on field in fact table that is not a measure in cube.

I have several fields in the fact table that are not measures in a cube. In this case I have a OrderEntryDateId, WantDateId, and InvoicedDateId. If the order has not invoiced the InvoiceDateId is 0 (zero).

My cube joins the fact table to the time dimension table on the wantDateId. The cube has measures for feet, dollars, pounds, material cost...

I want to create a calculated measure called "Invoiced Dollars" that is based on iff(invoiceDateId>0;TotalDollars,0) however invoiceDateId is not a measure nor is it linked to time in this cube.

Any ideas?

Hi. You could make the InvoiceDateID a member property of time dimension at the leaf level. You can then check the member property value in your calulated member. It might look something like this:

IIF(CINT(Time.Time.CurrentMember.Properties("InvoiceDateID")) > 0, TotalDollars, 0)

Drawbacks to this solution: (1) it only works at the leaf level of the time dimension, (2) Probably slow because of the conversion of the member property value.

Hope this helps - Paul Goldy

|||

I already played with this a bit this afternoon and it is not what I need.

I guess the best way to do this is to add a column to the fact table so it can work at all levels and be pre-aggregated.

Thanks!