Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  AS2K Formats ignored by Excel Pivot tables?

Author  Topic  tderoo Starting Member 4 Posts Posted - 2008-02-25 : 02:23:57 Hi I'm struggling with a formatting problem of a measure in AS2K which should display the Average Departure time of our flights. In the measure I have set the format to be "hh:nn". This seems to work fine in the Cube Editor, however when adding this measure in an Excel 2003 or 2007 pivot table the value is formatted 02-01-1900 11:10 rather than 11:10 which I would expect. Is this an Excel issue or should this normally work? It really confuses my end users to see a date in 1900 when they expect to see a time. So my attempt at a work around was to create a calculated measure which just returned an already formatted string value. Format([Measures].[SumDept]/[Measures].[SumLegs],"hh:nn"). Although this shows the expected times in Excel as time values only it introduces some slightly unexpected behaviour. In the first scenario it would show only the 3 aircraft that have flown with their average departure time, however when using this new calculated member it suddenly shows all aircraft groups, even the ones that did not fly! (sigh) For the ones that did not fly the value is left blank, but I would want to leave them out altogether. So generally is there an issue why Excel pivot tables miss out these formats set in AS2K and if so is there a work around? Generally I like the Excel pivot table for cube analysis but are there simple/cheap sulutions available that integrate better with AS2K cubes? Theo de Roo Mission Aviation Fellowship