Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Parse multi select filter values to Excel report

Highlighted
AbdulMajeed_1
Super Collector

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

 

4 REPLIES
alex-h
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.

AbdulMajeed_1
Super Collector

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

Etienne_Canaud
HPE 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.

Shiwan_Agrawal
Super Collector

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

//Add this to "OnDomLoad" event