arputh Contributor.
Contributor.
220 views

Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hi,

 

I have and ppmdb.execQuery and I need to have report parameters to filter the data. One parameter works fine i.e.

<jt:forEach items="${ppmdb.execQuery('select .... where project_type_id = ?',F_PROJECT_TYPE)}" var="prj">

 

As soon as I use more than one report parameter then the no data returns i.e.

<jt:forEach items="${ppmdb.execQuery('select .... where project_type_id = ? and trunc(to_date(project_start_period_start,\\'dd/mm/yy\\')) >= trunc(to_date((select start_date from ppm_fiscal_periods where fiscal_period_id = ?),\\'dd/mm/yy\\'))',F_PROJECT_TYPE,START_DATE)}" var="prj" >

 

Can anyone assist? (Attached please find the excel template with all details)

 

I have looked at the JETT - JDBC Exection and it says that for each "?" you add you should add a parameter in the execQuery method.

 

Thanks,

Alfred Puth
PPM Consultant
EOH Application Management
0 Likes
1 Solution

Accepted Solutions
Absent Member.. fabsvz Absent Member..
Absent Member..

Re: Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hello Arthur,

 

According to the excel reports guide, you can pass parameters to a SQL query by inserting a question mark (?) in the query where the parameters stand, and pass as parameters values as extra parameters of the execQuery() method.

 

The execQuery method is simply calling it to preform work. Essentially it works like– Select all values from Table containing the printer driver information. This “table” contains lots of information on the printer drivers.

 

I am attaching the Excel Reports guide where you can find information.

 

Our best recommendation in this case since it is related to query constructor is to contact PSO (Professional Services Organization), this department get involved directly with the customers in order to provide services that are out of the standard support scope, such as customizations, step by step implementations, "How to…" assistance, queries customizations, and much more.

 

Best regards,

Fabiola Vargas

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
4 Replies
Absent Member.. fabsvz Absent Member..
Absent Member..

Re: Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hello Arthur,

 

According to the excel reports guide, you can pass parameters to a SQL query by inserting a question mark (?) in the query where the parameters stand, and pass as parameters values as extra parameters of the execQuery() method.

 

The execQuery method is simply calling it to preform work. Essentially it works like– Select all values from Table containing the printer driver information. This “table” contains lots of information on the printer drivers.

 

I am attaching the Excel Reports guide where you can find information.

 

Our best recommendation in this case since it is related to query constructor is to contact PSO (Professional Services Organization), this department get involved directly with the customers in order to provide services that are out of the standard support scope, such as customizations, step by step implementations, "How to…" assistance, queries customizations, and much more.

 

Best regards,

Fabiola Vargas

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
Micro Focus Expert
Micro Focus Expert

Re: Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hi Alfred,

 

The SQL looks correct, but did you verified that the value of START_DATE is the actual period ID, and not a period Name or a date value?

 

If the query returns zero results, it means that the query works and that the 2 parameters are taken into consideration, but that the resulting query doesn't return any result.

 

Also, you might want to double check what's the actual SQL Query string that's executed when all the quotes and backslashes are de-escaped. You can try saving the SQL query to a string first (using a block of code), and then inserting the string value in a cell for control, and then passing the string variable as the first parameter of the execQuery() method.

 

Let me know how that goes.

 

Thanks,

Etienne.

 

PS: And no, creating Excel Reports should not require a PSO or Partner engagement, even though that would sure help 🙂

 

 

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hi Alfred,

 

I do confirm that it works fine to pass more than one parameter to the SQL query. See the attached templates that can be run from a report type passing a string (project name starts with...) and a project type parameter.

 

I'd advise you to run the report in Debug mode in order to validate whether value received by the report template for Period_ID is actually the one you're expecting to get.  Not getting the expected Period_id might explain not getting any result back.

 

Thanks,

Etienne.

0 Likes
arputh Contributor.
Contributor.

Re: Excel Report 9.31 - Report Parameters not filtering data as expected

Jump to solution

Hi Etienne,

 

Thanks for the feedback.

 

I found my fault, I verified agains our production data but my query executed against the Developement database.

 

My bad, should read properly what I do next time.

 

It works 100%

 

Thanks

Alfred Puth
PPM Consultant
EOH Application Management
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.