Price Aggregation Question

Hi all,

I have a cube that has time, product, and location hierarchies. I also have the retail price for every transaction at the leaf level. I am having trouble writing the mdx to get the aggregation the way I need them.

In the time dimension, at the non-leaf level, i want the last retail price of the children in the time. ie: if I am at the quarter level, i want to show the price of period 3.

In both the Product and Location dimensions, I want the retail price to be averaged at the non-leaf levels such as Product category and Zip code.

I don't really know what the right approach is to make this happen, any advice is greatly appreciated.

Thanks,

Wino...

Hi Wino,

Based on my understanding of your scenario, and assuming you have AS 2005 Enterprise Edition, here's an approach to try:

Create a cube measure like [RetailPrice] on the "Retail Price" fact table field, using LastNonEmpty aggregation function.

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!