Fiscal Year in Excel When Browsing SSAS Cube

Question

Hi

I am using Excel as the front end to a SQL Server Analysis Services (SSAS 2008) cube. I have a "calendar" dimension, which consists of a hierarchy of year-quarter-period where period is a 4 or 5 week month.

Excel offers lots of useful options under its "Date Filters" menu such as being able to select just Quarter 1 etc. This works in that it filters the data based on the underlying date, however, the calendar being used by Excel is a normal calendar rather than a fiscal calendar. Excel treats Quarter 1 as being January, February March, whereas my fiscal quarter 1 is the first 13 weeks in the year starting April 1st. Is it possible to get Excel to use a fiscal calendar?

Thanks

Darren

Answer 1

I'm pretty sure Excel is merely exposing the relationships in the cube to you. The SSAS cube is what maintains the breakdown from years to quarters to periods, not Excel.

If you have access to modify the Cube, the best option is to create Fiscal Calendar attributes in the Date dimension. If you use the built-in wizard to create your Date dimension, you can select normal as well as fiscal calendars. This gives you access to both calendars in Excel, and you can pick whichever one you want to use at a certain time.