Report # 3 Questions

Hi Mike,

I have a request for Input parameters where the values are a range.

Eg: My Input Parameter Display Text will show the following

0 - 5
6 -10
> 10
ALL

I should interpret them as such. But How can I "say" the range in the Value Box. Is there a way where I can do that.

This way I can use it in my query as is...like say

Age between 0 and 5.

Thanks
Nurani Sivakumar

Find more posts tagged with

Comments

mwilliams

Hi Nurani,

It may work best to do your parameters like the following:

value | display text
0 | 0 - 5
6 | 6 - 10
etc...

And then since you know the ranges you want, you can check the value of the parameter and edit your query to work with the range you want.

i.e.
if (param["range"] == 0){
this.queryText = this.queryText + " where dataField between 0 and 5";
}
etc...

nuraniuscc

Ok Mike,

I did just what you said and It makes sense. But I have no clue why I am getting this 'undefined" error. It seems like whenever there is a JOIN I see that. (Similar issue raised for Report # 2)

So here it is. Maybe you can answer the other one and this will be taken care too.

select CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR as CYCLE, DATACENTER, MEMBER, TIMESREJECTED, TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') as AGING, BAN, EXPERTKEY || '/' || APPLICATION_ID as ERRAPPID from TBLRMREJECTLIST A, TBLTWRESOURCEDESC B where A.MEMBER = B.RESOURCENAME and A.FIXEDDATE is NULL and B.SUNHOURSAVAIL = 0 and B.HOLHOURSAVAIL = 0 and B.SATHOURSAVAIL = 0undefined and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 6 and 10 and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') > 10 and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY')>= 0Group By CYCLE_RUN_MONTH, CYCLE_CODE, CYCLE_RUN_YEAR, DATACENTER, MEMBER,TIMESREJECTED, TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY'), BAN, EXPERTKEY, APPLICATION_IDOrder by AGING Desc, CYCLE, DATACENTER, MEMBER, BAN, EXPERTKEY

mwilliams

Nurani,

Can you email me the script you use to build this query?

nuraniuscc

Script for Report # 3

if(params["Aging Range"].value = 0){
if (MySQLString == ""){
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 0 and 5";
}
else
{
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 0 and 5";
}}

if(params["Aging Range"].value = 6){
if (MySQLString == ""){
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 6 and 10";
}
else
{
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 6 and 10";
}}

if(params["Aging Range"].value = 11){
if (MySQLString == ""){
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') > 10";
}
else
{
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') > 10";
}}

if(params["Aging Range"].value = 99){
if (MySQLString == ""){
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') >= 0";
}
else
{
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY')>= 0";
}}

{
this.queryText = this.queryText + MySQLString + "Group By CYCLE_RUN_MONTH, CYCLE_CODE, CYCLE_RUN_YEAR, DATACENTER, MEMBER,TIMESREJECTED, TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY'), BAN, EXPERTKEY, APPLICATION_ID"
+ "Order by AGING Desc, CYCLE, DATACENTER, MEMBER, BAN, EXPERTKEY";
}

SQLText = "";
SQLText = this.queryText;

mwilliams

Nurani,

It could be because of this:

if (MySQLString == ""){
this.MySQLString = this.MySQLString + " and TO_DATE(CURRENT_DATE, 'DD-MON-YY') - TO_DATE(ORIGINALDATEREJECTED, 'DD-MON-YY') BETWEEN 0 and 5";
}

Just use MySQLString = MySQLString + ..... rather than this.MySQLString. That could be your issue. Try it and let me know.

note: "this", in this situation, is a context call to your dataSet. You use it to access this.queryText and other properties, etc. of the dataSet, not for your variables you create.

Hope this helps.

tdtyson2

Nurani,
Please correct me if I am wrong, but it looks like you are setting the 'Aging Range' parameter with each if. If that is the case, then each statement would be true as it processes.

Please let me know if this is the case.

Troy