none
SSAS2008 r2 and Excel Line Chart and Date Axis

    Question

  • Hi

    Excel 2010. SSAS 2008 R2

    I have a excel line chart made out from SSAS pivot table with a date dimension (date) and No of licences.

    Currently, it displays 1/Jun/2014 to 30/Jun/2015 (FY2015) and this looks really ugly. 

    While keeping the details, I'd like to display by Month-Year. 

    However, while I can do this with other normal excel data, I just can not do it with SSAS Pivot table.

    Under Axis options, I ticked 'Date axis' but it won't bring up date options. 

    I think I tried everything but just can not figure it out. I wonder if it is even possible.


    Th date dimension is recognised as date because I can apply date filter.  I want to make it neat. Can anyone help me with this please? 

    It must be 'Date' Level. 

    Kind regards





    Friday, August 29, 2014 8:43 AM

All replies

  • Your best bet is to either create a new text field with the exact display format you'd like, and make sure to sort by your date field to maintain the proper order. You can also reformat the display of your date field in the Power Pivot window itself. Unfortunately, since Power Pivot and Excel are two different engines, your formatting might not come across exactly the way you want.
    Tuesday, September 02, 2014 2:22 PM
  • Hi SQLMa,

    Could you please elaborate more detail regarding the "Date" dimension structure? In order to display the date format with Month-Year, we should build a hierarchy to organize the granularity of date dimension at SSAS level. Please refer to the documents below:
    Create a Date type Dimension: http://msdn.microsoft.com/en-us/library/ms174884.aspx
    Modifying the Date Dimension: http://msdn.microsoft.com/en-us/library/ms166578.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, September 03, 2014 6:01 AM
    Moderator
  • Hi Elvis

    Thank you for the reply.

    Date dimension structure is Date - Month - Quarter - Year. 

    However, I can not roll up to Month. That would give a wrong number. It can not use accumulated math to do it. 

    I am not sure what you are suggesting. Are you suggesting making Date to display as Month? this won't work as I have other measures needs to be displayed as date sometimes. 

    Kind regards, 


    • Edited by SQLMa Thursday, September 04, 2014 2:42 AM
    Thursday, September 04, 2014 2:40 AM
  • Hi Greg

    Thanks for the reply.

    Did not think about using Powerpivot. Hm.. I think it could work but it is not a solution I can apply as my users have no knowledge of Powerpivot and they don't want to know. 

    Kind regards,

    Thursday, September 04, 2014 2:43 AM
  • @SQLMA, sorry for my misunderstood.

    If you want to format the date display format for the PivotChart, maybe we should design the expected date format underlying SSAS data source, such as named calculation in SSAS Data Source View.
    New Named Calculation in SSAS DSV: http://www.msbiguide.com/2011/12/new-named-calculation-in-ssas-dsv/

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, September 04, 2014 3:24 AM
    Moderator
  • I only suggested Power Pivot because that's the forum we're in.

    You can change the data formatting in SSAS similarly. Or you can create a text field which contains the date formatted exactly as you need.


    Thursday, September 04, 2014 2:18 PM