Contributor.. Shiwan_Agrawal Contributor..
Contributor..
245 views

Old PPM Excel Report doesn't show pivot data

Jump to solution

Hello All

I have configured an excel template in which there are two sheets 1> Raw Data 2> Pivot

Raw Data sheet generates data as per configured SQL query and Pivot sheet creates pivot data configured in excel template.

If I run report and open, both sheets show proper data but if I try to open an old report (without saving it on my local), raw data works fine but pivot doesn't generate proper data.

Same old report if I save and then open will work fine.

Anyone has faced this issue? Please advise why is it happening?

Thanks,

Shiwan

 

0 Likes
1 Solution

Accepted Solutions
Contributor.. Shiwan_Agrawal Contributor..
Contributor..

Re: Old PPM Excel Report doesn't show pivot data

Jump to solution

Hello Steven

 

Thank you for your response. I got the reply from Etienne which says it is a limitation on excel 2013. See below:

______________________________________________________________________________________________________________________________________

Hi Shiwan,

I confirm that I can reproduce the issue. When I try "Open" in IE11 when downloading a report, if it has a pivot with a datasource, I got the error message "We couldn't get the data from 'DataRange' in the workbook 'http://localhost:8080/itg/reports/rep_30400.xlsx'. Open this workbook in Excel and try again".

This seems related with some Excel behavior that Pivot datasources will explicitely link to a local XLSX file that they were saved in, and that won't work if the file is stored remotely (you'll notice that when you open the XLSX file on PPM server, it's actually read-only, you need to save it locally for changes to be possible).

It may be related to the issues described below, however I couldn't find any workaround or solution to make it work in our case (i.e. get file from remote Server): http://stackoverflow.com/questions/29545080/excel-pivot-table-data-source-after-file-name-change-refers-to-previous-file-na https://social.technet.microsoft.com/Forums/systemcenter/en-US/9fbaf480-089d-4383-bb31-b260d693d189/pivot-table-data-source-keeps-referring-to-old-file-name?forum=excel http://www.networksteve.com/exchange/topic.php/Excel_2013_not_updating_Pivot_Table_data_ranges_correctly.%C2%A0_Plea/?TopicId=50087&Posts=6

Looks like this behavior was introduced in Excel 2013 and didn't exist before - but I don't have an earlier version of Excel installed to confirm.

I'll document that limitation in PPM 9.40 Excel Cookbook.

Thanks,

Etienne.

______________________________________________________________________________________________________________________________________

Hope it will help other users.

Regards,

Shiwan

 

0 Likes
2 Replies
Valued Contributor.. Steven_R_PPM Valued Contributor..
Valued Contributor..

Re: Old PPM Excel Report doesn't show pivot data

Jump to solution

Hello Shiwan,

It could be related to the below known Excel issue:

It is not related to PPM. It is a Microsoft Office issue. As a MS Office user, We face the same issue.

https://softwaresupport.hpe.com/group/softwaresupport/search-result/-/facetsearch/document/LID/QCCR1L50035?lang=en&cc=us&hpappid=202392_SSO_PRO_HPE

https://softwaresupport.hpe.com/group/softwaresupport/search-result/-/facetsearch/document/LID/QCCR1L46523

This is a current known Excel limitation that PPM (well, before 9.22) only export to "Excel" as some HTML content in a file with an XLS extension, prompting Excel to display a warning upon opening the file.
 
This is a "feature" of Excel 2007. The current Excel design does not allow one to open HTML content from a web site in Excel. So, ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert.
 
If an HTML MIME type is used, then the web browser will open the content instead of Excel. So, there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. One's own MIME type can be added, if it can control both the web server and the client desktops that need access to it. Otherwise the best option is to use a different file format or alert users of the Warning and tell them to select 'Yes' to the dialog.
 
The logical explanation that I can give is that from 2007 Microsoft changed the extension for all their office products. Like Excel was previously .xls not its .xlsx. Similarly word was .doc and not its .docx. So if you try to open a file with old format, you will get this warning. However, this is just my opinion as a Microsoft Office user. You may want to contact Microsoft technical support to confirm this.
 
Below are the supported formats of Microsoft Office Excel 2007
· Microsoft Office Excel 2007 binary workbook (*.xlsb)
· Microsoft Office Excel 2007 workbook (*.xlsx)
· Microsoft Office Excel 2007 macro-enabled workbook (*.xlsm)
· Microsoft Office Excel 2007 template (*.xltx)
· Microsoft Office Excel 2007 macro-enabled template (*.xltm)
· Microsoft Office Excel 2007 add-in (*.xlam)

We'll keep migrating pages one after the other to use "Real XSLX export" in future PPM versions, but this is a very long work as we can just do it once for the whole application, we need to do it page by page and it's very time consuming.

By the way, you can check the following article http://support.microsoft.com/kb/948615 to do some testing and certified that the warning message can be got rid of on the client side.

Hope it helps,
Steven

0 Likes
Contributor.. Shiwan_Agrawal Contributor..
Contributor..

Re: Old PPM Excel Report doesn't show pivot data

Jump to solution

Hello Steven

 

Thank you for your response. I got the reply from Etienne which says it is a limitation on excel 2013. See below:

______________________________________________________________________________________________________________________________________

Hi Shiwan,

I confirm that I can reproduce the issue. When I try "Open" in IE11 when downloading a report, if it has a pivot with a datasource, I got the error message "We couldn't get the data from 'DataRange' in the workbook 'http://localhost:8080/itg/reports/rep_30400.xlsx'. Open this workbook in Excel and try again".

This seems related with some Excel behavior that Pivot datasources will explicitely link to a local XLSX file that they were saved in, and that won't work if the file is stored remotely (you'll notice that when you open the XLSX file on PPM server, it's actually read-only, you need to save it locally for changes to be possible).

It may be related to the issues described below, however I couldn't find any workaround or solution to make it work in our case (i.e. get file from remote Server): http://stackoverflow.com/questions/29545080/excel-pivot-table-data-source-after-file-name-change-refers-to-previous-file-na https://social.technet.microsoft.com/Forums/systemcenter/en-US/9fbaf480-089d-4383-bb31-b260d693d189/pivot-table-data-source-keeps-referring-to-old-file-name?forum=excel http://www.networksteve.com/exchange/topic.php/Excel_2013_not_updating_Pivot_Table_data_ranges_correctly.%C2%A0_Plea/?TopicId=50087&Posts=6

Looks like this behavior was introduced in Excel 2013 and didn't exist before - but I don't have an earlier version of Excel installed to confirm.

I'll document that limitation in PPM 9.40 Excel Cookbook.

Thanks,

Etienne.

______________________________________________________________________________________________________________________________________

Hope it will help other users.

Regards,

Shiwan

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.