jphahn Trusted Contributor.
Trusted Contributor.
730 views

Excel reports on PPMC cluster

Hi

According to the PPMC 9.40 Excel Reports Cookbook, page 12 HP recommends to use a shared folder for Excel Report templates:

By default, the Excel template files are stored at <PPM_HOME>/conf/custom_excel_templates. However, in a clustered environment, a template file must be copied on every PPM_HOME directory of the cluster. This can quickly become tedious to maintain. For that reason, whenever running PPM in clustered configuration, it is advised to define the server parameter EXCEL_TEMPLATES_PATH and set it to a shared folder where Excel templates will be stored. This allows to store templates in one single folder, whatever be the size of your cluster deployment.

I tried to use a shared folder (/opt/itg/share/custom_excel_reports/Reports/pm) but the reports fail to run. I always get a message that the template cannot be found in /opt/itg/conf/excel_templates.

I had a call with HP support but they could not help. Their comment was that it should work and that I should use trial and error to test different setting of the EXCEL_TEMPLATES_PATH parameter. I desperately tried to make the reports run using every possible path combination for the parameter that I could imagine of.

Has any one else ever tried to use the EXCEL_TEMPLATES_PATH server.conf parameter in a clustered emvironment?

Best regards
Jean-Paul Hahn

0 Likes
7 Replies
Micro Focus Expert
Micro Focus Expert

Re: Excel reports on PPMC cluster

Hi Jean-Paul,

What is the shared folder parameter value that you used? Why don't you have a machine name or IP as part of it? If you use is in a cluster, you're supposed to have multiple servers accessing it ; same recommendations apply as when defining a shared Sever.conf location.

"Make sure that you use Universal Naming Convention (UNC) notation (//<IP_Address>/<Shared_Folder> or <File_Server_Name>/<Shared_Folder>) to specify the location of your shared folder."

Cheers,

Etienne.

0 Likes
jphahn Trusted Contributor.
Trusted Contributor.

Re: Excel reports on PPMC cluster

Hi Etienne

Well we have (as proposed by HP) an NFS share which is mounted on every single cluster member under /opt/itg/share. The server.conf for the cluster resides on that share and can be accessed by all cluster members and the cluster starts correctly.

hf1v1@ppmapt12:itg $ df | grep t11
nfst01:/vol/vol_nfs_test_03/ppmc_store_t11  20971520   684096  20287424   4% /mnt/ppmc_store_t11

How should my path look like?

  •  //NFST01:/vol/vol_nfs_test03/ppmc_store_t11
  •  /NFST01:/vol/vol_nfs_test03/ppmc_store_t11 (because it's a linux server?)
  •  /x.y.z.k/vol/vol_nfs_test03/ppmc_store_t11

Cheers

Jean-Paul

 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Excel reports on PPMC cluster

Hi Jean-Paul,

You should use the same format as you used in file PPM_HOME/ppm_server_conf.env to point to your shared server.conf location.

 

0 Likes
jphahn Trusted Contributor.
Trusted Contributor.

Re: Excel reports on PPMC cluster

Hi Etienne

In the ppm_server_conf.env there is only the following line:
export PPM_SERVER_CONF_DIR=/opt/itg/share

The server.conf file resides in that folder.

In the server.conf I added the EXCEL_TEMPLATES_PATH pointing to /opt/itg/share/custom_excel_templates/Reports and this did not work. I also tried with custom_excel_templates/Reports and this did nor work either.

Any suggestions what could be changed?

Best regards
Jean-Paul Hahn

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Excel reports on PPMC cluster

Hi,

Whatever works for shared server.conf should work for shared templates folder. If it doesn't, I'd consider this a defect. 

I'll try to reproduce the issue, but in the meantime you might want to open a case with MF support to get this properly tracked. 

Thanks,

Etienne.

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Excel reports on PPMC cluster

Hi Jean-Paul,

I can't reproduce the issue on my environment, but it's a windows environment so that's only half-useful. Please provide more info:

1) You said that you set server.conf parameter EXCEL_TEMPLATES_PATH to /opt/itg/share/custom_excel_templates/Reports . Can you verify in the admin console (or in the workbench server config report) that the correct value is actually set for this parameter?

2) What is the report template path that you're using in your report type? i.e. can you copy the line of the report type command step starting with "ksc_run_excel_report"?

3) Can you provide the full path to the report template file stored on your disk in the shared folder? PPM generates the full path of the template file to load by adding the value of EXCEL_TEMPLATES_PATH,  a slash, and the template path defined in the report type (with backslashes replaced by slashes). Is it a match for you? Can you access the file on your disk using that path with the same unix user running the PPM server?

FYI, here's the current logic used by PPM to retrieve an Excel template:

1) Get the value of parameter EXCEL_TEMPLATES_PATH.  If it is undefined or empty, use <PPM_HOME>/conf/custom_excel_templates.

2) Generate the full template  file path by adding a slash and the template path (defined in the report type) after the path defined in 1) 

3) If this full template path does not point to a valid file that exists, repeat 2)  but with PPM_HOME/conf/excel_templates instead (that's where all the ootb templates are). 

The logic in point 3) is the reason the report error message always points to /opt/itg/conf/excel_templates when the file cannot be found. 
You might wonder why we don't show an error message mentioning the EXCEL_TEMPLATE_PATH location, and why we're looking for it in /conf/excel_templates, where we clearly stipulate is a very bad location to store excel report templates? Well, the reason is that every PPM page generating XLSX (except portlets export) share the same code as excel reports, and most of the time (i.e. when you export a PPM page to Excel) we do NOT expect to find a custom template in the custom location, because we'll likely use the built-in templates (when exporting a search results page for example). So getting templates from the custom location is more the exception than the rule - unless of course you're running a custom Excel report. 

I'll try to fix that in next PPM version and provide more information in the excel report logs about the  locations in which we looked for the excel template, not just the last folder containing built-in templates.

Thanks,

Etienne. 

 

 

0 Likes
jphahn Trusted Contributor.
Trusted Contributor.

Re: Excel reports on PPMC cluster

Hi Etienne

When i put

com.kintana.core.server.EXCEL_TEMPLATES_PATH=/opt/itg/share/custom_excel_templates/Reports/pm/

then everything works. The EXCEL_TAMPLATES_PATH is correctly displayed in the admin console. I even renamed the /opt/itg/conf_custom_excel_templates folder in order to be sure that nthe file is not found there and then report still works.

The command is ksc_run_excel_Report Report_name.xlsx

I made another test and this was also successful even after removing thr trailing / ffrom the parameter

Best regards

Jean-Paul Hahn

 

 

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.