Measure Label Different in Pivot Table Than Source Project

  • I'm stuck and need some help.

    I'm reviewing a SSAS database designed by someone else who is long gone. The SSAS project was designed in BIDS 2010 and has an Excel pivot table as the front-end to do the reporting.  When I look at a particular measure in the Excel pivot table it has one label.  However, when I look at the design in BIDS I cannot for the life of me find where that label was defined.

    In BIDS, I looked at the Cube design and the properties of the relevant measure.  There it says the label name is "Actual".  However, if I look at that same measure in Excel (I'm sure it's the same), it says "Actual 2014".

    When looking at the cube, I checked the translations, calculations, KPIs, etc... They all say "Actual".

    Where can I find where I can change the label being displayed in the Excel Pivot table?

    Is the BIS project file out of sync somehow with the underlying database (that you can view in SSMS) somehow? If so, how can I make them in sync (I already tried importing existing database to new project with no success. Though I didn't reprocess becuase it throws errors).

    To complicate things further.... If you look at the actual database in SSMS, it doesn't even show the measures at all when drilling down the measure folder.  

    A mess!

  • ptownbro - Monday, January 16, 2017 8:10 PM

    I'm stuck and need some help.

    I'm reviewing a SSAS database designed by someone else who is long gone. The SSAS project was designed in BIDS 2010 and has an Excel pivot table as the front-end to do the reporting.  When I look at a particular measure in the Excel pivot table it has one label.  However, when I look at the design in BIDS I cannot for the life of me find where that label was defined.

    In BIDS, I looked at the Cube design and the properties of the relevant measure.  There it says the label name is "Actual".  However, if I look at that same measure in Excel (I'm sure it's the same), it says "Actual 2014".

    When looking at the cube, I checked the translations, calculations, KPIs, etc... They all say "Actual".

    Where can I find where I can change the label being displayed in the Excel Pivot table?

    Is the BIS project file out of sync somehow with the underlying database (that you can view in SSMS) somehow? If so, how can I make them in sync (I already tried importing existing database to new project with no success. Though I didn't reprocess becuase it throws errors).

    To complicate things further.... If you look at the actual database in SSMS, it doesn't even show the measures at all when drilling down the measure folder.  

    A mess!

    Yeah, sounds like a little bit of a mess.

    In Excel it is possible to change the label of a field. Select the field name in the "Rows" or "Columns" section of the PivotTable Fields list and click on "Field Settings". You'll see a "Custom Name" property in the settings which effectively changes the label.

  • Martin Schoombee - Wednesday, January 18, 2017 8:41 AM

    ptownbro - Monday, January 16, 2017 8:10 PM

    I'm stuck and need some help.

    I'm reviewing a SSAS database designed by someone else who is long gone. The SSAS project was designed in BIDS 2010 and has an Excel pivot table as the front-end to do the reporting.  When I look at a particular measure in the Excel pivot table it has one label.  However, when I look at the design in BIDS I cannot for the life of me find where that label was defined.

    In BIDS, I looked at the Cube design and the properties of the relevant measure.  There it says the label name is "Actual".  However, if I look at that same measure in Excel (I'm sure it's the same), it says "Actual 2014".

    When looking at the cube, I checked the translations, calculations, KPIs, etc... They all say "Actual".

    Where can I find where I can change the label being displayed in the Excel Pivot table?

    Is the BIS project file out of sync somehow with the underlying database (that you can view in SSMS) somehow? If so, how can I make them in sync (I already tried importing existing database to new project with no success. Though I didn't reprocess becuase it throws errors).

    To complicate things further.... If you look at the actual database in SSMS, it doesn't even show the measures at all when drilling down the measure folder.  

    A mess!

    Yeah, sounds like a little bit of a mess.

    In Excel it is possible to change the label of a field. Select the field name in the "Rows" or "Columns" section of the PivotTable Fields list and click on "Field Settings". You'll see a "Custom Name" property in the settings which effectively changes the label.

    Thanks for the response.  That was close but not quite there yet.. =) That was good information though.

    I neglected to say that the label that shows "Actual 2014" is from the "Pivot Table Field List" itself - not from the result set that is on the worksheet.  In other words, it's where you go to "Choose fields to add to report" and create your pivot table. There they have nodes for measures and dimensions you can use to drag and drop on to the sheet to create your pivot table. They presumably read from the underlying SSAS database/project to display the measures you have available.

  • Here's a picture of what it looks like.  You can see where it says "2014 Actual" and "2015 Budget". In the field list, but the underlying database all labels say "Actual" and "Budget" respectively (as it shows even in the "values" section).

  • ptownbro - Wednesday, January 18, 2017 7:41 PM

    Here's a picture of what it looks like.  You can see where it says "2014 Actual" and "2015 Budget". In the field list, but the underlying database all labels say "Actual" and "Budget" respectively (as it shows even in the "values" section).

    Someone has simply typed over the cell in the pivot table. For some reason it changes the name of the measure in the Pivot Table field list when you do this. Change it to something else to see it in action. The clue to this is that in your values box in the Pivot Table field list, the measures have retained their names as defined in SSAS.


    I'm on LinkedIn

  • That was it! Thank you. Geesh... so obvious =D
    Wow, as much as I know about Excel... that just eluded me somehow.
    Thanks again for your help

  • ptownbro - Thursday, January 19, 2017 6:27 PM

    That was it! Thank you. Geesh... so obvious =D
    Wow, as much as I know about Excel... that just eluded me somehow.
    Thanks again for your help

    No worries. It is a bit of a confusing "feature" 😀


    I'm on LinkedIn

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply