Export to Excel numeric attribute as text, not number RRS feed

  • Question

  • Product dimension has a UPC Code attribute that has all numeric content. Many of the UPC Codes begin with leading zeros.

    When PDP ver exports the view to Excel 2007, Excel receives them as numbers and strips the leading zeros.  So UPD Code 00934 ends up in Excel as 934.

    Copy/Paste produces the same result.

    How can I somehow code in SQL SSAS or PDP so that text attributes export to Excel as text, regardless of content?

    I tried adding a single quote to the beginning of the code, so that UPC Code 009334 became '00934, since typing a single quote into an Excel cell is how to trigger a number to be treated as text in Excel. But this just exports as is: it shows up in Excel as '00934, including the leading quote!


    Sunday, November 15, 2009 10:57 PM


  • Hi Lori,

    First, make sure you download and install the lateset PDP service pack 3 from the download.microsoft.com site.  Your version is the original RTM. 

    As for the leading zeros, you might be out of luck.  I had a similar case recently and it's a design decision that has limitations.  One of the workarounds we pursued was SSAS and SSRS - but both had limitations.

    ISSUE:  You wanted to keep your leading zeros when exporting/copying/pasting drillthrough results into Excel.  You were also intersted in investigating the possibility of using Drillthough and Report Actions to put the data in SSRS first, to see if that helped you maintain formatting.   


    RESOLUTION:  Unfortunately, you are running into design limitations.  Here are your best options in detail:


    “I spoke at length with a foremost expert on the issue.  He says that what you want to do is a feature desired by many but which just doesn’t exist right now.  You can build a report in SSRS, and pass parameters to it from SSAS Report Actions, but it’s not dynamic.  You must specify the members you will want to see when you build the SSRS report, and then set up the parameters in the Action to be passed to the report.  The parameters can be things such as member names or member numbers.  For example, you could use the .currentmember function to grab data from SSAS and pass it to a corresponding item in the SSRS report.  He says that you should implement a “reasonable subset” of what you think your users might ask for – three or four or five parameters. 


    Here is some recommended information - http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!4567.entry?&_c02_owner=1%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%3f%25&sa=784599407


    • Marked as answer by Lori Wright Friday, December 3, 2010 8:03 PM
    Thursday, December 3, 2009 12:50 AM