none
Multiline Text Fields in Excel OData Reports RRS feed

  • Question

  • Hi Everyone,

    I seem to have run into a bit of a snag with a Project Online report requirement.  I need to insert the contents of a multiline text custom field but Excel doesn't render the content very well since these fields allow formatting which is stored as HTML tags in the string.  As a result, the content just appears as plain text with tags.

    Has anyone developed a strategy for dealing with these types of custom fields in OData reports or is this a "perhaps we can reimagine your status reporting..." conversation with the customer?  Unfortunately, external SSRS reporting is not an option.

    Chris


    Wednesday, December 3, 2014 12:03 AM

Answers

All replies

  • We are using macro. After importing data, we are pressing button with macro, which delete all html tags from fields. If you want I can share.

    Wednesday, December 3, 2014 8:08 AM
  • Marc, a fellow Project MVP posted this exact same question here. You might reply to it in order to know if he found an easier way than an Excel macro.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, December 3, 2014 1:16 PM
    Moderator
  • I think a macro is the only way but then the Excel file will not refresh in the Excel Web App as it will state something like macros not supported...

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, December 3, 2014 1:20 PM
    Moderator
  • Hmm, yeah, I was hoping to avoid the macro solution for the very reason you state - plus it's an extra step and doesn't preserve any formatting that one might want.  Perhaps HTML tag support will be added to PowerView or something.  Alternatively, an option to force multiline text fields to be plain text would work since you can make Excel respect line breaks with word wrapping.

    Thursday, December 4, 2014 4:23 PM
  • What you could do (given that you find the required time and energy to write the lines),
    would be to replace all (!) html characters like here (http://stackoverflow.com/questions/14705605/remove-html-tags-from-cell-strings-excel-formula - this is one of the Excel UDF/VB-based solutions, but will not refresh in Excel Services - however there is a good list of what to replace) with PowerQuery.
    That would refresh over a PowerBI subscription in the least..


    -Ville


    • Edited by VilleV Thursday, December 18, 2014 7:40 PM
    Thursday, December 18, 2014 7:40 PM