Dynamic Report parameter

[shreya alle]

[code=oracle]
select <calculation for week> AS data,'Week' AS week_month from all_objects
UNION ALL
select <calculation of month>,'Month' from all_objects
[/code]
And in WHERE clause add where condition:
WHERE data<= .....
AND week_month = :$FLEX$.firstva lueset

We cannot use alias name "week_month " in the where clause like this.
"week_month = :$FLEX$.firstva lueset".


I found the following link expalining abt the value sets. In that it is given like we can not use $FLEX$ in table name, value and ID fields.

http://www.oracleappsh ub.com/aol/aol-valueset-a-beginner-guide/

[amitpatel66]

We cannot use alias name "week_month " in the where clause like this.
"week_month = :$FLEX$.firstva lueset".


I found the following link expalining abt the value sets. In that it is given like we can not use $FLEX$ in table name, value and ID fields.

http://www.oracleappshub.com/aol/aol...eginner-guide/

Did you try giving a SQL statement in the default value of the concurrent program parameters screen for this parameter?

[shreya alle]

No. I didn't do that...

[amitpatel66]

No. I didn't do that...

Why dont you try that and check if the default value is correctly set for second parameter based on the value of the first parameter selected.

[shreya alle]

Why dont you try that and check if the default value is correctly set for second parameter based on the value of the first parameter selected.

What about the valuset to be given there??

[amitpatel66]

What about the valuset to be given there??

Any value set that is of type table and can hold varchar2(50) as i do not think that the LOV for your query will be more than 10 characters....

[shreya alle]

Any value set that is of type table and can hold varchar2(50) as i do not think that the LOV for your query will be more than 10 characters....

HI Amit ...

Thanks fro your guidence. Even by using default values, it was throwing some error. So i just used the followind code and its working fine.

[code=oracle]

--Table name : all_objects

value : Decode (:$FLEX$.xx_per iod_type,'week' , (TO_CHAR (sysdate+1- ((rownum)*7 ),'WW-YYYY') ),'month',to_ch ar( add_months( sysdate,- rownum+1),'mont h-yyyy') )
ID : Decode (:$FLEX$.xx_per iod_type,'week' , (TO_CHAR (sysdate+1- ((rownum) *7 ),'WW-YYYY') ),'month',to_ch ar( add_months (sysdate,-rownum+1),'mont h-yyyy') )

--and in where clause

where rownum < Decode (:$FLEX$.xx_per iod_type,'week' ,261,'month',61 )
order by 1

[/code]



This is working fine... :)

Thank u...

Thanks & Regards

[amitpatel66]

HI Amit ...

Thanks fro your guidence. Even by using default values, it was throwing some error. So i just used the followind code and its working fine.

[code=oracle]

--Table name : all_objects

value : Decode (:$FLEX$.xx_per iod_type,'week' , (TO_CHAR (sysdate+1- ((rownum)*7 ),'WW-YYYY') ),'month',to_ch ar( add_months( sysdate,- rownum+1),'mont h-yyyy') )
ID : Decode (:$FLEX$.xx_per iod_type,'week' , (TO_CHAR (sysdate+1- ((rownum) *7 ),'WW-YYYY') ),'month',to_ch ar( add_months (sysdate,-rownum+1),'mont h-yyyy') )

--and in where clause

where rownum < Decode (:$FLEX$.xx_per iod_type,'week' ,261,'month',61 )
order by 1

[/code]



This is working fine... :)

Thank u...

Thanks & Regards

That's Fantastic. So the problem was that ID value also needs to be specified with the same syntax that we use in table value field is it?

[shreya alle]

That's Fantastic. So the problem was that ID value also needs to be specified with the same syntax that we use in table value field is it?

Yes and one more thing is , we can't use $FLEX$ in SELECT statement.

[amitpatel66]

Yes and one more thing is , we can't use $FLEX$ in SELECT statement.

Ok. So the problem is solved. Great!!

Shreya,

Please make sure you make use of [CODE] TAGS when ever you post any source code in this forum. CODE TAGS increases the readability and clarity of the source code for others to understand much better. When you click on POST REPLY or REPLY BUtton of the thread, it opens a new window where you can enter your reply. At the right hand side of this window you can check guidelines that will explain you about how to use [CODE] tags