how to configure excel report query filter using other excel report query with in same excel

[ShravanK]

Hi All,

We have requirement where we need to configure 2 excel report queries where we need to pass result of one column from one excel report query to another excel report query with in same excel.I know that we can use filter concept as below given by guide But i need to pass parameter using output of other query in same excel

Below is from 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.

Could you please let me know as soon possible how can I handle this.

[Etienne_Canaud]

Hi,

Check the attached template, on sheet "Run SQL with Parameters" for an example. 

And don't forget that you can run SQLs (or do any kind of scripting), in a ${{ }} block. So you can run your SQL in a query block, assign the result to a variable, and use this variable later to fill in some excel cells as well as read some data from it to insert as the parameter of another SQL query.

If you need more help, I think that it'll be easier if you can share your excel template and the SQL queries you want to use.

Thanks,

Etienne.

[Bhawna Virmani]

Hello,

I am facing same issue , I am trying to use {$pct1.request_id} received from one query to another Query to get his table component data.
can you explain how can I save it to the parameter and use it, as I have tried all and could no do.

<jt:forEach items="${ppmdb.execQuery('select * from (select * from (SELECT 1 seq,kte.parameter8||\\'-\\'||kte.visible_parameter41 MILESTONE_DESCRIPTION, to_char(TO_DATE(kte.visible_parameter5,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')BASELINE_DATE,kte.visible_parameter6 LEAD,to_char(TO_DATE(kte.visible_parameter1,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')MILESTONE_DATE, decode(kte.visible_parameter2 ,\\'Green\\',\\'=UNICHAR(127318)\\',\\'Yellow\\',\\'=UNICHAR(127312)\\',\\'Red\\',\\'=UNICHAR(127329)\\',\\'Black\\',\\'=UNICHAR(127313)\\',\\'White\\',\\'=UNICHAR(127314)\\') MILESTONE_RAG, kte.visible_parameter3 MILESTONE_LEVEL, DECODE(kte.visible_parameter2,\\'No Milestones Provided\\',klu.description) MILESTONE_RAG_DESCRIPTIO, DECODE(kte.visible_parameter2,NULL,\\'No Milestones Provided\\',kte.visible_parameter4) MILESTONE_CATEGORY FROM kcrt_request_details rdb2,kcrt_table_entries kte,knta_parameter_set_contexts kpsc,knta_lookups klu whERE 1=1 AND rdb2.request_id =${pct1.request_id} AND rdb2.batch_number = 2 AND rdb2.request_id = kte.request_id(+) AND kte.parameter_set_context_id(+) = kpsc.parameter_set_context_id AND kpsc.mdl_view_name_root = \\'MERCER_MILESTONE\\' AND klu.lookup_type = \\'Merc_Proj_Status_New\\' AND klu.lookup_code = kte.parameter2 union SELECT 2 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 3 seq,NULL, NULL, NULL,NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 4 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 5 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 6 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 7 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 8 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 9 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 10 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 11 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual) order by seq)where rownum<=6 ')}" var="pct2">${pct2.SEQ}

[Bhawna Virmani]

Hello,

I am facing same issue , I am trying to use {$pct1.request_id} received from one query to another Query to get his table component data.

can you explain how can I save it to the parameter and use it, as I have tried all and could no do.

<jt:forEach items="${ppmdb.execQuery('select * from (select * from (SELECT  1 seq,kte.parameter8||\\'-\\'||kte.visible_parameter41 MILESTONE_DESCRIPTION, to_char(TO_DATE(kte.visible_parameter5,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')BASELINE_DATE,kte.visible_parameter6 LEAD,to_char(TO_DATE(kte.visible_parameter1,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')MILESTONE_DATE, decode(kte.visible_parameter2 ,\\'Green\\',\\'=UNICHAR(127318)\\',\\'Yellow\\',\\'=UNICHAR(127312)\\',\\'Red\\',\\'=UNICHAR(127329)\\',\\'Black\\',\\'=UNICHAR(127313)\\',\\'White\\',\\'=UNICHAR(127314)\\') MILESTONE_RAG, kte.visible_parameter3 MILESTONE_LEVEL, DECODE(kte.visible_parameter2,\\'No Milestones Provided\\',klu.description) MILESTONE_RAG_DESCRIPTIO, DECODE(kte.visible_parameter2,NULL,\\'No Milestones Provided\\',kte.visible_parameter4) MILESTONE_CATEGORY FROM  kcrt_request_details rdb2,kcrt_table_entries kte,knta_parameter_set_contexts kpsc,knta_lookups klu whERE   1=1 AND rdb2.request_id =${pct1.request_id} AND  rdb2.batch_number = 2 AND rdb2.request_id = kte.request_id(+) AND kte.parameter_set_context_id(+) = kpsc.parameter_set_context_id AND kpsc.mdl_view_name_root = \\'MERCER_MILESTONE\\' AND  klu.lookup_type = \\'Merc_Proj_Status_New\\' AND klu.lookup_code = kte.parameter2 union SELECT  2 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  3 seq,NULL, NULL, NULL,NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  4 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  5 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  6 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  7 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  8 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  9 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  10 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual union SELECT  11 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM  dual) order by seq)where rownum<=6 ')}" var="pct2">${pct2.SEQ}

[Etienne_Canaud]

Hi Bhawna,

Everything is explained in PPM Help: https://admhelp.microfocus.com/ppm/en/9.60/Help/Content/RG/ExcelReports/0605_GetDataSQL.htm

You can pass parameters to the SQL query by inserting some question marks in the query, and passing parameters values as extra parmeters to the execQuery() method that will be inserted in order in the "?" placeholders within the SQL.

So in your SQL, replace ${pct1.request_id} by ?  , and pass an extra parameter pct1.request_id, like that:

<jt:forEach items="${ppmdb.execQuery('select * from (select * from (SELECT 1 seq,kte.parameter8||\\'-\\'||kte.visible_parameter41 MILESTONE_DESCRIPTION, to_char(TO_DATE(kte.visible_parameter5,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')BASELINE_DATE,kte.visible_parameter6 LEAD,to_char(TO_DATE(kte.visible_parameter1,\\'YYYY-MM-DD HH24:MI:SS\\'),\\'DD-Mon-YYYY\\')MILESTONE_DATE, decode(kte.visible_parameter2 ,\\'Green\\',\\'=UNICHAR(127318)\\',\\'Yellow\\',\\'=UNICHAR(127312)\\',\\'Red\\',\\'=UNICHAR(127329)\\',\\'Black\\',\\'=UNICHAR(127313)\\',\\'White\\',\\'=UNICHAR(127314)\\') MILESTONE_RAG, kte.visible_parameter3 MILESTONE_LEVEL, DECODE(kte.visible_parameter2,\\'No Milestones Provided\\',klu.description) MILESTONE_RAG_DESCRIPTIO, DECODE(kte.visible_parameter2,NULL,\\'No Milestones Provided\\',kte.visible_parameter4) MILESTONE_CATEGORY FROM kcrt_request_details rdb2,kcrt_table_entries kte,knta_parameter_set_contexts kpsc,knta_lookups klu whERE 1=1 AND rdb2.request_id =? AND rdb2.batch_number = 2 AND rdb2.request_id = kte.request_id(+) AND kte.parameter_set_context_id(+) = kpsc.parameter_set_context_id AND kpsc.mdl_view_name_root = \\'MERCER_MILESTONE\\' AND klu.lookup_type = \\'Merc_Proj_Status_New\\' AND klu.lookup_code = kte.parameter2 union SELECT 2 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 3 seq,NULL, NULL, NULL,NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 4 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 5 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 6 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 7 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 8 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 9 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 10 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual union SELECT 11 seq,NULL, NULL,NULL, NULL,NULL, NULL, NULL, NULL FROM dual) order by seq)where rownum<=6 ', pct1.request_id)}" var="pct2">${pct2.SEQ}

Cheers,

Etienne.