nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Optional Date Range Parameters

birtnewbie2014

Hi All! Forgive me if this is already posted somewhere but I searched and searched and couldn't find the answer to what I'm trying to do. What I have is a simple date range (From Date and To Date). In the select portion of the SQL I have defined the date as to_char(doc_dt, 'MM/DD/YYYY') and have it set up in the where clause as "to_char(doc_dt, 'MM/DD/YYYY') between ? and ?". I did the to_char here because I want the user to be able to enter their desired date into the parameter field in that format. (If there's a better way to do that, please let me know!) So the issue I am having is that both of those dates are optional and if the user decides to leave them both blank then I want to return all of the data. Right now, if I leave them both blank I don't get any data back. Any ideas on how to handle this?? Thanks so much for any advice you can provide!

Find more posts tagged with

Comments

mwilliams

You could leave them both as not required. Then, in your dataset query, don't do the where date between ? and ?, just do the basic query. In the beforeOpen script of your dataset, you can check your parameters for values. If they are blank/null, you would leave the query as is. If a value was put in, you could add to your query from the script with something like: this.queryText = this.queryText + " where date between '" + params["from"] + "' and '" params["to"] + "'"; Hope this helps. Let me know if I'm misunderstanding your issue.

birtnewbie2014

Ok, I've removed the where date between ? and ? statement from my query. I'm not sure how to check the parameters for values or where to add the this.queryText line. I've only been using this tool for about a month so I'm not familiar with all of the ins and outs of it yet. Thanks for your help!

pricher

Hi,
 
To modify the query before it is run, you need to write a few lines of JavaScript code in the beforeOpen method of the DataSet. To get to the beforeOpen method: 1) select your data set in the Data Explorer; 2) click on the Script tab at the bottom of your report design, and 3) select beforeOpen from the drop-down list above the script editor.
 
In the attached example, you can see one example of how to modify the query. Here, I test to see if the parameter values are null. If they are not, I replace a dummy where clause ("where 1=1") with a where clause that will filter by date.
 
Hope this helps,
 
P.

birtnewbie2014

Good Morning P!
 
Thanks for your help. Unfortunately, I cannot open the file you attached as I'm only allowed to use version 2.5.2. Can you just copy and paste the example that you were trying to show me? Or do you know of a way to open a newer file in 2.5.2? 
 
Thanks so much!

pricher

Hi,
 
Here's the script added to the beforeOpen method of the data set:
 
if (params["dateFrom"].value != null && params["dateTo"].value != null) { 
this.queryText = this.queryText.replace("where 1=1", "where o.orderdate between '" + params["dateFrom"].value + "' and '" + params["dateTo"].value + "'") 
}
 
And here's the query:
 
select count(o.ordernumber) as cnt 
, o.orderdate 
from orders o 
where 1=1 
group by o.orderdate
 
Hope this helps,
 
P.

birtnewbie2014

Bad news....I tried the above suggestion and it's still not working. I'm still having the problem where if I leave both parameters (From Date and To Date) blank, it doesn't return any data. I want it to return everything when those parameters are blank. Neither are marked as required. Any other suggestions/ideas??

pricher

Can you post your design?
 
P.

mwilliams

This design is made in 2.5.2 and works whether both, one, or neither of the parameters are entered. Hope it helps.

birtnewbie2014

Thank you so much for your help. I have added the code in the beforeOpen script but I keep getting an error.
 
Table (id=9): An exception occured during procession. Please see following message for details:
Failed to prepare the query execution for the date set: Test Date
Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended.... I would copy the whole error but I am unable to since it's on a classified machine. 
 
I know this is directly related to the code in the beforeOpen script since the error goes away when I remove it. 
 
Here is what I have in the beforeOpen script -
 
if (params["From Date"].value != null && params["To Date"].value != null){
this.queryText = this.queryText + " where doc_date between '" + params["From Date"] + "' and '" + params["To Date"] + "'";
}
else if(params["To Date"].value != null){
this.queryText = this.queryText + " where doc_date < '" + params["To Date"] + "'";
}
else if(params["From Date"].value != null){
this.queryText = this.queryText + " where doc_date > '" + params["From Date"] + "'";
}
else{
}
 
Here is what you have in the beforeOpen script - 
 
if (params["sDate"].value != null && params["eDate"].value != null){ 
 this.queryText = this.queryText + " where orderdate between '" + params["sDate"] + "' and '" + params["eDate"] + "'"; 
} 
else if(params["eDate"].value != null){ 
 this.queryText = this.queryText + " where orderdate < '" + params["eDate"] + "'"; 
} 
else if(params["sDate"].value != null){ 
 this.queryText = this.queryText + " where orderdate > '" + params["sDate"] + "'"; 
} 
else{ 
}
 
Am I missing something??

mwilliams

Does the report that I attached work for you though? In your report, what is your original query?

birtnewbie2014

Yes, I was able to run the sample report you provided. 
 
SELECT
d.doc_date,
d.doc_num,
l.status,
substr(ID,12,4) FY,
substr(DEPT,7,2) DEPT,
substr(Code,11,4) CODE,
substr(ID,7,2) AcctNum,
l.amt,
l.org_id,
l.comments
FROM
doc_tbl d,
doc_ln l,
corp_tbl f
WHERE d.uid = l.line_id
and f.uid = l.fid
and substr(ID,7,2) like ? (I have an optional AcctNum parameter)
and substr(ID,12,4) like ? (I have an optional FY parameter)
and substr(DEPT,7,2) like ? (I have an optional DEPT parameter)
and l.status like ? (I have an optional Status parameter)
ORDER BY d.doc_num

mwilliams

Okay. Remove the ORDER BY d.doc_num from your original query and add that in your beforeOpen script (see below). Also, you already have a where statement started, so you don't need to say where again. Try this: if (params["From Date"].value != null && params["To Date"].value != null){ this.queryText = this.queryText + " and doc_date between '" + params["From Date"] + "' and '" + params["To Date"] + "' ORDER BY d.doc_num"; } else if(params["To Date"].value != null){ this.queryText = this.queryText + " and doc_date < '" + params["To Date"] + "' ORDER BY d.doc_num"; } else if(params["From Date"].value != null){ this.queryText = this.queryText + " and doc_date > '" + params["From Date"] + "' ORDER BY d.doc_num"; } else{ this.queryText = this.queryText + " ORDER BY d.doc_num" }

birtnewbie2014

So close! I'm still having the issue of if I leave either date parameters blank I don't get any data back (instead of selecting the radio button for Null Value, I select the radio button beside the input field and do not enter a date). The reason I need to do this is b/c the users will be running this report in another program (Momentum) and they need to be able to leave these fields completely blank.
 
Thanks!!

pricher

Be careful: a null value is not the same as an empty value!
 
You will need to test for both scenarios in your script. The following IF statement should work:
 
if (params["From Date"].value != null && params["To Date"].value != null) && (params["From Date"].value != "" && params["To Date"].value != "")
 
Hope this helps,
 
P.

birtnewbie2014

That did it!!! Thanks so much for all of your help guys!! I'm sure I'll be back...

mwilliams

We're always glad to help. Definitely let us know whenever you have questions.