locked
Excel - Power Pivot - MDX - Duplicate column headers within the same column returned (e.g. table1column9column9) RRS feed

  • Question

  • Hi All,

    I have a problem which I am hoping you can help with.

    I don't know how to get the accurate column header displayed from an MDX Power Pivot query within excel.  Instead i see the column heading duplicated (e.g. Table1Column9Column9 instead of Column9) which makes it hard to read and create meaningful reports from. 

    Within MDX designer our cube has a folder/table called 'Work Order' and a column called 'Work Order Number'.  When I drag this to the query, it displays correctly as 'Work Order Number', however when i click 'Ok' and 'Save' and return the Power Pivot Window which dislays the data, i see a column called 'Work OrderWork Order NumberWork Order Number'.  Ideally this should be called 'Work Order'.

    Below is the MDX generated via the designer mode:

    SELECT
      
    NON EMPTY {
        
    [Measures].[Count Work Order Number]
      
    } ON COLUMNS,
      
    NON EMPTY {
        
    (
          
    [Work Order].[Work Order Number].[Work Order Number].allmembers
        
    )
      
    } DIMENSION PROPERTIES member_caption, member_unique_name ON ROWS
    FROM ( SELECT
           
    (
             
    m5580744
           
    ) ON COLUMNS
         
    FROM [cube_WorksManagement]) CELL PROPERTIES value, back_color, fore_color,
    formatted_value, format_string, font_name, font_size, font_flags  

    I would be extremelty grateful if others can help point me in the right direction in how to resovle this.

    Thank you

    Tuesday, March 27, 2018 5:27 AM

Answers

  • Hi cRIMSOn_BI,

    Thanks for your question.

    >>>however when i click 'Ok' and 'Save' and return the Power Pivot Window which displays the data, i see a column called 'Work OrderWork Order NumberWork Order Number'.  Ideally this should be called 'Work Order'.
    For this issue, please right click on the column, select rename column, then you can rename the column name from 'Work OrderWork Order NumberWork Order Number' to 'Work Order'. See below images:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 27, 2018 9:38 AM
  • Hi cRIMSOn_BI,

    Thanks for your response.

    >>>So ideally I am looking for a non-manual way of updating these column names?

    Unfortunately, you would have to update these column names currently. I am really curious, since you already have created a SSAS cube, you can just use that SSAS cube. It is not necessary to import it from cube,  and it is much better to connect to SSAS cube directly(connect live) other than import to generate power pivot report using EXCEL as report client ,not development tools.

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 5, 2018 7:40 AM

All replies

  • Hi cRIMSOn_BI,

    Thanks for your question.

    >>>however when i click 'Ok' and 'Save' and return the Power Pivot Window which displays the data, i see a column called 'Work OrderWork Order NumberWork Order Number'.  Ideally this should be called 'Work Order'.
    For this issue, please right click on the column, select rename column, then you can rename the column name from 'Work OrderWork Order NumberWork Order Number' to 'Work Order'. See below images:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 27, 2018 9:38 AM
  • Thanks for the detailed reply and screenshots.

    I was aware that i could rename there, however the problem is that I have 120 columns in one file and in the others I will need a difference 40 columns here, 30 columns there etc.

    So ideally I am looking for a non-manual way of updating these column names?

    Cheers

    Wednesday, March 28, 2018 12:57 AM
  • Hi cRIMSOn_BI,

    Thanks for your response.

    >>>So ideally I am looking for a non-manual way of updating these column names?

    Unfortunately, you would have to update these column names currently. I am really curious, since you already have created a SSAS cube, you can just use that SSAS cube. It is not necessary to import it from cube,  and it is much better to connect to SSAS cube directly(connect live) other than import to generate power pivot report using EXCEL as report client ,not development tools.

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, April 5, 2018 7:40 AM