none
PWA 2010 View Excel Export Formatting Missing! RRS feed

  • Question

  • I'm getting alot of complaints from users about the Export to Excel feature in PWA2010 exporting without the formatting (missing bold summary tasks, indenting, and expand/collapse). In PWA 2007, saveral views were created tro satisfy customer report requests. When exporting these views to Excel in PWA2007, they exported with indented WBS structure, expand/collapse on summary level, and bold on summary level by default with no additional formatting required.

    We re-created these same views in PWA2010 when migrating (migrated to another existing platform, not upgeade). However, when Export to Excel is used, the data exports to Excel without any of the indenting, bold summary tasks, or expand/collapse as it did in PWA2007.

    Is there some setting that was missed or can be set to bring this functionality back in the export to Excel? Reports are now very difficult to read and determine where in the structure tasks are without the indenting and bold and cannot be collapsed as before when reviewing. Any help to get this functionality back in the export to Excel would be greatly appreciated.   


    Rick Frisby

    Friday, July 20, 2012 12:54 PM

All replies

  • Rick,

    Why not create a custom Excel report, using the BI features, instead of using Export to Excel? This would provide a higher level of control over the final product.

    Treb Gatte

    Friday, July 20, 2012 6:30 PM
    Moderator
  • Hi Trab,

    Thanks for the response. That's one of the first things I thought of, but it does present some problems. First, the primary report is used by team resources and customers and I would have to give those folks access to BI, which would open up alot of other data we don't want these folks to see. The current view used to drive a report locked down to a specific group/category with specific fields, so no additional security needed. Second, looking through the reports/templates/data conections, I am not finding the fields I need to recreate the primary report in BI (ID, WBS, Task Name, % Complete, Duration, Resource Initials). I would have expected to see these in OlapTasksNonTimePhased, but they are not there. Lots of great analysis fields, but fields to construct basic task report with WBS structure are not jumping out. I'm still looking through these, but it would be helpful if you can point me to a specific template/report with an OLAP cube that can give me those fields.

    P.S. - Are the bold summary, indent, and expand/collapse features that used to export to Excel in EPM2007 now taken away in EPM2010?  


    Rick Frisby

    Friday, July 20, 2012 7:11 PM
  • Treb,

    Sorry I spelled you name wrong earlier. The full field list for the primary report is: ID, Task Name, % Complete, Duration, Start, Finish, and Resource Initials. These are specific fields the customer wanted provided maintaining WBS structure and we have been providing up to this point via that formatted task view Export to Excel. I'd be real interested to know if this can be duplicated in BI as well as a view so we can get the formatting back.


    Rick Frisby

    Friday, July 20, 2012 7:45 PM
  • Rick,

    If they are set up as report viewers, they can open the report in Excel Services, but they can't open the report in Excel client while connected to the datasource. See http://technet.microsoft.com/en-us/library/ee662106.aspx for more details on the Report Viewers group.

    They can only download a snapshot (similar to the Export to Excel) via Excel Services, except that it maintains formatting.

    The fields you seek are OLAP Properties. Therefore, once you add Task List, right-click on the field and select Show Properties in Report. Task Index is ID, FYI. The only thing missing is Resource Initials. You may want to check out the AssignmentNonTimephased Cube to see if it gives you everything.

    Hope this helps,

    Treb Gatte - @tgatte

    Friday, July 20, 2012 8:42 PM
    Moderator
  • Hi Treb,

    A step closer, but still I was able to get the fields I needed from Properties as you suggested and get a resource list from OlapPortfolio Analyzer. However, formatting of the properties fields is a real issue. The properties  fields (duration, finish, Percent Complete, start, Task Index) populate in the BI report theough the entire WBS, but are not in the order needed and seem to be fixed in position. When I try to move these fields into the correct order, I'm finding I cannot drag them to the proper position and if I try to cut/insert into the correct position I get a "You cannot move a part of a pivottable..." and "...Use the PivotTable field list to add, move, or remove fields" but these items are not listed in the PivotTable field list.

    Also, the properties fields won't hold format. You can format and save the report (e.g. - Formatted start and finish as date only), but as soon as the query runs, all formatting on the properties fields is lost. Not sure why this is as other formatted Excel Services reports retain their formatting when the query is run. I'm really scratching my head on these items. Have you run into the same problems adding properties fields to the reports or know how to rearrange these items?

    I'm going to have to look further into how to configure a single report for specific users as we already have a defined Report Authors and Report Viewers group setup, but these groups allow respective users to see all BI reports. . Thanks for the link.


    Rick Frisby

    Monday, July 23, 2012 5:42 PM
  • FYI - Did find a way to reorder the columns on the properties fields. You must use the Pivot Table Tools > Options Menu, then click Property Fields and change the order on the "Choose Property Fields for Dimension" dialog box.

    Still looking for a way to retain the formatting of the data after query retrieval.


    Rick Frisby

    Monday, July 23, 2012 6:31 PM
  • Right click on the pivot table and select Pivot Table options. On the Layout and Format tab, ensure that Preserve cell formatting on update is checked.

    Treb Gatte

    Monday, July 23, 2012 10:44 PM
    Moderator
  • Yeah, thats what I would thought too. I previously verified that was checked and even toggled a few times, trying both ways. With Preserve Formatting checked, formatting remains intact in the report for everything except the "properties" fields. In those columns, the header remains formatted, but as soon as the query runs to refresh the data, the data formatting in the "properties" fields is lost. Very strange behavior :(

    Rick Frisby


    • Edited by Rick VF Tuesday, July 24, 2012 12:30 PM
    Tuesday, July 24, 2012 12:29 PM
  • Rick,

    Is the issue in Excel Services or Excel Client? I'm not seeing this in the client.

    --Treb

    Tuesday, July 24, 2012 7:44 PM
    Moderator
  • Treb,

    I see it in both. I open the report using Edit in Excel, which opens Excel (client), make the format changes, and save. The changes hold until the query runs, then the properties fields lose their format when the report query completes.

    Formatted and saved to the server...

    After OLAP Query refreshes the report...

    This is with "Preserve Cell Formatting on Update" checked. I triple checked. 


    Rick Frisby

    Wednesday, July 25, 2012 12:44 PM
  • I got this behavior to repro and I think I found a solution. When I applied the Date format to the entire column, it didn't impact the Pivot and it wiped out the format on every refresh. Bizarre.

    Once I did the following, the format stayed after refresh.

    1. Hover your cursor over the Start Date column heading until the down arrow appears.
    2. Click to select all cells in the pivot table
    3. Right-click and select Format Cells
    4. On the Number tab, select Date and then the format
    5. Click OK.

    The format should stick now.

    Let me know if this works for you.

    Treb Gatte


    Wednesday, July 25, 2012 6:43 PM
    Moderator
  • Hi Treb,

    I followed your instructions exactly and the formatting did not stick once the query refreshed. Very strange. Also, out of curiosity I tried formatting just one cell in several of the properties data columns and the formatting of those single cells stuck. Next I tried highlighting part of a column without highlighting the whole column and in this case some remained formatted and some didn't in the same column. Very bizzarre behavior!


    Rick Frisby

    Friday, July 27, 2012 12:54 PM
  • Rick,

    I'm on the latest Office client CU so maybe this was patched. Very strange.

    Treb Gatte

    Friday, July 27, 2012 5:49 PM
    Moderator
  • Rick,

    I'm on the latest Office client CU so maybe this was patched. Very strange.

    Treb Gatte

    Friday, July 27, 2012 5:49 PM
    Moderator
  • Good point. I've requested an update to the latest CU. I believe that is the June 2012. As soon as pushed to us, I'll see if that corrects this problem. Thanks for the help thus far. Really appreciate the effort :)


    Rick Frisby

    Monday, July 30, 2012 12:00 PM
  • Hi Treb/Rick,

    Has this issue been resolved by June 2012 CU update or do you have any other solution steps for this issue? We are also facing the similar issue.

    Thanks

    Anuranjan Prasad


    Thanks & Regards Anuranjan Prasad


    • Edited by Anuranjan Prasad Thursday, December 13, 2012 10:51 AM wrongly spelled
    Thursday, December 13, 2012 10:50 AM
  • Anuranjan,

    We did the CU update and no change. The bizzare formatting problem exists. No matter what, as soon as the query runs, all property field formatting is lost. Not sure what else to do as everything in this thread was tried with no change. Its a shame as it was looking promising as a way to get this data to the customer without having to go through all this manual formatting intervention after pulling the data. Sure wish they would bring the formatting back to Excel Reports like they had in EPM2007. Much simpler to just export the view ready to go.

    Rick 


    Rick Frisby

    Thursday, December 13, 2012 3:28 PM
  • Thanks Rick for your response.

    Thanks & Regards Anuranjan Prasad

    Thursday, December 13, 2012 5:44 PM