New Ranks & Badges For The Community!
Notice something different? The ranks and associated badges have gone "Star Fleet". See what they all mean HERE
Highlighted
Absent Member.
Absent Member.
679 views

Excel Reporting in 9.30 - connection to Operational Reporting

Jump to solution

Hi guys

 

Does any one know what needs to be added on the PPM server.conf as a parameter for Operational Reporting so that it can be called from the Excel Reporting?

 

I am trying to get this connection to connect ${reportingdb.execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')}  (page 16 of Excel Cook Book PDF) and I believed there should be a parameter which needs to be setup in server.conf

 

I had a look at Operation Reporting Installation guide and cannot find this

The server that I had already have CP 2.0 installed in it and it is working fine

I probably missed the parameter, but if anyone can help to give the right direction that would be very helpful

 

thanks

Tony Hadiyanto

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

No, as of PPM 9.31, the Reporting DB Password has to appear in clear text in the config. I think there may be some plans to correct that in a future version though.

 

View solution in original post

0 Likes
14 Replies
Highlighted

Hi Tony Hadiyanto,

 

Hope you are doing well.

Here are some parameter you should add to server.conf in order to use the Excel reports for Operational Reporting from PPM Center:

 

REPORTING_BASE_URL
If Operational Reporting is deployed on your system, this is the base URL for your BusinessObjects server.

 

REPORTING_JDBC_URL
If Operational Reporting is deployed on your system, this is the locator for the database that contains the Operational Reporting database schema.

 

REPORTING_DB_USERNAME
If Operational Reporting is deployed on your system, this is the username for the Operational Reporting database schema.

 

REPORTING_DB_PASSWORD
If Operational Reporting is deployed on your system, this is the password for the Operational Reporting database schema.

 

Also, i have attached "Installation and admin guide" for you, please refer on page no.484 for more details.

 

 

Regards,

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Highlighted
Absent Member.
Absent Member.

Hi vinhloc81 

 

Thanks for replying and giving input

So the following is what I have done on the server side:

 

I have enabled and ensure that:

  • tnsnames.ora = gets updated so that it points to the correct db schema (as the reporting schema is separate)
  • follow the steps as per your instruction and added those parameters onto server.conf
  • Reboot the server

Result is still failure and this is the error I am receiving:

 

Running report 6thReport-OperationalReporting.xlsx
Error running report: net.sf.jett.exception.AttributeExpressionException: Null value or expected variable missing in expression "${reportingdb.execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')}". at org.apache.poi.ss.util.CellReference ['Testing SaaS'!A4]
Performance info for Report Report 31665
Report 31665 : 295 ms
  Parsing command : 0 ms
  Preparing Data : 16 ms
  Generating Excel Document : 271 ms

 

In addition, I tried to also run it with Debug mode, and results is attached

Is there anything else that I might have missed?

 

Thanks

Tony Hadiyanto

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Tony,

 

Did you run kUpdateHtml.sh after making changes to server.conf?

 

That's mandatory, as this is what will create the reporting JNDI datasource in PPM Configuration. Without the reporting JNDI datasource created, you'll get the error showed below when trying to use reportingdb object in your excel template.

 

Thanks,

Etienne.

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi Etienne

 

Yes, I did run the kUpdateHtml.sh after modifying the server.conf

 

However the result is as described

 

Thanks
Tony H

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Tony,

 

I keep believing that there's something wrong with your JNDI datasource.

 

Could you check the file server.xml generated from the server.xsl when you run kUpdateHtml.sh? (please obfuscate password). Please share the whole element starting with <Resource name="ReportingDS" ...

 

Here's the part in server.xsl that will be turned into the reporting DB JNDI Datasource in server.xml. You may have some parameter missing or incorrectly set up. I highlighted the parameters used just in case.

 

<xsl:if test="/conf/property[@name='REPORTING_JDBC_URL'] != '' ">
<Resource name="ReportingDS" auth="Container"
factory="com.kintana.sc.security.auth.KintanaSecureIdentityLoginModule"
type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" maxIdle="10">
<xsl:attribute name="url"><xsl:value-of select="/conf/property[@name='REPORTING_JDBC_URL']"/></xsl:attribute>
<xsl:attribute name="username"><xsl:value-of select="/conf/property[@name='REPORTING_DB_USERNAME']"/></xsl:attribute>
<xsl:attribute name="password"><xsl:value-of select="/conf/property[@name='REPORTING_DATASOURCE_PASSWORD']"/></xsl:attribute>
<xsl:attribute name="maxActive"><xsl:value-of select="/conf/property[@name='MAX_ITG_DB_CONNECTIONS']"/></xsl:attribute>
<xsl:attribute name="validationQuery">select 1 from dual</xsl:attribute>
<xsl:attribute name="maxWait"><xsl:value-of select="/conf/property[@name='MAX_DB_CONNECTION_WAIT_TIME'] * 1000"/></xsl:attribute>
<xsl:attribute name="minEvictableIdleTimeMillis"><xsl:value-of select="/conf/property[@name='MAX_DB_CONNECTION_IDLE_TIME'] * 60 * 1000"/></xsl:attribute>
</Resource>
</xsl:if>

 

Do you see any error message in PPM console or in serverLog.txt when trying to run the report?

 

 

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi Etienne

 

This is the parameter which i copied from Node 01 server.xsl

 

 <xsl:if test="/conf/property[@name='REPORTING_JDBC_URL'] != '' ">
   <Resource name="ReportingDS" auth="Container"
      factory="com.kintana.sc.security.auth.KintanaSecureIdentityLoginModule"
      type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" maxIdle="10">
    <xsl:attribute name="url"><xsl:value-of select="/conf/property[@name='REPORTING_JDBC_URL']"/></xsl:attribute>
    <xsl:attribute name="username"><xsl:value-of select="/conf/property[@name='REPORTING_DB_USERNAME']"/></xsl:attribute>
    <xsl:attribute name="password"><xsl:value-of select="/conf/property[@name='REPORTING_DATASOURCE_PASSWORD']"/></xsl:attribute>
    <xsl:attribute name="maxActive"><xsl:value-of select="/conf/property[@name='MAX_ITG_DB_CONNECTIONS']"/></xsl:attribute>
    <xsl:attribute name="validationQuery">select 1 from dual</xsl:attribute>
    <xsl:attribute name="maxWait"><xsl:value-of select="/conf/property[@name='MAX_DB_CONNECTION_WAIT_TIME'] * 1000"/></xsl:attribute>
    <xsl:attribute name="minEvictableIdleTimeMillis"><xsl:value-of select="/conf/property[@name='MAX_DB_CONNECTION_IDLE_TIME'] * 60 * 1000"/></xsl:attribute>  
   </Resource>
  </xsl:if>
 

 

When i ran the report, the following is the error i am receiving: as per attached log

 

It is pointing that JNDI is not getting the connection

I am not sure if any other settings are missing in the server

 

Thanks

Tony Hadiyanto

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Tony,

 

The error confirms that your JNDI datasource is not correctly defined for reporting.

 

Also, we don't need your server.xsl (it's the same for everyone for a given PPM version). We need the server.xml that was generated from it when you run kUpdateHtml.sh.

 

Please also send the list of parameters that you created in server.conf for purely reporting purpose (please get their values from the Server config report from the workbench, just to be sure). These are the parameter names highlighted in RED in my previous post.

 

Thanks,

Etienne.

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi Etienne

 

I have attached the server.xml as found in my node 01

I can see that it is commented out, not sure if that is the way it should be.

 

As well, this is what I had in the server.conf:

#com.kintana.core.server.REPORTING_BASE_URL == we are not using BO htrough URL that is why is commented
com.kintana.core.server.REPORTING_JDBC_URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PPMBOP5-DBSID)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PPMBOP5)))
com.kintana.core.server.REPORTING_DB_USERNAME=xxxx####xxxx (i removed this)
com.kintana.core.server.REPORTING_DB_PASSWORD=xxxx####xxxx (i removed this)

 

Please let me know if there is anymore information require

 

Thank you

Tony H

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Tony,

 

The JDNI datasource is correctly set, so I went back looking at the error.

I just realized from the previous error message (stacktrace) that you're trying to reach the reporting DB using the line:

 

externaldb.get(ReportingDS).execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')

 

There's two problems with this line.

 

1) It will only work if you pass ReportingDS as a String. Here, you're asking to get a datasource named after the value of the variable ReportingDS, which is not set (thus the "null").

 

2) You should use the built-in reportingdb variable to run your SQL, instead of going through the externaldb variable.

 

As a result, you should be able to fix your problem with one of the two following options:

 

externaldb.get('ReportingDS').execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')

 

or

 

reportingdb.execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')

 

Thanks,

Etienne.

0 Likes
Highlighted
Absent Member.
Absent Member.

My bad Etienne, the result of the error was due to the excel file which I only copy to node 01, and I didnt copy to the rest of the nodes AND it seems when the report access it, it was accesing the incorrect file.

The file was also one of my trial and error troubleshooting that is why it was having the correct syntax

 

Anyway, now that we have eliminate the JNDI issue, the incorrect file has been replaced with the correct syntax as per advice, however am looking different error now

 

I am attaching the Excel Report, and the log as well

 

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Tony,

 

The error message looks pretty straightforward to me:

 

ORA-01017: invalid username/password; logon denied

 

Please ensure you input the correct login and password in server.conf parameters.

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.