locked
Cannot order month names column correctly in Excel PivotTable and PivotChart (Months are in alphabetical order instead of by their month number) RRS feed

  • Question

  • Hello,

    I have a PivotTable and a PivotChart done in Excel which display "Total Amounts" described in 3 levels of details (per Fiscal Years, per Calendar Years, and per Months). The Fiscal and Calendar Year field are both numeric values, and the Months field values are the first three letters of the month names (ex: Feb, Mar, Apr, etc). 

    The problem is that when I expand all levels to display all the details the Month level is displayed in alphabetical order. This means that "Apr" totals will appear before "Feb" totals which is not what I want. I am looking for the month names to be ordered in their correct order. Just in case, I have a field in the Field List that has the month numbers but I don't know how to use it to "order by" my PivotTable and PivotChart without displaying the field.

    The PivotTable is using a database as the source. The sql query that I used has an "ORDER BY" which orders the data correctly (By using the column which has the month number) but this order is not followed by the PivotTable nor PivotChart.

    Any ideas to make my month names to be in the correct order in my PivotTable and PivotChart?

    Thanks in advance!
     


    Wednesday, March 3, 2010 3:00 PM

Answers

  • I take it you are not using a Analysis Service data source for this...correct?  If you were you would relate the month number to the name and change the orderby property to reference that attribute instead.

    In your case it sounds like you might just be referencing a tabular data source because you mention a sql query.  You would basically need to add the month number to the front so that it showed up as 01 Jan, 02 Feb, ...., 12 Dec. 

    If you can switch over to utilizing a SSAS cube that would probably be the way to go to full leverage the PPS product, but not always an option I guess.  These OWC pieces are not going to be in the next release in PPS 2010, so just make sure you plan accordingly.


    Dan English's BI Blog
    Thursday, March 4, 2010 10:11 AM
  • If PivotTable and PivotChart is the direction you want to go I would do this with Excel Services.  Create the PivotTable and PivotChart in Excel and then move then move this out to Excel Services referencing the DCL and items in the Excel file.

    Using Excel Services Reports with PerformancePoint Server (PPS)

    You can do the same thing that you are trying to do and the PivotChart will reference the PivotTable changes.


    Another option to explore as an alternative solution would to be go with SSRS where you will have greater control of the format since you are just referencing a SQL Server query for your source.  If you eventually go with SSAS then you could leverage more of the PPS product with the Analytical components.

    I would still stay away from the PivotTable and PivotChart components in PPS since this is leveraging OWC and support will be dropped going forward.
    Dan English's BI Blog
    Friday, March 5, 2010 11:50 AM

All replies

  • I take it you are not using a Analysis Service data source for this...correct?  If you were you would relate the month number to the name and change the orderby property to reference that attribute instead.

    In your case it sounds like you might just be referencing a tabular data source because you mention a sql query.  You would basically need to add the month number to the front so that it showed up as 01 Jan, 02 Feb, ...., 12 Dec. 

    If you can switch over to utilizing a SSAS cube that would probably be the way to go to full leverage the PPS product, but not always an option I guess.  These OWC pieces are not going to be in the next release in PPS 2010, so just make sure you plan accordingly.


    Dan English's BI Blog
    Thursday, March 4, 2010 10:11 AM
  • Thanks for your response.

    I think I found HALF of the solution to the problem.

    Instead of having a month field with the name or number of the month I make the field to be a complete datetime value. That means that instead of displaying "Feb", "Mar" or "Jan" on the month field I display "02/01/2010", "03/01/2010", "01/01/2010" and so on. This values can be converted into datetime values in the sql query output.

    When you use this field in the Excel PivotTable you can just select that column field and change the format by using the "Format Cells..." option on the right-click menu. Here you can custom your date value to display whatever you want, in my case I set the format to "mmm", which displays the first 3 letters of the month name. Since it's a date value and not a string, you can order the PivotTable using the normal sorting options and it will sort correctly!

    The problem is that my PivotChart displays the full datetime value ("02/01/2010", "03/01/2010", "01/01/2010" and so on) and doesn't display the PivotTable format.

    Any help with this problem? Other altenatives??????

    Thanks.
    Thursday, March 4, 2010 2:46 PM
  • If PivotTable and PivotChart is the direction you want to go I would do this with Excel Services.  Create the PivotTable and PivotChart in Excel and then move then move this out to Excel Services referencing the DCL and items in the Excel file.

    Using Excel Services Reports with PerformancePoint Server (PPS)

    You can do the same thing that you are trying to do and the PivotChart will reference the PivotTable changes.


    Another option to explore as an alternative solution would to be go with SSRS where you will have greater control of the format since you are just referencing a SQL Server query for your source.  If you eventually go with SSAS then you could leverage more of the PPS product with the Analytical components.

    I would still stay away from the PivotTable and PivotChart components in PPS since this is leveraging OWC and support will be dropped going forward.
    Dan English's BI Blog
    Friday, March 5, 2010 11:50 AM