Hi @DevBob,

I believe you are referring to SQL quick reports. 

If so, you can do this by using Query flags.

This allows an easy control of end user input and allows or disallows certain combinations. You need to provide the query flags inside open and close square braces in between the ampersand sign and the parameter name;

e.g. &mmcsbl]PARAMETER_NAME

All flags have opposites specified by a '-' (minus). For example the opposite of 'Single' is of course multiple values (separated by ';') which in turn corresponds to the functionality in the Query Dialog. The default value for SQL Quick Reports is i-CS-L].

• M=Mandatory
• C=Custom (simplified or 'values only')
• S=Single flag
• B=Allow Between expressions
• L=Allow wildcards ('%' and '_')

See the example below;

SELECT * FROM fnd_user WHERE identity LIKE '&m-CS-L]Fnd_User'

This is how you can set a default value;

You can get the Quick Report parameter dialog to show default values such as enumerations for SQL type Quick Reports. For this you need to specify the view name along with the column name for the parameter prompt e.g. &VIEW_NAME.COLUMN_NAME. With the use of this you will get the default values and the translated prompt name for the parameter. The following will show the enumerated values for the "Customer Category" and will have the translated parameter prompt.

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

You can combine this with Query Flags as well.

SELECT * from customer_info where customer_category_db LIKE '&a-CS--]CUSTOMER_INFO.CUSTOMER_CATEGORY_DB'

This information can be found in the F1 development guide;

Foundation1 / Development Guide / Business report and Analysis Specific Development / Adhoc Reporting / Development of Quick reports 

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:

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.

That semicolon is in IFS land, not Oracle land. Oracle doesn’t honor semicolon delimiting.

SELECT * FROM DUAL WHERE dummy = 'X';

-- returns one record

SELECT * FROM DUAL WHERE dummy LIKE 'X%';

-- returns one record

SELECT * FROM DUAL WHERE dummy LIKE 'X;Y';

-- returns no records

The traditional way to parse multiple parameters has been to use REPORT_SYS.PARSE_PARAMETER.

AND report_sys.parse_parameter(ma.company, '&&-C--L]Company_Equal')

However, this disregards all your database indexes, forcing full scans. (This function can’t be used on a function-based index, either.)

I came up with a better solution by breaking apart the expression into unique records, then joining those distinct records. This does use any indexes you might have.

 INNER JOIN (SELECT REGEXP_SUBSTR(d__.str, 'd^;]+', 1, LEVEL) AS val_

               FROM (SELECT '&R-C--L]Company_Equal' AS str FROM DUAL) d__

             CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1) filter_

    ON ma.company LIKE filter_.val_ /* ON clause is actually a business filter */
 

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?

I'd hate to see this "fixed" as a bug, though. You'd have to intercept and rewrite the SQL on its way to the database, and it would likely break the most clever quick reports. (We use the WITH clause, pipelined functions, window functions, etc. You would end up rewriting the Oracle parser to not break any of our reports.)

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 for the replies, however, we don’t have time for that. I’m surprised there’s such a limitation; building a front-end UI over an ORACLE SQL shouldn’t restrict functionality, but I do understand the complexities.

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

Creative workaround- thanks!

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','%'))

perfect! That’s doable.

Works! Thanks everyone for your input!

thanks everyone for the useful input!

this condition allows to enter several values, but unfortunately does not find the exact values:

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

I entered: 

191-9232-1KB; 191-9232-1KC

Result:

I tried various options but was not able to make it work that only the exact values will be found (here: 191-9232-1 should not pop-up)

Any ideas? 

Thank you!

@IFSmattis,

Let try this.

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

Thanks for the input, but unfortunately it does not work. It only fetches the first value I entered:

I tried three variations:

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

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

(';&m-C--L]catalog_no' LIKE '%;' || aspd.catalog_no || ';%')

@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','%'))

Hello,

I’m trying to prepare a quick report with parameter has default values. (IFS10) 

Like given example in F1 development guide;

“

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

”  

 I have used “ where t.project_id LIKE '&PROJECT_LOV.PROJECT_ID' ” but this is not working. 

Does anyone know why ? What is difference between them ? 

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’,’%’) 

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.

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’

Hi @Wiktor,

I have checked and tried to use other views with comparing your ‘&inventory_part.contract’ example .

It seems we should use Parent Key columns. (FLAGS= P---L )

ACTIVITY doesn’t works. But PROJECT_ACTIVITY works. 

Thank you so much.

Best regards.

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.

Thank you. It worked perfectly within Quick report’s SQL query - IFS DEV10.

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