locked
SSAS 2008 and Excel Pivot RRS feed

  • Question

  • Hi,

    I am having requirement from the business users to have the dimension members in the excel pivot to be of number data type.

    Here is some background -

    My SSAS cube has Date Number (meaning day of the month e.g. 1, 2, 3...etc) and Month Number (meaning Month number of year e.g. 1,2,3 .... 11,12) in the Date dimension. The data types of these fields Date Number and Month Number in the data source bindings are integer from the physical relational table. While defining the dimension attribute properties for these fields I tried to preserve their original data types (i.e. integer) for these fields.

    The SSAS cube is processed and business users create excel pivot from the Cube. When the users drop Date Number or Month Number dimension attributes from Date dimension on the column or row axis in the Excel pivot then values displayed are string rather than numeric format in the excel.

    Does anyone know about this excel behavior related to pivot tables from SSAS? Let me know if more information is needed.

    Thanks,

    Ketan

    Wednesday, February 15, 2012 9:33 AM

Answers

  • Hi Ketan,

    I reproduced the issue with Excel 12.0.6565.5003 (SP2).

    If you don't specify a NameColumn, KeyColumn will be used (after converting it to a string) - for example, I tried it with aJanuary, bNovember, cFebruary and dOctober; returned sorting A to Z in this order even if the keys were 1, 11, 2, and 10 (for each, in order). When I used January, February, October, and November, Excel automatically treated them as months and sorted them by the month number. Even if I set the keys (in the previous order) 10, 11, 2 and 1 :) But it might be effected by the client's Regional Settings.

    So it seems that the sort order only depends on the displayed label when you select Sort A to Z.

    Unfortunately I don't have access to Excel 2007 with SP3 at the moment to verify a post in an earlier forum discussion which stated that this behaviour had been fixed in a patch (which was included in SP3), but I'll try to check it somehow.

    Please check and share the build number of Excel in the meantime. Try with installing SP3 if it's not applied yet.

    Two workarounds came in my mind in the time being:

    1) unfortunately it's only for one sort direction. If you select the small button which displays the sort and filter options, select "More Sort Options...", then under Sort Options, select "Data Source Order". There is no option for using a reversed data source order. However I think this is what you'd like to see. In my second example, it returned November, October, January, and February as it was expected.
    2) Use a string type key column for this attribute (!!! sounds horroristic, I know, but no worries :)) and use a leading zero, so 01 instead of 1; no way that 11 will be sorted before 02 :) No NameColumn is needed in this case.

    Hope it helps,
    Zoli


    • Edited by Zoltán HorváthMVP Thursday, February 16, 2012 5:05 PM Forgot to finish a sentence. sry :)
    • Marked as answer by Challen Fu Tuesday, February 21, 2012 5:39 AM
    Thursday, February 16, 2012 5:02 PM