Quick Report Parameter Questions

@durette nice approach! 

I used your regex and placed it in a query like this. 
Thanks again

SELECT order_no
  FROM IFSAPP.SHOP_ORD

 where order_no IN
       (SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_
          FROM (SELECT '&[-C--L]Param1' AS str FROM DUAL) d__
        CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1)

CREATE OR REPLACE PACKAGE C_QUICK_REPORT_API IS
 TYPE ret_table_type IS RECORD(year VARCHAR2(4),
 contract site.contract%TYPE,
 ... );
 TYPE ret_table IS TABLE OF ret_table_type;
 FUNCTION quick_rep(input IN VARCHAR2 ) RETURN C_QUICK_REPORT_API.ret_table PIPELINED;
END C_QUICK_REPORT_API;
/

CREATE OR REPLACE PACKAGE BODY C_QUICK_REPORT_API IS
 FUNCTION quick_rep(input IN VARCHAR2 ) RETURN C_QUICK_REPORT_API.ret_table PIPELINED IS
 qr_table C_QUICK_REPORT_API.ret_table_type; 
 CURSOR get_rep_data IS
 < actual SQL >;
 BEGIN
 FOR c1 IN get_rep_data LOOP 
 qr_table.year := c1.year; 
 qr_table.contract  := c1.contract; 
 .. .       
 PIPE ROW(qr_table);       
 qr_table := NULL;
 END LOOP;   
 RETURN;
 END quick_rep;
END C_QUICK_REPORT_API;
/

Since this thread came back to life recently, I finally played around with pipelined record sets and using them on Quick Report -- it does indeed work, so thank you for that!

The Quick Report runs and displays data. However, “Export To Excel” does not work.  :(   The result comes up with the dreaded “insufficient privileges” error that we also get whenever we do anything odd in a query, like using date range parameters or using “WITH”:

Anyone have any new ideas on how to overcome this issue when using “Export To Excel” on Quick Reports?

In any case, thanks for the idea (that I finally got around to playing with...) Pipelining is nifty!

Thanks,

Joe Kaufman

Hi @cilik ,

You right, we should use parent key columns (P) + list (L).

I thought you have already known that the project activity will give you all activities in all projects and you will be gotten a ton of data in LOV.

I don’t know what is purpose of your report. For my opinion, if users would need to run the report after the status approved, I would recommend you to use view “ACCOUNTING_PROJECT_LEDGER” instead of “PROJECT_ACTIVITY”.  However, the you have make sure you granted this view to users are not accounting too.

Hello @kenny.sjbk ,  

 

My purpose is to make list for report parameter.

I have tried CUSTOMER_INFO example and it works.

 

 

But when i have tried a simple query as below it doesn’t work. Doesn’t show any project_id in list of project_id parameter.

 

 

Is that clearer for you now ? 

Thank you.

Hi

It will work if you point to some “foreign key” field, not real LOV field.

So, instead of using ‘&site.contract’ i’m using ‘&inventory_part.contract’

In your case I’d try ‘&activity.project_id’

SELECT * from customer_info where customer_category_db = '&CUSTOMER_INFO.CUSTOMER_CATEGORY_DB'

I can’t tell you why because I didn’t see whole statement in PROJECT_LOV statement and I do not think you CUSTOMER_INFO statement is work because of parameter incorrect. 

In my opinion, you should use select t.* from project_lov t where t.project_id like nvl(‘&Project_ID’,’%’) 

@IFSmattis, you were missing a semi-colon. You can copy my statement below.

WHERE (';&[-C--L]catalog_no;' LIKE '%;' || aspd.catalog_no || ';%' OR aspd.catalog_no LIKE NVL('&[-C--L]catalog_no','%'))

@IFSmattis,

Let try this.

WHERE ( ';&VAR1;' LIKE '%;' || PART_NO || ';%' ) 

Works! Thanks everyone for your input!

I have used the statement below.

SELECT CUSTOMER_ID, NAME
FROM &AO.CUSTOMER_INFO
WHERE ( '&[-C--L]VAR1' LIKE '%' || CUSTOMER_ID || '%' OR CUSTOMER_ID LIKE NVL('&[-C--L]VAR1','%'))

Given your constraint, here’s a possible simpler approach that might help.

For our large number of Crystal based quick reports we created multiple fields to allow users to enter multiple values for a field.  So for example, if they were to run a report for 3 Sites we created 3 (and perhaps a couple more just in case) Site fields so that multiple values could be used.

In our case it was the upgrade from Apps7.5 to Apps9 that triggered the change… in 7.5 you could enter multiple values into a single with no issue.

May or may not be helpful to your situation (SQL Query reports vs Crystal) but I thought I’d put it out there...

Nick

For complex quick reports you can define the logic inside a PLSQL function which return  pipelined table of SQL result set so quick report SQL is simple.

below stub explains how we use it.

Quick report:

select * FROM  TABLE(C_Quick_Report_API.quick_rep('&input')) a

Business logic

CREATE OR REPLACE PACKAGE C_QUICK_REPORT_API IS
 TYPE ret_table_type IS RECORD(year VARCHAR2(4),
 contract site.contract%TYPE,
 ... );
 TYPE ret_table IS TABLE OF ret_table_type;
 FUNCTION quick_rep(input IN VARCHAR2 ) RETURN C_QUICK_REPORT_API.ret_table PIPELINED;
END C_QUICK_REPORT_API;
/

CREATE OR REPLACE PACKAGE BODY C_QUICK_REPORT_API IS
 FUNCTION quick_rep(input IN VARCHAR2 ) RETURN C_QUICK_REPORT_API.ret_table PIPELINED IS
 qr_table C_QUICK_REPORT_API.ret_table_type; 
 CURSOR get_rep_data IS
 < actual SQL >;
 BEGIN
 FOR c1 IN get_rep_data LOOP 
 qr_table.year := c1.year; 
 qr_table.contract  := c1.contract; 
 .. .       
 PIPE ROW(qr_table);       
 qr_table := NULL;
 END LOOP;   
 RETURN;
 END quick_rep;
END C_QUICK_REPORT_API;
/

This solves the limitation of quick reports which does not start with "SELECT" clause and headache with version controlling of SQLs.

Hope it helps someone!

Thanks!

I was able to get it to work, however when I try multiple values it comes back with ‘No Data’.

 

 

 

 

Here’s the report without entering a Company:

 

Hi @DevBob,

; (semicolon) should work for multiple values. It looks like a bug to me. Could you please create an IFS case for that?

Also, by default values, I meant the ability to default a parm when the View Report window appears.

I do like the ability to add a list of values to a parm! I can see using that a lot.