Eclipse Community Forums: BIRT » Date parameter not Working

Home » Archived » BIRT » Date parameter not Working(BIRT 2.3.2 Unable to pass year from date parameter in query)

Date parameter not Working [message #1058988]

Thu, 16 May 2013 03:00

Eclipse User

Hi All,
I am using BIRT 2.3.2. I have to pass my date parameter as string like 2013
and my query is as follows
var sqld= new String();
sqld= '2013' ;

// Add query to sqlText variable.
sqlText =" select pmnum,SITEID,(select description from locations where pm.location = locations.location) "
+ "as Site, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > sqld+'-07-01' and targstartdate "
+ "<'2013-08-01') as july "
+ " from pm where siteid = 'BEDFORD' "
+ "and status = 'ACTIVE' "
+ "and pfmanticipated > 0 "
+ "ORDER BY pmnum "

as when i am trying to put targetstartdate> sqld+'-07-01' it is not showing me any results but if i will hard code like targetstartdate> '2013'+'-07-01' it is pulling data properly. Can you guide me what i am doing wrong here. Cheers

Re: Date parameter not Working [message #1059121 is a reply to message #1058988]

Thu, 16 May 2013 16:28

Eclipse User

I'm not having a problem with this working with the sample database. Maybe I'm setting something up differently than you. Can you please recreate this issue with the sample database and attach the design? Thanks!

Re: Date parameter not Working [message #1059139 is a reply to message #1059121]

Thu, 16 May 2013 20:32

Eclipse User

Hi,
Thanks for your email. I actually tried a little bit different code and it worked and but this code has some issue

// Add query to sqlText variable.
sqlText =" select pmnum,SITEID,(select description from locations where pm.location = locations.location) "
+ "as Site, "
+ "(select wonum from workorder where "

+ "workorder.pmnum = pm.pmnum and targstartdate > '" + params["fromdate"] + "-07-01' and targstartdate "
+ "<'" + params["fromdate"] + "-08-01') as july "
+ " from pm where siteid = 'A' "
+ "and status = 'ACTIVE' "
+ "and pfmanticipated > 0 "
+ "ORDER BY pmnum "

maximoDataSet.setQuery(sqlText);

here in the above code i am passing fromdate as integer that is 2013 and its working. Now what is my actual problem i want to pass financial year as 2013/2014 as string not integer. Is there any way i can play with '" + params["fromdate"] + "-07-01' and pass parameter as string

Re: Date parameter not Working [message #1059146 is a reply to message #1058988]

Thu, 16 May 2013 20:56

Eclipse User

Take a look at the attached report. It takes a parameter value of 2002/2003, 2003/2004, or 2004/2005 as a "fiscal year", then in the beforeOpen, the string is split into a string array and each year is used with the static month and date to complete the query.

Hope this helps.

Attachment: ConcatDatePiecesForQuery.rptdesign
(Size: 24.33KB, Downloaded 324 times)

Re: Date parameter not Working [message #1059466 is a reply to message #1059146]

Sun, 19 May 2013 05:38

Eclipse User

Hi,
Thanks a lot.It worked. Now i have only one issue. When i am passing fiscal parameter into report in preview from report administration. When i am passing fiscal year parameter from list 2012/2013 it selects =2012/2013 and report is bringing nothing but when i select without = that is 2012/2013 it brings results. Any idea how to resolve this my fiscal year parameter is string and query is as follows in which parameter is being passed from outsite

sqlText =" select pmnum,SITEID,(select description from locations where pm.location = locations.location) "
+ "as site, "
+" (select description from commodities where commodities.commodity= "
+" pm.commoditygroup) as workcategory,description, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-01-01' and targstartdate "
+ "< '" + startYear + "-02-01') as jan "

Re: Date parameter not Working [message #1059475 is a reply to message #1059146]

Sun, 19 May 2013 07:07

Eclipse User

Hi,
Thanks for your reply. This report is working fine at BIRT level. But when i am trying to run the same report from BIRT report administration it is taking value as
=2012/2013 when value is passed with =2012/2013 it brings no results but when i passed it without = that is 2012/2013 it works well. Any idea how to resolve this issue of = at BIRT administration level

var sqlText = new String();
var yearParam = new String();
var startYear = new String();
var endYear = new String();

yearParam = params["FiscalYear"].value;
yearParam = yearParam.split("/");
startYear = yearParam[0];
endYear = yearParam[1];

// Add query to sqlText variable.
sqlText =" select pmnum,SITEID,(select description from locations where pm.location = locations.location) "
+ "as site, "
+" (select description from commodities where commodities.commodity= "
+" pm.commoditygroup) as workcategory,description, "
+ "(select wonum from workorder where "
+ "workorder.pmnum = pm.pmnum and targstartdate > '" + startYear + "-01-01' and targstartdate "
+ "< '" + startYear + "-02-01') as jan "

Re: Date parameter not Working [message #1059639 is a reply to message #1059475]

Tue, 21 May 2013 01:07

Eclipse User

You could just do startYear = startYear.replace("=",""); after the startYear = yearParam[0]. This should handle both cases. Let me know.

Previous Topic:	custom format x-axis and y-axis label in chart by script
Next Topic:	Removing carriage returns from a column output

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Mar 26 01:54:53 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter