I have the following situation in my cube:
Shop A uses calendar Cal1. Their 1st sales month starts Jan 5th. Shop B uses calendar Cal2. Their 1st sales month starts Jan 10th. Shop C...etc
I need to produce a daily (reporting services) report with the actual calendar date as a parameter. The list of shops is also a multi select parameter. If a user selects the 15th of Jan, I need to show the combined MTD sales for all shops
selected in the parameters. So that would mean the first 10 days of sales for shop A and the first 5 days of sales for shop B etc. Note this means the MTD calc has to work over sales months rather than calendar months.
Any ideas on how best to set up my dimensions/attributes to make this work?
I am implementing multiple calendars using a bridging table between my date and calendar dimensions. It is the technique described here: http://duncansutcliffe.wordpress.com/2010/06/11/a-better-date-dimension/
I can not hard code the calendars as there is a requirement to possibly add more
View Complete Post