Getting the Application URL in a Jaspersoft Report

[Robert_Ensinger]

Hi all.

  I'm working on a report what we wish to provide hyperlinks back into the CA PPM App. I'd prefer to not hard code the URL. What is the best way to get the CA PPM Application URL in a Jaspersoft Report?

 

  I'm open to any and all ideas. Is the application URL easily accessible somewhere in the datamodel? The best idea I have at this time is the properties.xml stored in CMN_CONFIG. In Jasper, I could use the linked database syntax and pull the 'sslEntryUrl' value out of the properties file. I have no experience using xpath in SQL but I'm trying to work this out now. Below is a little 'hello world' that I'm working on now - any xpath in sql advice is appreciated, OR if there is an easier alternative approach to dynamically getting the application URL in a Jasper Report I'm open to all ideas.

 

SELECT 
P.NAME
,  CAST(P.VALUE AS XML).query('data(/properties/directories/@installDir)') as 'Install Dir'
FROM CMN_CONFIG P
WHERE P.NAME = 'properties.xml'

 

Thanks!

[David Morton]

I've done it (not in MSSQL though) in a slightly low-tech way by pulling the bit of text following schedulerURL=" out from that VALUE record in CMN_CONFIG (using convoluted instring and substring string processing stuff)

 

Worked OK, not as neat as XPATH tho' I think.

[Robert_Ensinger]

I was eyeing that schedulerURL - I can't think of a situation (for our implementation at least) where schedulerURL doesn't equal the application URL... but I've traveled this far, so...

 

SELECT 
P.NAME
, CAST(REPLACE(REPLACE(CAST(P.VALUE AS NVARCHAR(MAX)),'utf-8','utf-16'), '&', '&') AS XML).query('data(/properties/webServer/webServerInstance[@id="app"]/@sslEntryUrl)') as 'CA PPM URL'
FROM CMN_CONFIG P
WHERE P.NAME = 'properties.xml'

 

 

Things I have learned this morning (we're MSSQL Server, Oracle will certainly be different):

1) The Value column's data type is ntext. This needs to be converted to an XML data type.

2) The properties file is UTF-8. This needs converted to UTF-16 and on MSSQL you need to convert the ntext to nvarchar first.

3) Ampersands sting.

 

Surely there has to be an easier way than this - but we're done here. I'm chewing on the Idea for making this easier. So far I think the easiest approach would be to add another column to CMN_CONFIG with a Data Type in MSSQL of 'XML' and  'XMLType' in Oracle and keep a copy of the properties.xml there as well, that way we can simply xPath our way into this data without having to jump through character and data type hoops.

[Nika Hadzhikidi]

Hi Robert,

 

Thanks for posting your results and solution.

The application URL will only be available in the CMN_CONFIG table for properties.xml row, as in your solution. There is no other place we can get this in the PPM database.

Just as another idea to add to the topic, it's also possible to create a custom field on any Clarity object saying "Application URL" and enter the value there, then just add the field to DWH and then the report.

The downside will be that you'll have to modify the value if the URL changes (or refresh is done on the data).

 

Kind Regards

 

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[Alistair Miller]

This might be a daft suggestion, but ... most sites I've been on have a Code Constants object (or something similar) where the User Name and Password of a XOG User (and other helpful items) are stored.  Would an entry in there to store the Application URL be of any use whatsoever?