Absent Member.. AbdulMajeed_1 Absent Member..
Absent Member..
1512 views

Parse multi select filter values to Excel report

Hi Team,

 

I am working on a new excel reporting feature in 9.3. I am not sure how to parse multi select report filter values to query. I am trying to pass Role ID to the report query like below format but it works only for single value and fails for multi select values. Any specific syntax for multi select filter in excel reporting?

 

<jt:forEach items="${ppmdb.execQuery('SELECT USERNAME, FULL_NAME, START_DATE, END_DATE, DEPARTMENT_MEANING DEPARTMENT, MANAGER_FULL_NAME MANAGER,  RESOURCE_CATEGORY_MEANING CATEGORY,  roles.ROLE_NAME ROLE FROM KNTA_USERS_V u,  RSC_RESOURCES r,  RSC_ROLES roles WHERE RESOURCE_FLAG = \\'Y\\' AND u.ENABLED_FLAG = \\'Y\\' AND r.user_id = u.user_id AND r.PRIMARY_ROLE_ID  = roles.ROLE_ID (+) AND roles.role_id in (?)',RP_ROLES)}" var="resource">

 

When i try multi select values, it gives below error..

Error running report: net.sf.jett.exception.AttributeExpressionException: Null value or expected variable missing in expression

 

Kindly help.

 

Regards,

Abdul

 

0 Likes
11 Replies
Absent Member.. alex-h Absent Member..
Absent Member..

Re: Parse multi select filter values to Excel report

Hi,

 

You can use DEBUG mode to see the list of parameters and their content.

 

Excel reports uses bind variable, and you have to find a method to split multivaluated values (connect by prior + regex).

 

 

Example :

with temp as  (
       select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
       union all
       select 109, 'test2', 'Err1' from dual
     )

SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name

 

PPM will interfer each time it find [ or ], so to avoid that, you have to replace [ and ] by respectively CHR(91) and CHR(93).

 

With this little trick, you will be able to use regular expressions almost anywhere

...and also be able to use bind variables in almost the whole PPM configuration, except Request Rules.

0 Likes
Absent Member.. AbdulMajeed_1 Absent Member..
Absent Member..

Re: Parse multi select filter values to Excel report

Hi Alex,

 

Thank you for the input. I did replace the query filter part with reg expression and i could pass the multiple values successfully. Now report works fine for any filter values but when it is NULL, it throws error. I did try to bypass null using nvl function but vain. Any thoughts would be helpful?

 

(SELECT trim(regexp_substr(str, \\'[^#@#]+\\', 1, level)) str from (select nvl(?,0) str from dual) t CONNECT BY instr(str, \\'#@#\\', 1, level - 1) > 0)

 

Thank You.

 

Regards,

Abdul

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Parse multi select filter values to Excel report

Hi Abdul,

 

I can see two ways to go around your problem:

 

1) Use a jt:if tag in order to have two different cells (i.e. SQL queries) depending on whether the value is NULL or not ( http://jett.sourceforge.net/tags/if.html )

 

2) Put a ${{ }} code block anywhere before the cell with your SQL that will replace the NULL value to whatever value will work (for example, and empty string). You can write the following code block (the block will be replaced by an empty string):

${{ if (FILTER_VALUE == null) { FILTER_VALUE = ''; } return ''; }}

You can find more information about the syntax of the scripting language to use in a ${{ }} block here: https://commons.apache.org/proper/commons-jexl/reference/syntax.html .

 

Thanks,

Etienne.

Contributor.. Shiwan_Agrawal Contributor..
Contributor..

Re: Parse multi select filter values to Excel report

Hello Abdul

I would suggest you to create a view and then use it in your excel report. Lets say view is TEST_V and filters that you are passing in your report are Start Month, End Month, Project Name and Project Manager. Here Project Name and Project Manager are MULTISELECT.

${{
   params = new("java.util.ArrayList"); 
 
  sql = "SELECT *  FROM TEST_V  ";
  sql = sql.concat("   WHERE Month >=  (?)  AND  Month <= (?) ");
  params.add(STA_ST_FM);
  params.add(STA_END_FM);
if (VSTA_PROJECT_NAME != null) {
   sql = sql.concat(" AND (  ");
   for(project :  utils.splitList(VSTA_PROJECT_NAME)){
              if( project !=  null){
                  sql = sql.concat(" project_name = (?)  OR ");
                  params.add(project);
              }
   }
   sql = sql.substring(0, sql.length() -3);
   sql = sql.concat("  )  ");
  }
  if (VSTA_PROJECT_MGR != null) {
    sql = sql.concat("   AND instr(project_managers, (?)) > 0 ");
    params.add(VSTA_PROJECT_MGR);
  }
  sql = sql.concat("  ORDER BY week, resource_name  ");
   results = ppmdb.execQuery(sql, params.toArray());

  return '';
}}

Let me know if it helps.

Regards,

Shiwan

0 Likes
asampaolesi
New Member.

Re: Parse multi select filter values to Excel report

Hi,

 

how to use this code "(SELECT trim(regexp_substr(str, \\'[^#@#]+\\', 1, level)) str from (select nvl(?,0) str from dual) t CONNECT BY instr(str, \\'#@#\\', 1, level - 1) > 0)"

in your query for the excel report ?

thanks,

Alessandro

 

0 Likes
Erik Cole Acclaimed Contributor.
Acclaimed Contributor.

Re: Parse multi select filter values to Excel report

I'm trying to build a SQL statement for a report. I seem to face two issues, if anyone can help...


(1) Null checks on report parameters don't seem to work for me. So for example:

if (VAR != null) -- never evaluates to NULL when I pass no value for the parameter. But

if (VAR != '') -- seems to work okay...? 

(2) Trying to get this block to work:

if (STAFFING_PROFILE_NAME != '') {
   sql = sql.concat(" AND (  ");
   for(spname :  utils.splitList(STAFFING_PROFILE_NAME)){
              if( spname !=  ''){
                  sql = sql.concat(" STAFFING_PROFILE_NAME = (?)  OR ");
                  params.add(spname);
              }
   }
   sql = sql.substring(0, sql.length() -3);
   sql = sql.concat("  )  ");
  }


When my parameter passes a single value, it works. When I pass multiple values, I get no result from the query.

Full code:

${{ params = new("java.util.ArrayList"); 
  sql = "SELECT period MONTH, Full_NAME USER_NAME, STAFFING_PROFILE_NAME STAFFING_PROFILE_NAME, resource_pool_name RESOURCE_POOL, FTE FTE, ROLE_NAME ROLE_NAME, VISIBLE_USER_DATA3 FROM MY_DB_VIEW where 1=1 and period in (select to_char(start_date,'yyyy-mm') from ppm_fiscal_periods where period_type = 4 and start_date between trunc(ADD_MONTHS((LAST_DAY(SYSDATE)+1),-1)) and trunc(ADD_MONTHS((LAST_DAY(SYSDATE)+1),22)))";
  if (LINKED_PROJ != '') {
    sql = sql.concat(" AND instr(user_data3, (?)) > 0 ");
    params.add(LINKED_PROJ);
  }
if (STAFFING_PROFILE_NAME != '') {
   sql = sql.concat(" AND (  ");
   for(spname :  utils.splitList(STAFFING_PROFILE_NAME)){
              if( spname !=  ''){
                  sql = sql.concat(" STAFFING_PROFILE_NAME = (?)  OR ");
                  params.add(spname);
              }
   }
   sql = sql.substring(0, sql.length() -3);
   sql = sql.concat("  )  ");
  }
  sql = sql.concat(" and user_data3 is not null ");
  sql = sql.concat("  ORDER BY 3,4,6,2,1 ");
  results = ppmdb.execQuery(sql, params.toArray());
return '';}}

 

I guess I need help on this utils.splitList (is that a javascript function?).

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Parse multi select filter values to Excel report

Hi Eric,

No, "utils.splitList(...)" is not a javascript function, it's a Java function that we made available to the scripting code used in Excel templates. 

FYI, the Scripting language used there is JEXL (https://commons.apache.org/proper/commons-jexl/reference/syntax.html).

This "utils" object extends Apache commons StringUtils class. So it means you can use all of its methods on this "utils" object: https://commons.apache.org/proper/commons-lang/javadocs/api-2.4/org/apache/commons/lang/StringUtils.html 

In addition to these methods, we've added an extra method called "splitList" that takes a multi-value (or single value) field value as input, and returns a List<String>, with all values broken down. 

 

Your example code looks correct to me. To understand what's wrong, just ouput your SQL in a cell with:

${ sql }

You should also output the parameters submitted to the sql. For this, you can use the Apache StringUtils method "join" available in the "utils" object:

${ utils.join(params, ";") }

Then try running the SQL with the parameters, and hopefully that should clear up why it's not returning any result.

Thanks,

Etienne.

0 Likes
Erik Cole Acclaimed Contributor.
Acclaimed Contributor.

Re: Parse multi select filter values to Excel report

Just to update anyone who is interested. The reason things were not working for me is because I was explicitly passing the parameters in my report type's command string like you would normally do. Apparently, you do not do this with the Excel reports. When I removed this, it works.

Would be nice to see this whole thing documented more completely...

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Parse multi select filter values to Excel report

Hi Eric,

Could you provide more details on how you did fix that? How did you pass the parameters in the report type command (not working), and how did you ended up passing them for it to work?

I'll get that properly documented if I can understand what was going wrong.

Cheers,

Etienne.

 

0 Likes
Erik Cole Acclaimed Contributor.
Acclaimed Contributor.

Re: Parse multi select filter values to Excel report

Hi,

Basically there is no documentation of the PPM-side config necessary for the Excel reports. You have to import the examples, I guess, but I don't have access to them where I am.

When you are creating a SqlPlus-based report, you explicitly pass the filters/parameters to the script in the command, like:

ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @./scripts/my_script '[AS.REPORT_DIR]' '[RP.FILENAME]' '[P.REPORT_TITLE]' '[P.MY_PARAM1]' '[P.MY_PARAM2]'

But the Excel commands do not need this...

Works:

ksc_run_excel_report MyReport.xlsx
REPORT_ID=[RP.REPORT_SUBMISSION_ID]
ksc_end_report_parameters

Doesn't work:

ksc_run_excel_report MyReport.xlsx
REPORT_ID=[RP.REPORT_SUBMISSION_ID]
PARAM_A = '[P.MY_PARAM1]'
PARAM_B = '[P.MY_PARAM2]'
ksc_end_report_parameters

Because if the user leaves MY_PARAM1 blank, it evaluates to '' instead of NULL.

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Parse multi select filter values to Excel report

Hi Eric,

To my understanding, this is already documented in the Excel Report cookbook, which is the official documentation of PPM Excel Reports. If you have access to this forum, you'll likely have access to it online: https://admhelp.microfocus.com/ppm/en/9.42/Help/Content/IntroTopics/ExcelReports.htm

If you're unsure about what parameters are available in your template, please refer to the chapter "Debug Mode: How to View What Data is Passed to Your Excel Template": https://admhelp.microfocus.com/ppm/en/9.42/Help/Content/RG/ExcelReports/09_DebugMode.htm

If you're looking for the cookbook Sample files, they've moved to their new Micro Focus home: https://marketplace.microfocus.com/appdelivery/content/ppm-excel-reports-cookbook-and-sample-files 

Cheers,

Etienne.

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.