way to get "Sales Comparison" ...

[johnwps]

Say, need to compare A Store’s this year sales with B store’s last year sales,
Now, we have four dimensions
1.Dim Year –Fiscal Year List
2.Dim Year_Compare - Fiscal Year List(copy of Dim Year)
3.Dim Store – Stores Name List
4.Dim StoreCompare - Stores Name List (copy of Dim Store)

Two measures,
1.Sales,
2.SalesLastYear.--- (Copy of Sales measure)

In the report,
to select 2009 - 2008 - StoreA - StoreB as parameters,
we could have report like
Year (2009) -last year (2008) - Store (A)-Store (B) – Sales (2009)-Sales (2008).


It’s easy to get “A” store’s this year V.S. “B” store’s last year’s sales comparison. However, I do think that’s not good method to get value comparison.
I appreciate any help to find a way to get optimized solution for this kind of comparison report.

 

[johnwps]

Let's go another way,

Say, we got

Dim Year, Dim Store
and Measure Sales,

need to compare A store this Year's Sales with B Store last year's Sales.

How to the comparison result? MDX?

thanks a lot!

 

[jymm]

this was a great article when I was doing something similar back in '08... might give you a good start...

http://www.simple-talk.com/sql/sql-server-2005/advanced-matrix-reporting-techniques/