Frequently asked questions

This section lists the frequently asked questions about Excel reports.

Question: I get data from a Dashboard Datasource in my template, but the cell's formatting does not work on these fields. Why?

Answer: By default, a Dashboard Datasource returns every value as string. The development team try to convert string representing numbers to actual numbers (using the server locale for parsing), but nothing is done for date. You can either change the datasource to return already formatted values, or switch to a SQL query, where type of returned data is the same as the SQL type. Another option is to use scripting to parse the string values returned by the datasource into whatever type you need; however the scripting code might be complex.

Question: What is the object “verticadb” available in the template? It errors whenever I try to use it.

Answer: This object has been created in prevision of a possible future Vertica-based Big Data reporting and analytics solution for PPM. It is not to be used for now.

Question: There is an error when running my Excel report, and I canot figure out where the error comes from. How to identify the problem?

Answer: Troubleshooting a faulty report template can be a very tricky task, especially if someone does not have access to PPM Server logs, where more information may be provided. If that happens, you can try to remove parts of the templates until the report works again, and then focus on the last removed part to see whether a change in syntax can fix the problem. If that is not enough to pinpoint the root cause, another option is to start from a blank template and add content little by little until the error can be reproduced.

Question: I try to insert data from PPM database in an Excel table. Even though my tags are in the table, when the report is created, the Excel table is not automatically expanded to cover all the data inserted. Is that a bug?

Answer: No, this is by design. During report generation, the reports engine will try to maintain the formatting of source cells, but it does not automatically expand Excel tables. For this reason, you should convert the Excel table to standard cells in the template by right clicking the table and select Table / Convert to Range . Another option is to use a Named Range as the source data of your Excel table, like demonstrated in Add pivot table and chart. However, make sure not to include the data headers, as the Excel table has its own headers that are not part of the source data.

Question: What are the supported versions of Excel? Can it be used with other spreadsheet applications?

Answer: Excel reports work with any Microsoft Excel versions supporting XLSX format, which means Excel 2007 and later. However, it is recommended to use the same version of Excel for viewing the reports as the version that was used to create the templates. Examples bundled with this document have been created with Excel 2013. Any other application supporting XLSX format can be used to create templates and view Excel reports, to the extent of the supported features, such as charts and pivot tables. That includes tools such as LibreOffice, OpenOffice, or Google Docs.

Question: I’m using Large Data mode, but the report keeps failing with a NullPointerException message. When I select the last line with $$ values with the shortcut CTRL+SHIFT+<END>, it selects some blank cell on the right or under the last line, but I cannot get rid of them. How to proceed?

Answer: You need to copy all your cells with value in a new spreadsheet, and make sure that no cell from your spreadsheet has value if it is located on the right of the last $$ cell. This very tedious constraint might be fixed in a future PPM version.
Question: When I open an Excel Report with a pivot table in Internet explorer, I’m getting an error message stating “We couldn’t get the data from …”.

Answer: If you download the PPM Report in Internet Explorer and use the “Open” menu instead of “Save”, the report file will be opened in read-only mode directly from the PPM Server. It won’t be saved locally, and this will cause pivot datasources not to load correctly when opening the file in Excel 2013 and later. If your report has dynamic pivot tables, please use the “Save” or “Save as” menu when opening PPM Excel Reports in Internet Explorer.

Related links